Search⌘ K
AI Features
Home
Courses
Practical Data Analysis with SQL

Common Table Expressions

Explore how to use common table expressions in SQL to break complex queries into manageable parts. Learn to create and reference CTEs, understand materialization behavior in PostgreSQL, and use CTEs to improve query readability and optimization.

We'll cover the following...

Using subqueries

It is sometimes useful to split a large query into smaller queries. One way to do it is using a subquery:

PostgreSQL
SELECT * 
FROM (
    SELECT 'ME@hakibenita.com' AS email
) AS emails;

The subquery, in this case, is enclosed inside the parentheses. We call this a subquery because it is a query nested inside another query. We gave the subquery the alias name emails. We can now reference the subquery, emails, like any other table.

Subqueries are useful, but as queries get more complicated, the number of subqueries can increase to a point where the query becomes very hard to read.

Using the WITH clause

In SQL, we can define a common table expression (CTE) using the WITH clause:

PostgreSQL
WITH emails AS (
    SELECT 'ME@hakibenita.com' AS email
)
SELECT * FROM emails;

Using the WITH clause, we declare a named query that we can reference in the same query like any other table or ...