WHERE Clause on Right Table Converts LEFT JOIN to INNER JOIN

-- Trying to get all customers with high-value orders

SELECT c.name, o.id, o.total

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id

WHERE o.total > 100;

-- Customers without orders excluded!

-- Use AND in ON clause to preserve all customers

SELECT c.name, o.id, o.total

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id AND o.total > 100;

-- All customers included, only high-value orders shown

WHERE o.total > 100 excludes rows where o.total IS NULL (customers without orders). This converts LEFT JOIN to INNER JOIN behavior. To preserve all left table rows while filtering right table, use AND in ON clause. WHERE clause should only filter left table columns.

Rule: Right table filters go in ON clause (AND condition). Left table filters go in WHERE clause.

Critical

Missing customers without orders - defeats LEFT JOIN purpose

Using COUNT(*) Instead of COUNT(column) with LEFT JOIN

-- Trying to count orders per customer

SELECT

c.name,

COUNT(*) AS order_count

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id

GROUP BY c.id, c.name;

-- Customers without orders show count = 1 (wrong!)

-- Use COUNT(column) to exclude NULL

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;

-- Customers without orders show count = 0 (correct!)

COUNT(*) counts all rows including NULL rows. With LEFT JOIN, customers without orders have one NULL row, so COUNT(*) returns 1. COUNT(o.id) counts only non-NULL values, returning 0 for customers without orders. Always use COUNT(column) with LEFT JOIN.

With LEFT JOIN: COUNT(right_table.id) for correct zero counts. COUNT(*) includes NULL rows.

High

Wrong counts - customers without orders show 1 instead of 0

Checking Wrong Column for NULL in Anti-Join

-- Trying to find customers without orders

SELECT c.*