Finding the key that joins them

01 / 05

What the number points at

Put the two tables side by side and something is shared. The number sitting in orders.item_id is a number from items.id.

So if order 3 has an item_id of 2, it is the item whose id is 2 in items.

SELECT item_id FROM orders  WHERE id = 3;

Result

02 / 05

Look the other table up

Once you have the number you go and look in items. Make the number your condition and out comes the name.

So now you know "order 3 was a sunrise mug". But you wrote two queries, and you carried that 2 across by hand.

SELECT name FROM items  WHERE id = 2;

Result

sunrise mug

03 / 05

One query, using a subquery

The number you were carrying by hand can be replaced with the subquery from the last section. The brackets fetch the item_id, and the outside looks that number up.

One query now. You can follow it all the way to the name without knowing the number.

SELECT name FROM items  WHERE id = (    SELECT item_id FROM orders    WHERE id = 3  );

Result

sunrise mug

04 / 05

A key need not share a name

What acts as the key here is orders.item_id together with items.id. The names can differ; what matters is that they point at the same thing.

And the reverse happens too — things that share a name but are not a key. orders.id is the order number and items.id is the item number. Both called id, and completely unrelated.

SELECT * FROM orders  WHERE item_id = (    SELECT id FROM items    WHERE name = 'cotton dishcloth'  )  ORDER BY id;

Result

2 | 3 | 2 | 4 | web
7 | 3 | 4 | 2 | app

05 / 05

One at a time is not enough

A subquery follows you as far as one number at a time. To "show the item name for every order" you would be writing it nine times over.

What you want is a way to pair orders and items up wholesale, using the key. That is JOIN, in the next section. First, get your hand in at following the key. Let us write some.