SQL for Data Analytics · LEFT JOIN
LEFT JOIN
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.
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
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.
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.
To find the customers who have never ordered, do a LEFT JOIN and keep only the rows where the Orders side is NULL.
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.
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.
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.
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.
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.
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.
MySQL and MariaDB also have IFNULL(x, 0), which does the same thing. You'll see both.
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.