SQL for Data Analytics · Multiple Table JOINs

Multiple Table JOINs

Multiple Table JOINs17 min

Multiple Table JOINs17 min

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
One customer through the joins

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

When a join adds rows

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.

What one row is after each join

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

Rows…
One row is

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.

Counting after a join

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.

Count the rows after joining Orders to OrderDetails.
⌘/Ctrl + Enter

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.

Write a query that returns the number of orders that included at least one product from the Beverages category.
⌘/Ctrl + Enter

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.

Try it

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.

Write a query that shows the name of every product in the Dairy Products category, with the name and country of its supplier.
⌘/Ctrl + Enter
Write a query that shows how many orders each shipper has shipped to customers in the USA. Show the shipper's name and the count.
⌘/Ctrl + Enter

Start from Orders. It has both a ShipperID and a CustomerID, so you can join Shippers and Customers to it directly.