Simplify Complex Queries with Common Table Expressions

Common Table Expressions (CTEs) are a powerful feature that allows you to create temporary named result sets within your query. Think of them as temporary tables that exist only for the duration of your query execution. CTEs make complex queries more readable and help you break down complicated logic into manageable pieces.

Basic CTE Syntax

A CTE is defined using the WITH clause followed by a name and a query. Here’s the basic structure:

1WITH cte_name AS (
2    SELECT column1, column2
3    FROM table_name
4    WHERE condition
5)
6SELECT *
7FROM cte_name

Example: Customer Analysis

Let’s say you want to analyze high-value customers from your Individual DMO. First, create a CTE to identify customers with more than 5 contact points, then use that CTE in your main query:

1WITH high_activity_customers AS (
2    SELECT "ssot__Id__c", "ssot__FirstName__c", "ssot__LastName__c"
3    FROM "ssot__Individual__dlm"
4    WHERE "ssot__contact_point_count__c" > 5
5)
6SELECT "ssot__FirstName__c", "ssot__LastName__c"
7FROM high_activity_customers
8ORDER BY "ssot__LastName__c"

This query creates a temporary result set called high_activity_customers and then selects from it in the main query.

Multiple CTEs

You can define multiple CTEs in a single query by separating them with commas:

1WITH high_value_customers AS (
2    SELECT "ssot__Id__c", "purchase_total__c"
3    FROM "ssot__Individual__dlm"
4    WHERE "ssot__purchase_total__c" > 1000
5),
6recent_customers AS (
7    SELECT "ssot__Id__c", "created_date__c"
8    FROM "ssot__Individual__dlm"
9    WHERE "ssot__created_date__c" >= '2024-01-01'
10)
11SELECT hv."ssot__Id__c"
12FROM high_value_customers hv
13JOIN recent_customers rc ON hv."ssot__Id__c" = rc."ssot__Id__c"

Benefits of Using CTEs

Improved Readability: Break complex queries into logical, named components
Reusability: Reference the same subquery multiple times without repeating code
Debugging: Test each CTE individually to isolate issues

For comprehensive syntax details, advanced features like recursive CTEs, and additional examples, see the complete WITH Clause reference.

Note

When to Use CTEs

CTEs are particularly useful when you need to:

Calculate intermediate results that you’ll use multiple times
Make complex joins more readable
Replace complex subqueries with named, reusable components
Perform multi-step data transformations

In the next sections, you’ll learn about other advanced querying techniques that work well with CTEs, such as subqueries and joins.