Exploring LEFT JOIN

The Guest List Analogy

The "Guest List" Analogy

In SQL, choosing the right join is like managing a party. Imagine you have a Guest List (your primary table) and a Gift Registry (your secondary table).

Welcome to our deep dive into the LEFT JOIN. To understand how this works, let's look at a simple analogy: a party guest list and a gift registry. An INNER JOIN is like only looking at the people who actually brought a gift—you lose the names of everyone else. But a LEFT JOIN ensures you see every single person on your guest list. If they didn't bring a gift, you'll just see a blank space or a NULL value next to their name.

Schema & Concept

E-commerce Schema

We will use the Customers and Orders tables to see how a LEFT JOIN works in a real business context.

Key Terms:

Before we write the code, let's look at our database schema. We have a Customers table and an Orders table, linked by the CustomerID. In a LEFT JOIN, the first table you mention in your query is your 'Anchor'. While an INNER JOIN only gives you the overlap, a LEFT JOIN keeps everything from the anchor table, even if there's no matching order record.

Finding Inactive Customers

Practical Example

The marketing team wants a list of all customers and their order history to find who hasn't placed an order yet.

SELECT 
    Customers.CustomerName, 
    Orders.OrderID
FROM Customers
LEFT JOIN Orders 
    ON Customers.CustomerID = Orders.CustomerID;

Let's see this in action with code. We SELECT the name and OrderID, starting FROM Customers. Look at the result table. Alice Smith has an order, so she's easy. But look at Bob Jones. He has no orders, yet he's still in our list! Because we used a LEFT JOIN, SQL keeps Bob and simply marks his OrderID as NULL.

The WHERE Clause Trap

Common Pitfall

Be careful! Adding a WHERE clause on the right table can accidentally turn your LEFT JOIN back into an INNER JOIN.

Why? Because NULL values fail comparisons like > 0, filtering out the inactive customers you meant to keep.

There is a common trap beginners fall into. If you add a WHERE clause to filter the right table—for example, looking for orders over zero dollars— you accidentally kick Bob Jones out of the results! This happens because a NULL value isn't 'greater than zero'; it's unknown. To find only inactive customers, you should specifically check WHERE the OrderID IS NULL.

Exercise 1: All Products

Exercise 1: Products & Orders

Write a query to list all Products (ProductName) and any OrderID they were part of. Ensure products that have never been sold are still included.

Time for you to try. We want a list of every product in our catalog, even the ones that haven't sold yet. Write a query joining Products and Orders. Remember to put your anchor table first!

Exercise 2: Finding Inactive Customers

Exercise 2: Target Inactive Users

The marketing team needs the names of customers who have never placed an order. Use a LEFT JOIN and filter for NULL values.

Now, let's get specific. Write a query to find the names of customers who have zero orders. You'll need to use the IS NULL trick we just discussed.