Practical Data Analysis with SQL/

...

/

Common Table Expressions

Common Table Expressions

Learn to use common table expressions to split a query into multiple parts.

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:

Press + to interact
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:

Press + to interact
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 ...