Fulfledge

Join tables together

INPUT · Slides

Joining two tables

01 / 05

You want every row joined

A subquery followed one at a time. But what you really want is a table with the item name attached to every single order.

That is what JOIN does. It ties two tables together on the key and hands them back as one result.

02 / 05

How JOIN and ON look

Add JOIN other table behind FROM, and in ON write which columns being equal means being the same thing. That condition in the ON is the key.

Both tables have columns with the same name, so you point at columns as table.column. It is the way to make clear which table you mean.

SELECT items.name, orders.quantity  FROM orders  JOIN items  ON orders.item_id = items.id;

Result

cypress chopstick rest | 2
cotton dishcloth | 4
sunrise mug | 4
dappled light lamp | 1
bean coaster | 3
cypress chopstick rest | 2
cotton dishcloth | 2
sunrise mug | 3
bean coaster | 6

03 / 05

Watch them line up sideways

Use * and you can see what the join did. The matching item row has been stuck onto the end of the order row, making one row.

The five columns on the left are orders, the four on the right are items. Check that item_id and id carry the same number alongside each other.

SELECT * FROM orders  JOIN items  ON orders.item_id = items.id;

Result

1 | 1 | 1 | 2 | app | 1 | cypress chopstick rest | 480 | table
2 | 3 | 2 | 4 | web | 3 | cotton dishcloth | 340 | kitchen
3 | 2 | 1 | 4 | shop | 2 | sunrise mug | 1600 | table
4 | 4 | 3 | 1 | app | 4 | dappled light lamp | 3800 | lighting
5 | 5 | 4 | 3 | web | 5 | bean coaster | 260 | table
6 | 1 | 3 | 2 | web | 1 | cypress chopstick rest | 480 | table
7 | 3 | 4 | 2 | app | 3 | cotton dishcloth | 340 | kitchen
8 | 2 | 2 | 3 | app | 2 | sunrise mug | 1600 | table
9 | 5 | 1 | 6 | shop | 5 | bean coaster | 260 | table

04 / 05

Say which table or it complains

id exists in orders and in items. Write plain id without saying which, and SQL cannot choose, so it errors.

ambiguous column name: id means "I cannot tell which id you mean". Put the table name on it, as in orders.id, and it is settled.

SELECT id, name FROM orders  JOIN items  ON orders.item_id = items.id;

Result

ambiguous column name: id

05 / 05

Rows that do not match do not appear

What comes out of a JOIN is only the rows where the key matched. An item nobody ever ordered has no partner in orders, so it never shows up.

Below takes the item names out of the joined result with no repeats. There should be six items, and there are only five. The next section goes into this properly. Let us write some.

SELECT DISTINCT items.name  FROM orders  JOIN items  ON orders.item_id = items.id  ORDER BY items.name;

Result

bean coaster
cotton dishcloth
cypress chopstick rest
dappled light lamp
sunrise mug