SQL for Data Analytics · Multiple Table JOINs
Multiple Table JOINs
Multiple Table JOINs17 min
In the video, every JOIN added columns to the result. Some of them also changed how many rows there are. That's easiest to see with a single customer.
This starts with customer 5 in the Customers table and adds one join at a time, in the same order as the last query in the video. To keep the result narrow, it shows only two columns from each table. Click Add the next join and watch what happens to the rows.
One customer through the joins
A JOIN makes one row for every match it finds. How many matches a row has depends on which direction you're joining across a one-to-many relationship.
From the "one" side to the "many" side, a row can have many matches. Customer 5 matches three orders, so customer 5's one row becomes three rows, and the customer's columns are copied onto each of them. Joining Orders to OrderDetails works the same way. An order matches one row in OrderDetails for every product on it.
From the "many" side to the "one" side, a row has at most one match. Each row in OrderDetails has one ProductID, and that ProductID belongs to one product. The number of rows stays the same, and the product's columns are added to the rows that are already there.
You can tell which direction a join goes from its ON. Look at the column from the table you're adding. If it's that table's primary key, like Products.ProductID, each row finds at most one match. If it's a foreign key, like Orders.CustomerID, a row can find many.
When a join adds rows, what one row stands for changes too. After joining Orders, one row is one order. After joining OrderDetails, one row is one product on one order. Joining Products doesn't change that.
The last query in the video starts from Customers and joins four more tables to get to Categories. Here's that chain again, one join at a time, for all of Northwind's customers. Keep an eye on the number of rows.
One join at a time
The number of rows went up at two of the joins and stayed the same at the other two. What's different about those joins?
How I'd answer it
Joining Customers to Orders goes from the "one" side of a one-to-many relationship to the "many" side. One customer can have many orders, so a customer's columns end up on as many rows as that customer has orders. Joining Orders to OrderDetails is the same. One order can have many products on it.
Joining OrderDetails to Products goes the other way. Each row in OrderDetails has one ProductID, so it matches exactly one product and stays one row. Joining Products to Categories works the same way.
Joining Customers to Orders also drops customers. Seventeen of the 91 have never placed an order, so the JOIN finds no match for them, and none of the 196 rows belong to them. The LEFT JOIN lesson shows how to keep them.
After a join, the number of rows matters as soon as you COUNT or SUM anything. Run this. It joins Orders to OrderDetails and counts the rows.
A coworker runs that query and reports that Northwind has 518 orders. What did they actually count? How would you count the orders?
How I'd answer it
They counted rows. After the JOIN, one row is one product on one order, so 518 is how many times any product shows up on any order. An order with three products counts three times.
To count each order once, count the different OrderIDs with COUNT(DISTINCT Orders.OrderID). That gives 196, the same as the number of rows in the Orders table. It's the same problem as in What is one row?, where a game on five platforms was five rows.
The category's name is in Categories. To get there from an order, go through OrderDetails and Products. An order with two beverages on it is two rows, so count the different OrderIDs.
These are for your own practice. They aren't graded or turned in. Add one join at a time, and run the query after each one.
Start from Orders. It has both a ShipperID and a CustomerID, so you can join Shippers and Customers to it directly.