CTE with JOIN

Use a CTE inside a JOIN.

CTE with JOIN

You can use a CTE like a table inside a JOIN. It simplifies complex joins.

WITH active AS (
  SELECT * FROM customers WHERE status = 'active'
)
SELECT a.name, o.amount
FROM active a
JOIN orders o ON a.id = o.customer_id;

This query returns orders from active customers.

WITH defines a temporary result set (CTE) named active; the query after it uses it like a table. a.name, o.amount is selected from active a. orders o is joined on a.id = o.customer_id, keeping only matching rows.

CTE with JOIN — details

You use the CTE as one side of a JOIN. The query becomes more readable.

You give the CTE an alias (e.g. active a) and match it to the other table with a condition such as ON a.id = o.customer_id; the main query reads as if it joined two ordinary tables.

Exercise CTE with JOIN

JOIN the active CTE with the orders table.

Show example solution

WITH active AS (
  SELECT * FROM customers WHERE status = 'active'
)
SELECT a.name, o.amount
FROM active a
JOIN orders o ON a.id = o.customer_id;

Test yourself

Add JOIN

FROM active a
____ orders o ON a.id = o.customer_id;

Show answer

FROM active a
JOIN orders o ON a.id = o.customer_id;

CTE with JOIN: match the terms

CTE

like table
JOIN

join
ON

condition
readable

clean
How is a CTE used in a JOIN?
- Like a table
- Like a subquery
- WHERE'de
- HAVING'de
Show answer

Like a table — A CTE is used like a table in a JOIN.

Next lesson: Summary - CTE

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies