Code Lab BetaBUILD. RUN. DISCOVER.
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
Loading…
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
Loading…
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
SQL / POSTGRESQL / Learn / LESSON 10
UNION stacks the results of two queries one under the other. Both queries need the same number of columns with compatible types; column names come from the first query. UNION removes duplicate rows, while UNION ALL keeps everything, duplicates included, and is cheaper because it skips duplicate removal.
Choose deliberately: want distinct values → UNION; want to append lists and count everything → UNION ALL. A final ORDER BY for the whole result is written once at the very end. Without it, row order is not guaranteed.
Common mistakes: a different number of columns or incompatible types; expecting UNION to keep duplicates; ORDER BY in each part; confusing UNION with JOIN — UNION adds rows, JOIN adds columns.
Small example:
SELECT city AS place FROM customers WHERE city IS NOT NULL UNION SELECT 'Online' AS place;
Working tables for this task. Grading also uses other datasets with the same schema.
| id | name | category | price |
|---|---|---|---|
| 1 | Atlas | books | 30 |
| 2 | Novel | books | 15 |
| 3 | Comic | books | 14.99 |
| 4 | Lamp | home | 29.99 |
| 5 | Vase | home | 30 |
| 6 | Rug | home | 45 |
| 7 | Hammer | tools | 15 |
| 8 | Drill | tools | 60 |
| 9 | Tape | tools | 15 |
| 10 | Candle | NULL | 8.5 |
| 11 | Gift Card | NULL | 25 |
| id | name | city |
|---|---|---|
| 1 | Mira | Sofia |
| 2 | Niko | Varna |
| 3 | Iva | Sofia |
| 4 | Alex | NULL |
| 5 | Boris | Plovdiv |
| 6 | Lena | Varna |
| 7 | Petya | Sofia |
| id | customer_id | total | order_date |
|---|---|---|---|
| 1 | 1 | 120 | 2024-01-05 |
| 2 | 1 | 35.5 | 2024-01-20 |
| 3 | 2 | 40 | 2024-01-20 |
| 4 | 3 | 99.99 | 2024-01-31 |
| 5 | NULL | 60 | 2024-02-01 |
| 6 | 1 | 40 | 2024-02-01 |
| 7 | 2 | 75.25 | 2024-02-14 |
| 8 | 3 | 100 | 2024-02-15 |
| 9 | 5 | 22 | 2024-02-15 |
| 10 | 1 | 39.99 | 2024-02-29 |
| 11 | 2 | 55 | 2024-03-01 |
| 12 | 3 | 18 | 2024-03-10 |
| 13 | 1 | 130 | 2024-03-15 |
| 14 | 2 | 40 | 2024-03-15 |
| 15 | 2 | 12.5 | 2024-03-31 |
| 16 | 3 | 47 | 2024-04-01 |
Expected columns: label · row order is not required
Expected result (displayed data):
Loading…UNION stacks the results of two queries one under the other. Both queries need the same number of columns with compatible types; column names come from the first query. UNION removes duplicate rows, while UNION ALL keeps everything, duplicates included, and is cheaper because it skips duplicate removal. Choose deliberately: want distinct values → UNION; want to append lists and count everything → UNION ALL. A final ORDER BY for the whole result is written once at the very end. Without it, row order is not guaranteed. Common mistakes: a different number of columns or incompatible types; expecting UNION to keep duplicates; ORDER BY in each part; confusing UNION with JOIN — UNION adds rows, JOIN adds columns.
QUERY STANDBY
›