CTE with JOIN
- SQL course
- Advanced
- Lesson 5/81
- 4 min
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.
Practice CTE with JOIN
JOIN the active CTE with the orders table.
Starter code
-- CTE join
WITHSample 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;Expected output
name amount Ayşe 250
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add JOIN
FROM active a
____ orders o ON a.id = o.customer_id;FROM active a
JOIN orders o ON a.id = o.customer_id;Explanation: You combine the CTE with another table using JOIN; ON gives the matching condition.
2 CTE with JOIN: match the terms
| Term | Meaning |
|---|---|
CTE |
like table |
JOIN |
join |
ON |
condition |
readable |
clean |
3 How is a CTE used in a JOIN?
- Like a table Correct answer
- Like a subquery
- WHERE'de
- HAVING'de
Explanation: A CTE is used like a table in a JOIN.