Two lists, one list
The shop has a list of products it sells today. It also has a list of products it used to sell. The manager wants one list of every name, with no repeats.
You could copy one list under the other and delete the repeats by hand. Or you could ask the computer to stack them.
Key words
| Word | What it means |
|---|---|
| Database | an organised store of tables that you can ask questions |
| SQL | the language used to ask a database for data |
| Query | one question written in SQL |
| UNION | stacks the rows of two queries into one answer and removes repeats |
| UNION ALL | stacks the rows of two queries and keeps every repeat |
| Duplicate | a row that appears more than once |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can join two queries with UNION and UNION ALL, and say what the rules are.
Starter
Have a go
Starter
3 questionsHave a go: your answers are marked here and then forgotten. Sign in if you would like your score kept, and to pick up where you left off.Sign in to keep your score
Your answers are marked here and are not kept. A college account keeps every student’s marks.
Try it: stack two lists
SELECT name FROM products
UNION
SELECT name FROM old_products
ORDER BY name;
-- Apple is in both lists. Count it in the answer.
Three rules for UNION
- Both queries must give the same number of columns.
- Matching columns must hold the same kind of value.
- The column names come from the first query.
ORDER BY goes once, at the very end, and sorts the whole stack.
Worked example: UNION against UNION ALL
| Query | Rows from products | Rows from old_products | Rows in answer |
|---|---|---|---|
| UNION | 10 | 4 | 13 (Apple appears once) |
| UNION ALL | 10 | 4 | 14 (Apple appears twice) |
The cells students fill in are left blank in this preview.
Try it: keep the repeats
SELECT name FROM products
UNION ALL
SELECT name FROM old_products
ORDER BY name;
-- Compare with the UNION answer. Which name appears twice?
Check
Have a go
Check
4 questionsHave a go: your answers are marked here and then forgotten. Sign in if you would like your score kept, and to pick up where you left off.Sign in to keep your score
Your answers are marked here and are not kept. A college account keeps every student’s marks.