SQL for Data Analytics · LEFT JOIN

LEFT JOIN

What a JOIN leaves out5 min

A JOIN only keeps rows that have a match in both tables. Usually that's what you want. But sometimes the rows without a match are exactly what you're asking about, like which customers have never ordered anything, or which employees haven't handled an order.

Northwind has 91 customers. Run this to count how many different customers are left after joining Customers to Orders.

The number of different customers after joining Customers to Orders.
⌘/Ctrl + Enter

That's 74. The other 17 customers have never placed an order. The JOIN never finds a match for them, so they aren't in the result.

Here are the pets and owners from the Table JOINs lesson again. This time the query starts from the Owners table and uses a LEFT JOIN. Step through it. To compare, switch to JOIN and step through it again.

How a JOIN builds its rows

Result

LEFT JOIN5 min

A LEFT JOIN keeps every row from the first table, the one after FROM, whether it has a match or not. When a row has no match, the columns from the other table are filled with NULL.

Run this. It's Customers joined to Orders with a LEFT JOIN. The ORDER BY puts the rows with no OrderID at the top, so you can see them.

Every customer with their orders, including customers with no orders.
⌘/Ctrl + Enter

The first 17 rows are the customers with no orders. Each one gets a single row, with NULL in the Orders columns. The total is 213 rows: the 196 orders, plus one row for each of the 17 customers with none.

Which table is the "left" one depends on the order you write them in. Customers LEFT JOIN Orders keeps every customer. Orders LEFT JOIN Customers keeps every order, and since every order has a customer, it gives the same rows as a plain JOIN.

There's also RIGHT JOIN, which keeps every row from the second table. Anything you write with a RIGHT JOIN can be written as a LEFT JOIN by swapping the two tables, so this course only uses LEFT JOIN.

Finding rows with no match4 min

To find the customers who have never ordered, do a LEFT JOIN and keep only the rows where the Orders side is NULL.

Customers who have never placed an order.
⌘/Ctrl + Enter

Check a column that can't be NULL when there is a match. The primary key of the other table, here Orders.OrderID, is a safe choice, because every order has one.

Write a query that shows the first and last name of every employee who has never handled an order.
⌘/Ctrl + Enter

Start from Employees and use a LEFT JOIN, so every employee is kept. Then keep only the rows where Orders.OrderID IS NULL. If you get no rows back, check the join. A plain JOIN drops the employees with no orders before the WHERE runs.

Counting with a LEFT JOIN4 min

Say you want every customer with the number of orders they've placed, including the customers with none. A LEFT JOIN keeps those customers. Run this and look at what COUNT(*) gives them.

Each customer's number of orders, counted with COUNT(*). Fewest first.
⌘/Ctrl + Enter

Alfreds Futterkiste has never placed an order, but COUNT(*) says 1. Why?

How I'd answer it

COUNT(*) counts rows. The LEFT JOIN gave Alfreds Futterkiste one row, with NULL in all of the Orders columns. That's still a row, so the count is 1.

To get 0 for those customers, count a column from the Orders side. COUNT with a column name skips NULLs, as in the Null Values lesson, so a customer whose only row has a NULL OrderID gets a count of 0.

Each customer's number of orders, counted with COUNT(Orders.OrderID). Fewest first.
⌘/Ctrl + Enter
Filling in NULLs with COALESCE2 min

When every value SUM adds up is NULL, SUM gives NULL. Run this to see the total number of units each customer has ordered, including customers with no orders.

Total units ordered by each customer, fewest first.
⌘/Ctrl + Enter

Both joins have to be LEFT JOINs. If the second one were a plain JOIN, the customers with no orders would have a NULL OrderID, find no match in OrderDetails, and be dropped again.

If you're showing this to someone, NULL makes it look like the number is missing. These customers really did order zero units. COALESCE(x, 0) gives you x, unless x is NULL, in which case it gives you 0.

Total units ordered by each customer, with 0 for customers who have ordered nothing.
⌘/Ctrl + Enter

MySQL and MariaDB also have IFNULL(x, 0), which does the same thing. You'll see both.

Pause and answer

These are just for you. They aren't graded or turned in.

Sales asks for the average number of orders per customer. With a JOIN, there are 196 orders from 74 customers, about 2.65 each. With a LEFT JOIN, it's 196 orders over 91 customers, about 2.15 each. Which would you give them?

How I'd answer it

It depends on what Sales means by a customer. The 91 includes 17 who are in the Customers table but have never ordered anything. If Sales wants to know how often people who buy from Northwind come back, 2.65 is the better number. If they want to know how much the average account on file orders, it's 2.15.

Either way, I'd say which one it is: "2.65 orders per customer, counting only customers who have placed at least one order." If I wasn't sure what they meant, I'd ask, or give them both.