LEFT and RIGHT JOIN

Preserve all rows from one table with LEFT and RIGHT JOINs.

18 min read
Beginner

Introduction to LEFT and RIGHT JOINs

In the last lesson, you learned about INNER JOIN, which returns only rows that match in both tables. But what if you want to keep all rows from one table, even if there's no match in the other?

For example:

  • List all customers and their orders (including customers with no orders)
  • Show all products with their sales (including products never sold)
  • Display all employees with their assigned projects (including those not assigned yet)

That's where LEFT JOIN and RIGHT JOIN come in - they preserve rows from one table even when there's no match!

Returns all rows from the left table, plus matching rows from the right table. If there's no match, the right table's columns are filled with NULL.

Returns all rows from the right table, plus matching rows from the left table. If there's no match, the left table's columns are filled with NULL. (Less common than LEFT JOIN)

Let's work with our familiar tables:

customers
id
name
city
1Alice CorpNew York
2Bob IncLondon
3Charlie LtdToronto
4Diana CoSydney
orders
id
customer_id
order_date
total
10112024-01-15500.00
10222024-01-20350.00
10312024-02-01750.00
10432024-02-05220.00

Notice: Diana Co (customer_id = 4) has placed no orders.

LEFT JOIN - Keep All Rows from Left Table

Syntax:

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

The left table is the one after FROM, the right table is the one after LEFT JOIN.

sql
-- Get ALL customers, with their orders (if any)
SELECT
  c.name,
  c.city,
  o.id AS order_id,
  o.order_date,
  o.total
FROM customers c
LEFT JOIN orders o
  ON c.id = o.customer_id;

Key difference from INNER JOIN:

  • Diana Co appears in the results! 🎉
  • Her order columns are NULL because she has no orders
  • INNER JOIN would have excluded Diana entirely

What happened:

  1. SQL took ALL rows from customers (left table)
  2. For each customer, it looked for matching orders
  3. When a match was found (Alice, Bob, Charlie), it joined the order data
  4. When NO match was found (Diana), it kept the customer but filled order columns with NULL

Think of LEFT JOIN as: "Give me everything from the left table, and whatever matches from the right table. If nothing matches on the right, give me NULLs."

LEFT JOIN vs INNER JOIN - Side by Side

Let's compare the two with the same data:

sql
-- INNER JOIN - only customers WITH orders
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
INNER JOIN orders o
  ON c.id = o.customer_id
GROUP BY c.id, c.name;
sql
-- LEFT JOIN - ALL customers, including those without 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;

LEFT JOIN includes Diana with 0 orders. INNER JOIN excluded her entirely!

Note: We used COUNT(o.id) instead of COUNT(*) because:

  • COUNT(*) counts all rows (Diana would show 1, not 0)
  • COUNT(o.id) counts non-NULL order IDs (Diana has NULL, so count = 0)

Finding Rows with No Match

A common use of LEFT JOIN is to find rows that don't have a match:

sql
-- Find customers who have NOT placed any orders
SELECT
  c.id,
  c.name,
  c.city
FROM customers c
LEFT JOIN orders o
  ON c.id = o.customer_id
WHERE o.id IS NULL;

How this works:

  1. LEFT JOIN includes all customers
  2. Diana has no orders, so o.id is NULL
  3. WHERE o.id IS NULL filters to only rows where there was no match

Common pattern for finding "orphaned" records!

sql
-- Products that have never been ordered
SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN order_items oi
  ON p.product_id = oi.product_id
WHERE oi.id IS NULL;

RIGHT JOIN - Keep All Rows from Right Table

RIGHT JOIN is the mirror of LEFT JOIN - it keeps all rows from the right table (the one after RIGHT JOIN).

sql
-- Get ALL orders, with customer info (if available)
SELECT
  o.id AS order_id,
  o.order_date,
  o.total,
  c.name,
  c.city
FROM customers c
RIGHT JOIN orders o
  ON c.id = o.customer_id;

All orders are included. If an order had no matching customer (orphaned order), the customer columns would be NULL.

In practice, RIGHT JOIN is rarely used because you can rewrite it as a LEFT JOIN by swapping the tables:

sql
-- These are equivalent:
SELECT ... FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id;
SELECT ... FROM orders o LEFT JOIN customers c ON o.customer_id = c.id;

Best Practice: Use LEFT JOIN and put the "main" table (the one you want all rows from) on the left (after FROM). This makes queries easier to read and understand.

RIGHT JOIN is supported for completeness, but LEFT JOIN is clearer!

LEFT JOIN with Multiple Conditions

You can add additional conditions to the ON clause:

sql
-- All customers with their large orders (> $400) only
SELECT
  c.name,
  o.id AS order_id,
  o.total
FROM customers c
LEFT JOIN orders o
  ON c.id = o.customer_id
  AND o.total > 400;

Important difference:

  • Condition in ON: affects the join (Bob/Charlie/Diana show NULL because their orders don't meet condition)
  • Condition in WHERE: filters after join (would exclude Bob/Charlie/Diana entirely)
sql
-- Compare: Using WHERE instead filters out customers
SELECT
  c.name,
  o.id AS order_id,
  o.total
FROM customers c
LEFT JOIN orders o
  ON c.id = o.customer_id
WHERE o.total > 400;  -- This filters AFTER the join

WHERE o.total > 400 excludes customers without large orders entirely (behaves like INNER JOIN)!

LEFT JOIN with Aggregates

Combine LEFT JOIN with GROUP BY for powerful reports:

sql
-- Customer summary including those with no orders
SELECT
  c.name,
  c.city,
  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, c.city
ORDER BY total_spent DESC;

Notes:

  • COUNT(o.id) counts non-NULL order IDs (0 for Diana)
  • COALESCE(SUM(o.total), 0) converts NULL to 0 for customers with no orders
products
id
product_name
category
price
1LaptopElectronics999
2MouseElectronics29
3Desk ChairFurniture299
4Desk LampFurniture49
order_items
id
order_id
product_id
quantity
110112
210231
310311
sql
-- Product sales including unsold products
SELECT
  p.product_name,
  p.category,
  COUNT(oi.id) AS times_ordered,
  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.product_name, p.category
ORDER BY total_sold DESC;

Mouse and Desk Lamp have never been sold, but they still appear in the results!

Multiple LEFT JOINs

You can chain multiple LEFT JOINs:

employees
id
name
department_id
manager_id
1Alice10NULL
2Bob101
3Charlie20NULL
4DianaNULLNULL
departments
id
dept_name
10Engineering
20Marketing
sql
-- All employees with department and manager info (if available)
SELECT
  e.name AS employee,
  d.dept_name AS department,
  m.name AS manager
FROM employees e
LEFT JOIN departments d
  ON e.department_id = d.id
LEFT JOIN employees m
  ON e.manager_id = m.id;
  • Alice and Charlie have no managers (NULL)
  • Diana has no department (NULL) and no manager (NULL)
  • All employees appear thanks to LEFT JOIN!

Common Mistakes to Avoid

Don't make these mistakes:

  1. Using COUNT(*) with LEFT JOIN: ❌ Misleading!

    • COUNT(*) counts rows (Diana would show 1, not 0)
    • Use COUNT(right_table.id) to count matches

  2. Filtering NULL in WHERE instead of ON: ❌

    • WHERE filters AFTER join (removes non-matching rows)
    • Put conditions in ON if you want to affect the join itself

  3. Not handling NULL in aggregates: ❌

    • SUM(o.total) returns NULL for Diana
    • Use COALESCE(SUM(o.total), 0) to convert NULL to 0

  4. Using RIGHT JOIN when LEFT JOIN is clearer: ❌

    • FROM orders o RIGHT JOIN customers c is confusing
    • Better: FROM customers c LEFT JOIN orders o

  5. Confusing LEFT and INNER JOIN: ❌

    • LEFT JOIN keeps all left table rows
    • INNER JOIN excludes non-matching rows
    • Know which one you need!

  6. Not specifying which table in GROUP BY: ❌

    • With joins, always use table prefix: GROUP BY c.id, c.name

  7. Assuming NULL means "no data": Could be actual NULL! ❌

    • Use WHERE right_table.primary_key IS NULL to find non-matches
    • Don't use WHERE right_table.some_column IS NULL (might be NULL in matching rows too)

Practical Examples

Example 1: Sales report with inactive customers

sql
-- Show all customers, highlight those inactive (no recent orders)
SELECT
  c.id,
  c.name,
  COUNT(o.id) AS orders_last_year,
  CASE
    WHEN COUNT(o.id) = 0 THEN 'Inactive'
    WHEN COUNT(o.id) < 3 THEN 'Low Activity'
    ELSE 'Active'
  END AS status
FROM customers c
LEFT JOIN orders o
  ON c.id = o.customer_id
  AND o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
GROUP BY c.id, c.name
ORDER BY orders_last_year DESC;

Example 2: Product inventory check

sql
-- Find products that haven't sold in the last 30 days
SELECT
  p.id,
  p.product_name,
  p.price,
  MAX(o.order_date) AS last_sold
FROM products p
LEFT JOIN order_items oi
  ON p.id = oi.product_id
LEFT JOIN orders o
  ON oi.order_id = o.id
GROUP BY p.id, p.product_name, p.price
HAVING MAX(o.order_date) < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
    OR MAX(o.order_date) IS NULL;

Example 3: Employee onboarding tracker

sql
-- Employees and their assigned equipment (if any)
SELECT
  e.name AS employee,
  e.hire_date,
  eq.item_name,
  eq.serial_number
FROM employees e
LEFT JOIN equipment_assignments ea
  ON e.id = ea.employee_id
LEFT JOIN equipment eq
  ON ea.equipment_id = eq.id
WHERE e.hire_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
ORDER BY e.hire_date DESC, e.name;

Visual Summary

JOIN Types Comparison
JOIN Type
Returns
Use Case
INNER JOINOnly matching rows from both tables"Show orders WITH customers"
LEFT JOINAll left table rows + matches from right"Show ALL customers, with orders if any"
RIGHT JOINAll right table rows + matches from leftSame as LEFT (swap tables) - rarely used

Key Takeaways

What you learned:

✅ LEFT JOIN returns all rows from the left table + matches from the right ✅ Non-matching rows get NULL for right table columns ✅ Use WHERE right_table.id IS NULL to find rows with no match ✅ RIGHT JOIN is the mirror of LEFT JOIN (rarely used) ✅ Use COUNT(right_table.column) not COUNT(*) to count matches ✅ COALESCE() converts NULL to a default value (e.g., 0) ✅ Conditions in ON affect the join, conditions in WHERE filter after ✅ LEFT JOIN is essential for finding missing relationships ✅ Always use table prefixes in GROUP BY and SELECT

Coming up next: We'll learn about self-joins to join a table to itself, and UNION operations to combine results from multiple queries!

Practice Exercise: Try these queries:

  1. List all products with their order count (including unsold products)
  2. Find customers who have placed NO orders
  3. Show all employees with their manager names (if they have a manager)
  4. Calculate total revenue per product (including products with $0 revenue)
  5. List all departments with employee count (including empty departments)
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.