LEFT and RIGHT JOIN

LEFT JOIN returns every row from the left table whether or not it has a matching row in the right table. For rows that have a match, right-side columns are populated with the matched values. For rows with no match, right-side columns are filled with NULL. INNER JOIN returns only rows where the join condition is satisfied in both tables — unmatched rows from either side are excluded.

The key behavioural difference: INNER JOIN filters the left table — only rows with a match survive. LEFT JOIN never filters the left table — every row appears at least once. With a customers-to-orders LEFT JOIN, every customer appears in the result. With an INNER JOIN, only customers who have placed at least one order appear.

When to choose which: use INNER JOIN when you only care about rows that have related data on both sides — orders with known customers, order items with known products. The FK constraints in a well-designed schema mean INNER JOIN and LEFT JOIN produce the same result for required relationships. Use LEFT JOIN when you need all rows from the left table regardless of whether a match exists — all products including unsold, all customers including those who have never ordered, all stores including those with no employees. The presence of NULL values in the right-side columns after LEFT JOIN carries meaning: it signals the absence of a relationship that the query was looking for.

The LEFT JOIN + WHERE IS NULL pattern is called an anti-join. It finds rows in the left table that have no corresponding row in the right table. The structure is: SELECT left.* FROM left_table LEFT JOIN right_table ON left.key = right.key WHERE right.key IS NULL.

Why it works: LEFT JOIN produces NULLs in all right-side columns for every left row that found no match. The WHERE right.key IS NULL filter then keeps only those NULL rows — the unmatched ones. Rows that did match have a real key value (not NULL) and are excluded by the IS NULL filter. The result is exclusively the left rows with no right-side match.

This pattern answers "which X has no Y?" — customers who have never ordered, products that have never been sold, employees without store assignments, stores with no employees, orders with no line items. It is one of the five SQL patterns every analyst must know. The anti-join is functionally equivalent to NOT EXISTS and NOT IN (for non-NULL cases), but LEFT JOIN + IS NULL is often the most readable and performs well with proper indexing on the join column. It appears constantly in retention analysis (lapsed users), inventory management (unsold stock), and data quality checks (orphaned records).

LEFT JOIN adds NULL values to right-side columns for unmatched left rows. When a WHERE condition references a right-table column, it evaluates that condition on the NULL values. In SQL's three-valued logic, any comparison with NULL returns NULL — not TRUE and not FALSE. WHERE discards all rows where the condition does not evaluate to TRUE — which includes NULL. So rows with NULL right-side columns (the unmatched rows LEFT JOIN was supposed to preserve) are discarded by the WHERE filter.

Example: customers LEFT JOIN orders — all customers appear, with NULL order columns for unordered customers. Adding WHERE o.order_status = 'Delivered' evaluates 'Delivered' = NULL for unordered customers, which returns NULL, and WHERE discards those rows. The final result contains only customers with at least one delivered order — identical to INNER JOIN.

The fix: move the right-table filter to the ON clause. ON o.order_status = 'Delivered' is applied during the join itself, before NULL rows are added. It restricts which right-table rows can match, but does not filter the left table. Customers with no delivered orders still appear — they just have NULL order columns because no delivered order matched. The decision rule: for LEFT JOIN, any condition that references right-table columns and should preserve unmatched left rows belongs in ON. Only left-table conditions and aggregate-level conditions belong in WHERE.

To count records per group including groups with a count of zero, use LEFT JOIN from the group dimension table to the fact table, then COUNT the fact table's primary key (not COUNT(*)), combined with GROUP BY on the dimension table columns.

The key is COUNT(fact_table.pk) — not COUNT(*). COUNT(*) counts all rows including the NULL rows that LEFT JOIN adds for unmatched groups — it would return 1 for groups with no facts, not 0. COUNT(fact_table.pk) counts non-NULL values of the fact table's primary key. For unmatched groups, the pk is NULL, so COUNT returns 0. For matched groups, COUNT returns the number of matched rows.

Example: to count orders per store including stores with no orders — SELECT s.store_id, s.city, COUNT(o.order_id) AS order_count FROM stores AS s LEFT JOIN orders AS o ON s.store_id = o.store_id GROUP BY s.store_id, s.city. Stores with no orders have o.order_id = NULL, so COUNT(o.order_id) = 0. If you use COUNT(*) instead, stores with no orders would incorrectly show 1 (the NULL row is still a row). The same principle applies to SUM — COALESCE(SUM(o.total_amount), 0) returns 0 instead of NULL for groups with no matching rows, since SUM of an empty set returns NULL.

All three find rows in the left set that have no match in the right set, but they differ in NULL handling, performance, and readability. NOT IN is the most dangerous: NOT IN (subquery) returns zero rows if the subquery returns any NULL value — because NOT IN (1, 2, NULL) evaluates as NOT (col = 1 OR col = 2 OR col = NULL), and since col = NULL is always NULL, the entire expression is NULL for every row. This is a silent correctness bug. Only use NOT IN when you are absolutely certain the subquery column has no NULLs and will remain NULL-free.

NOT EXISTS is NULL-safe and usually the most semantically clear — "return rows where no matching row exists." The correlated subquery checks each left row individually. Most query optimisers convert NOT EXISTS to an efficient anti-join plan internally. NOT EXISTS short-circuits — it stops looking as soon as one match is found, making it efficient when matches are common.

LEFT JOIN + IS NULL is also NULL-safe and usually the most familiar pattern for analysts who think in terms of table joins. It produces an explicit NULL for unmatched rows that the IS NULL filter then selects. Performance is generally equivalent to NOT EXISTS on modern optimisers — both are translated to anti-join plans internally. LEFT JOIN + IS NULL has one practical advantage: it allows you to also SELECT the right-table columns (to see the NULLs themselves) which is useful for debugging. NOT EXISTS cannot return right-table columns at all since the subquery's SELECT is not referenced. Preference in production code: NOT EXISTS for maximum semantic clarity, LEFT JOIN + IS NULL for analytical queries where seeing the NULL columns is useful for verification.

Cause: A WHERE condition on a right-table column is filtering out the NULL rows that LEFT JOIN preserves for unmatched left rows. WHERE o.order_status = 'Delivered' on NULL order_status evaluates to NULL, which WHERE discards. The result excludes all customers with no delivered orders — identical to INNER JOIN behaviour.

Fix: Move right-table filter conditions from WHERE to the ON clause: LEFT JOIN orders AS o ON c.customer_id = o.customer_id AND o.order_status = 'Delivered'. The AND condition in ON restricts which right rows participate in matching, but does not filter the left table. Unmatched left rows (customers with no delivered orders) appear with NULL order columns. To diagnose: compare SELECT COUNT(*) FROM customers to the count from your LEFT JOIN query — if they differ, a WHERE condition is filtering out left rows.

Cause: The LEFT JOIN is being accidentally converted to an INNER JOIN by a WHERE condition on a right-table column (see above), so no NULL rows survive to be caught by IS NULL. Alternatively, every left-table row genuinely does have a match in the right table — the anti-join correctly returns zero rows because there are no unmatched rows.

Fix: Verify by running the LEFT JOIN without the WHERE IS NULL filter: SELECT COUNT(*) FROM left AS l LEFT JOIN right AS r ON l.key = r.key WHERE r.key IS NULL. If this returns 0 but SELECT COUNT(*) FROM left returns rows, every left row has a match — the anti-join correctly found nothing. If the counts differ but your WHERE IS NULL query still returns 0, check for a secondary WHERE condition on right-table columns that is eliminating the NULLs before IS NULL can filter them.

Cause: You used COUNT(*) after a LEFT JOIN. COUNT(*) counts all rows, including the NULL rows LEFT JOIN adds for unmatched groups. A store with no orders gets one NULL row in the joined result — COUNT(*) counts that row and returns 1, not 0.

Fix: Use COUNT(right_table.primary_key) instead of COUNT(*): COUNT(o.order_id) returns 0 for unmatched rows because order_id is NULL and COUNT(column) ignores NULLs. COALESCE is not needed for COUNT — it already returns 0 for all-NULL groups. For SUM and AVG on unmatched groups, wrap with COALESCE: COALESCE(SUM(o.total_amount), 0) returns 0 instead of NULL.

Cause: The NOT IN subquery returns at least one NULL value. NOT IN (1, 2, NULL) evaluates as NOT (col = 1 OR col = 2 OR col = NULL). Since col = NULL is always NULL (not TRUE), the entire OR expression can never be definitively TRUE for any col value — so NOT (NULL) = NULL for every row. WHERE discards all NULL rows, returning zero results.

Fix: Replace NOT IN with LEFT JOIN + IS NULL or NOT EXISTS — both handle NULLs correctly. If you must use NOT IN, add a WHERE filter to the subquery: WHERE subquery_column IS NOT NULL — ensuring the subquery never returns NULLs. Verify: SELECT customer_id FROM orders WHERE customer_id IS NULL — if this returns any rows, your NOT IN subquery is returning NULLs and will silently return zero rows.

Cause: A customer has multiple orders, so LEFT JOIN creates one row per (customer, order) pair. Without GROUP BY, each order for the same customer is a separate row — this is expected behaviour. If you then GROUP BY without listing all non-aggregate SELECT columns, the GROUP BY collapses inconsistently and produces unexpected duplicates.

Fix: Add GROUP BY for all non-aggregate columns in SELECT: GROUP BY c.customer_id, c.first_name, c.last_name, c.city, c.loyalty_tier. Use aggregate functions for order-level data: COUNT(o.order_id) for order count, SUM(o.total_amount) for total spend, MAX(o.order_date) for last order date. If you genuinely want one row per customer with aggregated order data, GROUP BY is required. If you want one row per order with customer details, do not GROUP BY — the multiple rows per customer are correct.

Write three queries using LEFT JOIN: (1) A query showing all product categories with their total delivered revenue and order count — including categories with zero sales. Show category, order_count, total_revenue (0 if none), and a 'sales_status' column: 'Active' if revenue > 0, 'No Sales' if zero. (2) An anti-join finding all customers who joined in 2023 or later but have never placed a delivered order. Show customer_id, full name, email, joined_date. (3) A query showing each store's employee count — including stores with no employees (show 0). Show store_id, city, employee_count, and avg_salary (NULL is fine for empty stores).