Set operators in SQL

UNION, UNION ALL, INTERSECT and EXCEPT: combining the results of two queries, column compatibility, duplicate removal and where ORDER BY goes.

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

UNION: what is it?
Show the answer
The operator that merges the rows of two queries into one result and removes duplicates.
A row present in both results appears only once. To do so the engine must compare rows, which has a cost.
The client cities are Lyon, Paris, Lyon, Nantes; the magasin cities are Lyon, Paris, Lille. What does this query return?

```sql
SELECT ville FROM client
INTERSECT
SELECT ville FROM magasin;
`
- Lyon and Paris, once each
- Lyon, Paris and Lyon
- Nantes
- Lyon, Paris, Nantes and Lille
Show the answer
Lyon and Paris, once each
Only Lyon and Paris appear on both sides. INTERSECT deduplicates: the clients' Lyon, present twice, comes out once. Four cities would be the result of a UNION.
Only one ORDER BY is allowed, written … of the compound query: it sorts ….
Show the answer
Only one ORDER BY is allowed, written at the end of the compound query: it sorts the whole result.
SELECT … UNION SELECT … ORDER BY ville sorts the union. Written in the middle without parentheses, the sort makes no sense: the database refuses it.
UNION ALL: what is it?
Show the answer
The operator that merges the rows of two queries without removing anything: duplicates are kept.
It returns as many rows as both queries put together. Faster, because the engine does not deduplicate: prefer it when duplicates are impossible or wanted.