SQL for Data Analytics · Table JOINs

Table JOINs

Table JOINs15 min

Table JOINs15 min

A note on this video

In the video, MariaDB words the error as Column 'CategoryID' in field list is ambiguous. On this site the same mistake gives ambiguous column name: CategoryID. They mean the same thing.

Run queries here while you watch. When I tell you in the video to open Workbench and try something, use this box. This is for your own practice. It isn't recorded or part of your unit submission.
⌘/Ctrl + Enter
How a JOIN builds its rows

In the video I joined products to categories. Here's the same idea with two small tables, so you can watch every row of the result get made. The Pets table has an OwnerID column. It's a foreign key that points at the Owners table.

Click Next row to step through the query.

How a JOIN builds its rows

Result

Olivia Smith's name and city are in the result three times, once for each of Olivia's pets. In the Owners table, they're still stored only once. The JOIN repeats them in the result and doesn't change the tables.

Dana Cruz is in the Owners table, but not in the result. Why not?

How I'd answer it

The query works through the Pets table and looks up each pet's owner. No pet has OwnerID 3, so nothing ever looks up Dana. A JOIN only makes a row when it finds a match, so an owner with no pets is left out. Most of the time that's what you want. When it isn't, a LEFT JOIN keeps the rows that have no match.

Writing the ON

The part of a JOIN that trips people up is the ON. It tells SQL which column in one table matches which column in the other. Almost always, that's the foreign key in one table and the primary key it points at in the other.

SELECT *
FROM Products
JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID;

Products.SupplierID is the foreign key in Products. Suppliers.SupplierID is the primary key of Suppliers. Both tables have a column called SupplierID, so the table name and a dot in front of each one tell SQL which you mean.

In Northwind the two columns have the same name, but they don't have to. If the Suppliers table called its primary key ID, the ON would be Products.SupplierID = Suppliers.ID.

Once the JOIN is in, both SupplierID columns are in the result. Any time you name that column anywhere else in the query, you have to say which table's you mean, even though the values are the same.

The query in the box doesn't run. Fix it so it shows each product's name, its SupplierID, and its supplier's name.
⌘/Ctrl + Enter

Read the error. SQL can't tell which SupplierID you want in the SELECT. Put a table name and a dot in front of it. Either table works.

Try it

These are for your own practice. They aren't graded or turned in. Like in the video, start with SELECT * and the JOIN, run it, and check that you're getting what you expect. Then cut it down to the columns you want.

Write a query that shows every order's ID and date, with the name of the customer who placed it.
⌘/Ctrl + Enter
Write a query that shows the name and price of every product from a supplier in Japan, with the supplier's name.
⌘/Ctrl + Enter

The supplier's country is in the Suppliers table. Once it's joined, you can use it in a WHERE like any other column.