---
schema_version: "1"
id: ea1d5f2dcea902554ef90fe04ad1bf9076130b4cd79a924eaf055ae3411f914b
title: Simplify Complex Queries with Common Table Expressions
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/guides/get-started-dc-sql/common-table-expressions.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/guide/common-table-expressions.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: d44b28114d78f53b72a5bbb52253a543a91c67b0b15ebd97dda771774557f164
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# 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:

```sql
WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM 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:

```sql
WITH high_activity_customers AS (
    SELECT "ssot__Id__c", "ssot__FirstName__c", "ssot__LastName__c"
    FROM "ssot__Individual__dlm"
    WHERE "ssot__contact_point_count__c" > 5
)
SELECT "ssot__FirstName__c", "ssot__LastName__c"
FROM high_activity_customers
ORDER 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:

```sql
WITH high_value_customers AS (
    SELECT "ssot__Id__c", "purchase_total__c"
    FROM "ssot__Individual__dlm"
    WHERE "ssot__purchase_total__c" > 1000
),
recent_customers AS (
    SELECT "ssot__Id__c", "created_date__c"
    FROM "ssot__Individual__dlm"
    WHERE "ssot__created_date__c" >= '2024-01-01'
)
SELECT hv."ssot__Id__c"
FROM high_value_customers hv
JOIN 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

**note:** For comprehensive syntax details, advanced features like recursive CTEs, and additional examples, see the complete [WITH Clause reference](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-with.html).

## 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](https://developer.salesforce.com/docs/data/data-cloud-query-guide/guide/subqueries-related-data.md) and [joins](https://developer.salesforce.com/docs/data/data-cloud-query-guide/guide/query-joins.md).