LEFT JOIN and RIGHT JOIN

Introduction

INNER JOIN only returns rows where matches exist in both tables. But what if you want to include rows that don't have matches? That's where LEFT JOIN and RIGHT JOIN come in—they include unmatched rows from one side of the join.

The Problem with INNER JOIN

Recall our customer/orders example:

-- Customers
INSERT INTO customers VALUES
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Carol');  -- No orders!

-- Orders
INSERT INTO orders VALUES
    (1, 1, 100.00),
    (2, 1, 75.50),
    (3, 2, 200.00);

-- INNER JOIN
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Result:

  name   | total
---------+--------
 Alice   | 100.00
 Alice   |  75.50
 Bob     | 200.00

Carol is missing! She has no orders, so no match.

Question: What if we want to see ALL customers, even those without orders?

Answer: Use LEFT JOIN!

LEFT JOIN (LEFT OUTER JOIN)

What is LEFT JOIN?

LEFT JOIN returns:

• All rows from the left table (FROM table)
• Matching rows from the right table (JOIN table)
• NULL values for right table columns when no match exists

Syntax

SELECT columns
FROM left_table
LEFT JOIN right_table ON left_table.column = right_table.column;

"LEFT OUTER JOIN" is the same as "LEFT JOIN"

Example

SELECT
    c.name,
    o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Result:

  name   | total
---------+--------
 Alice   | 100.00
 Alice   |  75.50
 Bob     | 200.00
 Carol   | NULL    ← Included with NULL!

Carol appears with NULL for total because she has no orders.

Visualizing LEFT JOIN

Customers (LEFT)        Orders (RIGHT)
┌────┬───────┐          ┌────┬─────────────┬───────┐
│ id │ name  │          │ id │ customer_id │ total │
├────┼───────┤          ├────┼─────────────┼───────┤
│ 1  │ Alice │ ←─────── │ 1  │      1      │ 100.00│
│ 2  │ Bob   │ ←─────── │ 2  │      1      │  75.50│
│ 3  │ Carol │ (no match)│ 3  │      2      │ 200.00│
└────┴───────┘          └────┴─────────────┴───────┘
       ↓
  All included

Practical Use Cases for LEFT JOIN

Use Case 1: Find Customers Without Orders

SELECT
    c.name,
    c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;  -- No matching order

Result:

  name  |       email
--------+--------------------
 Carol  | carol@example.com

Finds customers who have never placed an order.

Use Case 2: Customer Order Summary (Include All Customers)

SELECT
    c.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

Result:

  name   | order_count | total_spent
---------+-------------+-------------
 Alice   |      2      |   175.50
 Bob     |      1      |   200.00
 Carol   |      0      |     0.00    ← Included!

All customers shown, even Carol with 0 orders.

Use Case 3: Products Never Ordered

SELECT
    p.name,
    p.price
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.id IS NULL;

Finds products that have never been purchased.

RIGHT JOIN (RIGHT OUTER JOIN)

What is RIGHT JOIN?

RIGHT JOIN is the opposite of LEFT JOIN:

• All rows from the right table (JOIN table)
• Matching rows from the left table (FROM table)
• NULL values for left table columns when no match exists

Syntax

SELECT columns
FROM left_table
RIGHT JOIN right_table ON left_table.column = right_table.column;

Example

SELECT
    c.name,
    o.total
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

Result:

  name   | total
---------+--------
 Alice   | 100.00
 Alice   |  75.50
 Bob     | 200.00

Same as INNER JOIN in this case (all orders have customers).

When RIGHT JOIN Shows Difference

-- Orders with non-existent customer
INSERT INTO orders VALUES (4, 999, 50.00);  -- customer_id 999 doesn't exist

SELECT
    c.name,
    o.total
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

Result:

  name   | total
---------+--------
 Alice   | 100.00
 Alice   |  75.50
 Bob     | 200.00
 NULL    |  50.00  ← Order without customer!

LEFT JOIN vs RIGHT JOIN

They're Interchangeable!

-- These produce the same result:

-- LEFT JOIN
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- RIGHT JOIN (tables swapped)
SELECT c.name, o.total
FROM orders o
RIGHT JOIN customers c ON c.id = o.customer_id;

Convention: Prefer LEFT JOIN

Most developers prefer LEFT JOIN because:

• Easier to read (left-to-right)
• More intuitive ("start with this table, add matching data")
• RIGHT JOIN is rarely needed

Use LEFT JOIN by default.

Filtering with LEFT JOIN

WHERE vs ON

Filter in WHERE (After Join)

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.total > 100;

Result:

  name  | total
--------+--------
 Bob    | 200.00

Customers without orders (like Carol) are excluded because o.total IS NULL fails the WHERE condition.

Filter in ON (During Join)

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.total > 100;

Result:

  name   | total
---------+--------
 Alice   | NULL
 Bob     | 200.00
 Carol   | NULL

All customers included, but only orders > 100 are matched.

Key difference:

• WHERE: Filters final result (excludes unmatched rows)
• ON: Filters during join (keeps unmatched rows from left table)

Multiple LEFT JOINs

SELECT
    c.name,
    o.id AS order_id,
    p.name AS product
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id;

Result includes:

• Customers without orders
• Orders without items
• Items without products (if any)

Common Patterns

Pattern 1: Active vs Inactive Customers

-- Customers who haven't ordered in 90 days
SELECT
    c.name,
    c.email,
    MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email
HAVING MAX(o.order_date) < CURRENT_DATE - INTERVAL '90 days'
    OR MAX(o.order_date) IS NULL;

Pattern 2: Inventory Check

-- Products with low or no sales
SELECT
    p.name,
    p.stock,
    COALESCE(SUM(oi.quantity), 0) AS total_sold
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, p.stock
HAVING COALESCE(SUM(oi.quantity), 0) < 10;

Pattern 3: User Activity Report

-- All users with their last login (even if never logged in)
SELECT
    u.username,
    COALESCE(MAX(l.login_at)::TEXT, 'Never') AS last_login
FROM users u
LEFT JOIN logins l ON u.id = l.user_id
GROUP BY u.id, u.username;

Handling NULLs in LEFT JOIN

COALESCE for Defaults

SELECT
    c.name,
    COALESCE(COUNT(o.id), 0) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

IS NULL to Find Unmatched

-- Customers without orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

IS NOT NULL to Find Matched

-- Customers with orders
SELECT DISTINCT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NOT NULL;

Performance Considerations

LEFT JOIN Can Be Slower

-- If you only need matches, use INNER JOIN
-- ✅ Faster
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- ❌ Slower (unnecessarily includes unmatched rows)
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NOT NULL;  -- Filters them out anyway!

Use LEFT JOIN only when you need unmatched rows.

Index Both Join Columns

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_customers_id ON customers(id);  -- Usually primary key already indexed

Common Mistakes

Mistake 1: WHERE Negates LEFT JOIN

-- ❌ Defeats purpose of LEFT JOIN
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.total > 100;  -- Excludes customers without orders!

-- ✅ Keep LEFT JOIN effect
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.total > 100;

Mistake 2: Wrong NULL Check

-- ❌ Wrong: Checks wrong column
WHERE o.total IS NULL

-- ✅ Correct: Check foreign key or primary key
WHERE o.id IS NULL

Mistake 3: Using RIGHT JOIN Unnecessarily

-- ❌ Confusing
FROM orders o
RIGHT JOIN customers c ON ...

-- ✅ Clearer
FROM customers c
LEFT JOIN orders o ON ...

Practice Exercises

Exercise 1

Find all customers and their order count (include customers with 0 orders).

SELECT
    c.name,
    COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

Exercise 2

Find products that have never been ordered.

SELECT p.name
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.id IS NULL;

Exercise 3

Find customers who have no orders over $100.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.total > 100
WHERE o.id IS NULL;

Key Takeaways

• ✅ LEFT JOIN includes all rows from left table, even without matches
• ✅ RIGHT JOIN includes all rows from right table (rarely used)
• ✅ NULL values appear for unmatched columns
• ✅ WHERE vs ON: WHERE filters after, ON filters during join
• ✅ Use LEFT JOIN to find missing relationships
• ✅ Prefer LEFT JOIN over RIGHT JOIN for readability
• ✅ COALESCE handles NULLs in aggregates

Next Steps

You now know INNER JOIN and LEFT/RIGHT JOIN! In the next lesson, we'll learn FULL OUTER JOIN—how to include unmatched rows from both sides of the join.

Keep mastering JOINs!