LEFT and RIGHT OUTER JOINs

The Join You’ll Use Most Often

INNER JOIN is great when you only want matches. But most analytics questions are different:

• “Show me all customers, even if they haven’t ordered.”
• “List every product, including ones never sold.”
• “Find users who haven’t logged in.”

That’s LEFT JOIN territory. In practice, LEFT JOIN appears in maybe 70% of production analytics queries. Master this one.

How LEFT JOIN Works

SELECT
  e.first_name,
  d.dept_name
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.id;

The Anti-Join Pattern: Finding What’s Missing

This is one of the most common SQL interview patterns for data analyst roles. You’ll see it constantly:

• “Find customers who have never placed an order.”
• “Which products have never been sold?”
• “List employees with no direct reports.”

The trick: LEFT JOIN, then filter for NULL on the right side.

After the LEFT JOIN, customers without orders have NULL in all the order columns. We use that to filter down to just the unmatched rows.

Practice: Finding Posts by Reaction Type

Use a JOIN to connect posts with their reactions, then filter for the right reaction type.

Practice: Filtering Host Units

Join the host and unit tables, then filter and aggregate.

The Filter Trap: A Bug That Bites Everyone

This is one of the most common SQL bugs in production. It shows up in dashboards at multiple companies. Here’s how it happens:

Your team wants to see every customer and flag who has bought a Coat — so they can target the rest with a promotion. You need all customers in the report, with Coat order details attached where they exist.

-- THE BUG: This drops customers with no orders!
SELECT
  c.id,
  c.first_name,
  o.order_details
FROM customers c
LEFT JOIN orders o
  ON c.id = o.cust_id
WHERE o.order_details = 'Coat';

What went wrong? Customers without orders have NULL for o.order_details. And NULL = 'Coat' is not TRUE (it’s NULL), so those rows get filtered out. Your LEFT JOIN just became an INNER JOIN. The customers you wanted to target with the promo have disappeared from the report.

The Fix: Move the Filter to ON

The ON clause already contains the join condition (c.id = o.cust_id). We’re not replacing it — we’re adding the filter with AND to narrow which rows match.

Now the join only matches Coat orders, but customers who never bought a Coat still appear (with NULLs). That’s what you need — the full roster shows who to target with the promo.

RIGHT JOIN: The One Nobody Uses

RIGHT JOIN is the mirror image of LEFT JOIN. It keeps all rows from the right table.

In practice, almost nobody uses RIGHT JOIN. It’s harder to read (the “main” table is on the right, which feels backwards), and it can always be rewritten as a LEFT JOIN by swapping the table order:

-- These are equivalent:

-- RIGHT JOIN version:
SELECT
  c.first_name,
  o.id AS order_id
FROM customers c
RIGHT JOIN orders o
  ON c.id = o.cust_id;

-- LEFT JOIN version (preferred):
SELECT
  c.first_name,
  o.id AS order_id
FROM orders o
LEFT JOIN customers c
  ON c.id = o.cust_id;

Most teams standardize on LEFT JOIN. It’s easier to reason about when the “preserved” table is always on the left.

Key Takeaways

• LEFT JOIN keeps all rows from the left table, filling NULLs for unmatched right columns
• WHERE on right-table columns can accidentally break LEFT JOIN behavior — move filters to ON
• Skip RIGHT JOIN; rewrite as LEFT JOIN with swapped tables

What’s Next

What if you need unmatched rows from both sides? “Show me customers without orders and orders without valid customers.” That’s FULL OUTER JOIN territory.