This lecture covers the LEFT JOIN. In the last lecture, we saw that an INNER JOIN only returns rows where a match is found in both tables. This is often what you want, but not always. What if you want to see a list of all customers, and *if* they have orders, you want to see that order information too?
This is the perfect use case for a LEFT JOIN (which is also sometimes called a LEFT OUTER JOIN).
What is a `LEFT JOIN`?
A LEFT JOIN returns all rows from the left table (the first table mentioned), and the matched rows from the right table. If there is no match for a row from the left table in the right table, the result will still include the row from the left table, but the columns from the right table will be filled with NULL values.
The `LEFT JOIN` Syntax
The syntax is almost identical to an INNER JOIN. You just replace the word INNER with LEFT.
Let’s write a query that lists all customers and any orders they may have placed. Remember from the last lecture, we have a customer, Charlie, who has not placed any orders.
SELECT
Customers.FirstName,
Orders.OrderID,
Orders.OrderDate
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.Customer_ID;Let’s analyze the result:
- The rows for Alice and Bob will appear just like they did in the
INNER JOIN, with their corresponding order information. - The row for Charlie will also appear. His `FirstName` will be shown, but the `OrderID` and `OrderDate` columns will be
NULL, because there was no matching record for him in the `Orders` table.
Finding Records That Don’t Have a Match
A very common use for a LEFT JOIN is to find rows in one table that do not have a corresponding entry in another table. For example, how can we find all the customers who have *never* placed an order?
We can do this by performing a LEFT JOIN and then using a WHERE clause to filter for the rows where the columns from the right table are NULL.
SELECT
Customers.FirstName
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.Customer_ID
WHERE Orders.OrderID IS NULL;
This query will return a single result: ‘Charlie’, because he is the only customer for whom the `OrderID` is NULL after the join.
In the Next Lecture…
Writing `TableName.ColumnName` all the time can be tedious and make our queries long. In the next lecture, we will learn how to use table aliases to give our tables temporary, shorter names within a query to make them much more readable.
