Common Table Expressions (CTEs) in SQL - Simplifying Complex Queries

Introduction

A Common Table Expression (CTE) is like creating a temporary named result set that you can reference within your main query. Think of it as giving a name to a query result so you can use it later.

Why CTEs Matter?

They’re useful when you need to:

Break down complex queries
Make queries more readable
Reuse query results
Create step-by-step logic

Main Content: Understanding CTEs

Let’s use this simple table:

-- Create a simple table
CREATE TABLE sales (
    product_name VARCHAR(50),
    sale_date DATE,
    sale_amount DECIMAL(10,2)
);

-- Insert sample data
INSERT INTO sales VALUES
('Laptop', '2024-01-15', 1200.00),
('Phone', '2024-01-15', 800.00),
('Tablet', '2024-01-15', 500.00),
('Laptop', '2024-01-16', 1100.00),
('Phone', '2024-01-16', 750.00),
('Tablet', '2024-01-16', 550.00);

Example 1: Basic CTE

Let’s calculate daily totals using a CTE:

WITH daily_totals AS (
    SELECT 
        sale_date,
        SUM(sale_amount) as total_sales
    FROM sales
    GROUP BY sale_date
)
SELECT 
    sale_date,
    total_sales
FROM daily_totals
ORDER BY sale_date;

This query:

Creates a CTE named ‘daily_totals’
Calculates sum of sales per day
Uses the CTE in main query

Results:

sale_date  | total_sales
-----------|-------------
2024-01-15 | 2500.00
2024-01-16 | 2400.00

Example 2: Multiple CTEs

Let’s analyze products and daily totals:

WITH product_totals AS (
    SELECT 
        product_name,
        SUM(sale_amount) as product_sales
    FROM sales
    GROUP BY product_name
),
daily_totals AS (
    SELECT 
        sale_date,
        SUM(sale_amount) as daily_sales
    FROM sales
    GROUP BY sale_date
)
SELECT 
    p.product_name,
    p.product_sales,
    d.sale_date,
    d.daily_sales
FROM product_totals p
CROSS JOIN daily_totals d
ORDER BY p.product_name, d.sale_date;

This query:

Creates CTE for product totals
Creates CTE for daily totals
Combines both CTEs in main query

Results:

product_name | product_sales | sale_date  | daily_sales
-------------|---------------|------------|-------------
Laptop       | 2300.00       | 2024-01-15 | 2500.00
Laptop       | 2300.00       | 2024-01-16 | 2400.00
Phone        | 1550.00       | 2024-01-15 | 2500.00
Phone        | 1550.00       | 2024-01-16 | 2400.00
Tablet       | 1050.00       | 2024-01-15 | 2500.00
Tablet       | 1050.00       | 2024-01-16 | 2400.00

Example 3: Simple Calculations with CTEs

Let’s calculate averages and compare:

WITH sales_stats AS (
    SELECT 
        product_name,
        AVG(sale_amount) as avg_price,
        MIN(sale_amount) as min_price,
        MAX(sale_amount) as max_price
    FROM sales
    GROUP BY product_name
)
SELECT 
    product_name,
    avg_price,
    min_price,
    max_price,
    max_price - min_price as price_range
FROM sales_stats
ORDER BY avg_price DESC;

This query:

Creates CTE with statistics
Calculates price range
Orders by average price

Results:

product_name | avg_price | min_price | max_price | price_range
-------------|-----------|-----------|-----------|-------------
Laptop       | 1150.00   | 1100.00   | 1200.00   | 100.00
Phone        | 775.00    | 750.00    | 800.00    | 50.00
Tablet       | 525.00    | 500.00    | 550.00    | 50.00

Best Practices

Naming CTEs
- Use clear, descriptive names
- Follow naming conventions
- Make names indicate purpose
Structure
- One CTE per logical step
- Keep CTEs simple
- Order CTEs logically
Readability
- Indent properly
- Comment complex logic
- Break into steps
Common Uses
- Step-by-step calculations
- Temporary result sets
- Breaking down complex logic
- Reusing query parts
Things to Remember
- CTEs are temporary
- Define before using
- Can reference earlier CTEs
- Makes queries clearer