Today I spent some time learning about SQL common table expressions (aka “CTEs”). I already use subqueries, so they were easy to understand.

The syntax is very simple:

WITH my_cte AS (
    SELECT col1, col2, col3
    FROM table1
)
SELECT col1, col3
FROM my_cte
WHERE ...

Here’s a more complex example with two CTEs:

WITH my_cte1 AS (
    SELECT col1, col2, col3
    FROM table1
),
my_cte2 AS (
    SELECT col4, col5, col6
    FROM table1
)
SELECT my_table.*, my_cte1.col1, my_cte2.col4
FROM my_table
    JOIN my_cte1 ON my_table.fkey_id1 = my_cte1.col1
    JOIN my_cte2 ON my_table.fkey_id4 = my_cte1.col4
WHERE ...

In general, I should prefer CTEs over subqueries because:

  • CTEs can do most anything (everything?) subqueries can do
  • CTEs are easier to read (since they aren’t jammed in the middle of the query)

Useful links: