LEFT and RIGHT JOIN

LEFT JOIN

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

Every customer appears. Those with no orders get nulls in the order columns.

RIGHT JOIN is the mirror image. It is rarely used in practice — most people reorder the tables and use LEFT, because reading a query is easier when the preserved table comes first.

Finding what is missing

The anti-join pattern:

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

The IS NULL test keeps only rows where the join found nothing — customers who have never ordered. This is one of the most-used patterns in analytics.

The WHERE trap

-- silently becomes an INNER JOIN
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'shipped';

WHERE is applied after the join. Customers with no orders have o.status = NULL, which fails the test, so they are discarded — defeating the entire purpose of the LEFT JOIN.

Put the condition in ON instead:

LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.status = 'shipped'

The rule: conditions on the preserved table go in WHERE; conditions on the optional table go in ON. This is asked in almost every SQL interview.

Watch out for counts

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

A customer with no orders gets 1, not 0 — because COUNT(*) counts the null-extended row. Use COUNT(o.order_id), which skips nulls and correctly returns 0.