Two stock lists, one picture
Shop A and Shop B each send a stock list. Some products are in both lists. Some are in one only.
The manager wants every product from both, side by side. And one long list of all the rows. Two more joins do that.
Key words
| Word | What it means |
|---|---|
| Full join | keeps every row from both tables, matched or not |
| Union | stacks the rows of two tables into one longer table |
| Union join | a union where the tables may have different columns |
| Duplicate | a row that appears more than once |
| NULL | a missing value |
| Column | one field that runs down a table |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can use a full join and a union join and say how they differ.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
A full join keeps everything side by side
A full join keeps every row from both tables. Matches sit on one line. Rows with no match keep their own side and get NULL on the other.
It is a left join and a right join together.
Try it: full join two stock lists
select coalesce(a.product, b.product) as product,
a.units as shop_a,
b.units as shop_b
from shop_a a
full join shop_b b on b.product = a.product
order by product;
-- Helmet is only in A. Pump is only in B.
A union stacks rows
A union puts the rows of one table under the rows of another. It joins down, not across. Both tables need the same columns.
UNION ALL keeps every row. UNION removes a duplicate.
Try it: UNION and UNION ALL
select 'A' as shop, product, units from shop_a
union all
select 'B' as shop, product, units from shop_b
order by product, shop;
-- Try it: remove the shop column and use union. Which row disappears?
A union join allows different columns
A union join stacks rows from tables whose columns differ. Every column from both tables appears. A row gets NULL where its own table had no such column.
In DuckDB you write UNION ALL BY NAME.