LEFT & RIGHT JOIN — MySQL

LEFT JOIN and RIGHT JOIN in MySQL

Outer joins keep rows from one table even when there is no matching row in the other table. Where no match exists, MySQL fills the missing columns with NULL. This makes outer joins essential for reporting ("show me all customers, even those who never ordered") and for finding missing relationships ("which products have never been sold?").

LEFT JOIN — All Rows from the Left Table

LEFT JOIN (full name: LEFT OUTER JOIN) returns:

Every row from the left (first-named) table.
Matching columns from the right table where a match exists.
NULL for every right-table column where no match exists.

SQL

-- All customers with their order count (0 for customers who never ordered)
SELECT
  c.customer_id,
  c.first_name,
  c.email,
  COUNT(o.order_id) AS order_count,
  ROUND(COALESCE(SUM(o.total), 0), 2) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.email
ORDER BY total_spent DESC;

Seeing NULL in Non-Matching Rows

SQL

-- Sample data
INSERT INTO customers (customer_id, first_name, email) VALUES
  (1, 'Alice', 'alice@example.com'),
  (2, 'Bob',   'bob@example.com'),
  (3, 'Carol', 'carol@example.com');   -- Carol has no orders

INSERT INTO orders (order_id, customer_id, total) VALUES
  (101, 1, 75.00),
  (102, 1, 120.00),
  (103, 2, 45.00);

-- LEFT JOIN: Carol appears with NULL order columns
SELECT c.first_name, o.order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id;

first_name	order_id	total
Alice	101	75.00
Alice	102	120.00
Bob	103	45.00
Carol	NULL	NULL

Note

Carol appears in the result even though she has no orders. Her order_id and total columns contain NULL. This is the defining behavior of LEFT JOIN.

Anti-Join Pattern — Finding Rows with No Match

The most powerful use of LEFT JOIN is finding rows that have no corresponding row in another table. Add WHERE right_table.pk IS NULL to isolate non-matching rows. This is often faster than NOT IN (which has the NULL trap) and more readable than NOT EXISTS.

SQL

-- Customers who have NEVER placed an order
SELECT c.customer_id, c.first_name, c.email
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Products that have NEVER been sold
SELECT p.product_id, p.name, p.price
FROM products    AS p
LEFT JOIN order_items AS oi ON p.product_id = oi.product_id
WHERE oi.item_id IS NULL;

-- Categories with no active products
SELECT cat.category_id, cat.name AS category
FROM categories AS cat
LEFT JOIN products AS p
  ON  cat.category_id = p.category_id
  AND p.is_active     = 1
WHERE p.product_id IS NULL;

-- Employees with no performance review this year
SELECT e.employee_id, e.full_name, e.department
FROM employees   AS e
LEFT JOIN performance_reviews AS pr
  ON  e.employee_id = pr.employee_id
  AND YEAR(pr.review_date) = YEAR(CURDATE())
WHERE pr.review_id IS NULL;

Tip

Always test the NULL check against the right table's PRIMARY KEY or a NOT NULL column. Testing against a nullable column can give incorrect results because that column might contain genuine NULLs that have nothing to do with missing matches.

LEFT JOIN vs INNER JOIN — Output Comparison

SQL

-- INNER JOIN: Carol (no orders) is excluded
SELECT c.first_name, COUNT(o.order_id) AS order_count
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name;
-- Result rows: Alice (2 orders), Bob (1 order)

-- LEFT JOIN: Carol appears with count 0
SELECT c.first_name, COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name;
-- Result rows: Alice (2), Bob (1), Carol (0)

Note

COUNT(o.order_id) correctly returns 0 for Carol. Counting a specific column ignores NULLs, which is what you want when the right-table column is NULL because there is no match. Never use COUNT(*) in a LEFT JOIN aggregate if you want accurate zero counts.

Critical Distinction — ON Filter vs WHERE Filter

This is the most subtle and important LEFT JOIN behavior. Putting a filter in the ON clause applies it during the join: some right-table rows are excluded but every left-table row is preserved (with NULLs where the filtered rows would have matched). Putting a filter in WHERE applies it after the join, which removes any left-table rows that ended up with NULLs — converting the LEFT JOIN back into an effective INNER JOIN.

SQL

-- ON filter: all customers appear; only their 'delivered' orders are joined
-- Customers with no delivered orders appear with NULL order columns
SELECT c.first_name, o.order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o
  ON  c.customer_id = o.customer_id
  AND o.status      = 'delivered';    -- filter in ON: preserves outer join

-- WHERE filter: customers with no delivered orders are REMOVED
-- This is effectively an INNER JOIN on delivered orders
SELECT c.first_name, o.order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.status = 'delivered';         -- filter in WHERE: kills outer join behavior

Warning

This WHERE-vs-ON distinction is the single most common source of incorrect LEFT JOIN results. If you write a LEFT JOIN but then add a right-table filter in WHERE (other than IS NULL), you lose the customers/rows that had no match. Move the filter to ON to preserve outer join behavior.

RIGHT JOIN

RIGHT JOIN is the mirror of LEFT JOIN — it keeps all rows from the right (second) table and fills in NULLs for the left table where no match exists.

SQL

-- RIGHT JOIN: all orders appear, even orphaned ones with no matching customer
SELECT o.order_id, o.total, c.first_name
FROM customers AS c
RIGHT JOIN orders AS o ON c.customer_id = o.customer_id;

-- This is equivalent to:
SELECT o.order_id, o.total, c.first_name
FROM orders    AS o
LEFT JOIN customers AS c ON c.customer_id = o.customer_id;

Converting RIGHT JOIN to LEFT JOIN (Best Practice)

Most SQL style guides recommend avoiding RIGHT JOIN. Every RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order. Using only LEFT JOINs makes queries easier to scan because the anchor table (the one whose rows are always kept) is always on the left.

SQL

-- RIGHT JOIN: messy to read when mixed with other JOINs
SELECT o.order_id, oi.product_id, p.name
FROM products    AS p
RIGHT JOIN order_items AS oi ON p.product_id  = oi.product_id
RIGHT JOIN orders      AS o  ON oi.order_id   = o.order_id;

-- Rewritten as LEFT JOINs (same result, more consistent)
SELECT o.order_id, oi.product_id, p.name
FROM orders      AS o
LEFT JOIN order_items AS oi ON o.order_id    = oi.order_id
LEFT JOIN products    AS p  ON oi.product_id = p.product_id;

Multiple LEFT JOINs

SQL

-- Employee directory with optional department, manager, and desk location
SELECT
  e.employee_id,
  e.full_name,
  COALESCE(d.name, 'No Department')   AS department,
  COALESCE(m.full_name, 'No Manager') AS manager,
  COALESCE(desk.location, 'No Desk')  AS desk_location
FROM employees     AS e
LEFT JOIN departments AS d    ON e.department_id = d.department_id
LEFT JOIN employees   AS m    ON e.manager_id    = m.employee_id
LEFT JOIN desks       AS desk ON e.desk_id       = desk.desk_id
ORDER BY d.name, e.full_name;

Practical Reporting Examples

SQL

-- Monthly new customer acquisition and first-order conversion rate
SELECT
  DATE_FORMAT(c.created_at, '%Y-%m')    AS cohort_month,
  COUNT(DISTINCT c.customer_id)          AS new_customers,
  COUNT(DISTINCT o.customer_id)          AS customers_with_orders,
  ROUND(
    COUNT(DISTINCT o.customer_id) * 100.0 /
    NULLIF(COUNT(DISTINCT c.customer_id), 0),
  1)                                     AS conversion_pct
FROM customers AS c
LEFT JOIN orders AS o
  ON  c.customer_id = o.customer_id
  AND o.status      = 'delivered'
GROUP BY cohort_month
ORDER BY cohort_month;

-- Catalog audit: products missing description or primary image
SELECT
  p.product_id,
  p.name,
  p.price,
  CASE WHEN pd.product_id IS NULL THEN 'MISSING' ELSE 'OK' END AS description_status,
  CASE WHEN pi.image_url  IS NULL THEN 'MISSING' ELSE 'OK' END AS image_status
FROM products              AS p
LEFT JOIN product_details  AS pd ON p.product_id = pd.product_id
LEFT JOIN product_images   AS pi
  ON  p.product_id = pi.product_id
  AND pi.is_primary = 1
WHERE pd.product_id IS NULL
   OR pi.product_id IS NULL
ORDER BY p.product_id;

-- Revenue report: all categories with their revenue (including zero-revenue categories)
SELECT
  cat.category_id,
  cat.name                                    AS category,
  COUNT(DISTINCT o.order_id)                  AS orders,
  COALESCE(ROUND(SUM(oi.quantity * oi.unit_price), 2), 0) AS revenue
FROM categories  AS cat
LEFT JOIN products    AS p   ON cat.category_id = p.category_id
LEFT JOIN order_items AS oi  ON p.product_id    = oi.product_id
LEFT JOIN orders      AS o
  ON  oi.order_id = o.order_id
  AND o.status    = 'delivered'
GROUP BY cat.category_id, cat.name
ORDER BY revenue DESC;

Quick Comparison Table

Aspect	LEFT JOIN	RIGHT JOIN	INNER JOIN
Anchor table	Left (first) table	Right (second) table	Neither — both must match
Non-matching rows	Left rows kept with NULL right cols	Right rows kept with NULL left cols	Excluded entirely
Anti-join pattern	WHERE right.pk IS NULL	WHERE left.pk IS NULL	Not applicable
Recommended?	Yes — preferred outer join style	Avoid — rewrite as LEFT JOIN	Yes — default join type
COUNT(*) accuracy	Use COUNT(right.pk) for zeros	Use COUNT(left.pk) for zeros	COUNT(*) is fine

Use LEFT JOIN when the left table's rows must always appear in the result
Use the anti-join pattern (LEFT JOIN + WHERE right.pk IS NULL) to find orphaned rows
Move right-table filter conditions to ON, not WHERE, to preserve outer join behavior
Rewrite all RIGHT JOINs as LEFT JOINs for consistent, readable query style
Use COUNT(right_table.pk) instead of COUNT(*) to get accurate zero counts in LEFT JOIN aggregations
Use COALESCE to replace NULLs in LEFT JOIN output with meaningful default values