The WITH clause
Common table expressions: naming a query, chaining several, what WITH brings compared with a nested subquery, and WITH RECURSIVE to walk through a hierarchy.
- 20 cards
- 6 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
- Scenarios
- Understand
A few cards from the deck
Common table expression (CTE): what is it?
Show the answer
A query named at the head of a statement by
WITH name AS (…): the rest of the statement cites it like a table, without copying it.The name exists only during that statement, nothing is stored in the database. You can chain several, separated by commas, each able to cite the previous ones.
The employe(id, nom, id_chef) table holds: 1 Roux (NULL), 2 Sam (1), 3 Tara (1), 4 Uma (2), 5 Vik (2), 6 Wen (3), 7 Xav (4). Which names does this query return?
```sql
WITH RECURSIVE equipe AS (
SELECT id, nom FROM employe WHERE id = 2
UNION ALL
SELECT e.id, e.nom
FROM employe e JOIN equipe q ON e.id_chef = q.id
)
SELECT nom FROM equipe;`Sam,Uma,VikandXavSam,UmaandVikUma,VikandXavRoux,Sam,Uma,VikandXav
Show the answer
Sam,Uma,VikandXavThe anchor gives Sam; the first round adds his direct reports, Uma and Vik; the second adds Uma's, Xav; the third finds nobody and the recursion stops. Roux, Sam's boss, is never reached.
The name given by
WITH name AS (…)exists only for …: it is not stored in the database.Show the answer
The name given by
WITH name AS (…)exists only for that single statement: it is not stored in the database.In the next statement you must define it again:
SELECT * FROM total_clientanswersrelation "total_client" does not exist. For a lasting name, create a view.Anchor query: what is it?
Show the answer
The first part of a recursive query, run only once: it supplies the starting rows that the recursive part will extend.
In an org chart, it is for instance
SELECT … FROM employe WHERE id_chef IS NULL, the director. Without an anchor, the recursion would have nothing to extend.