What is the difference between LEFT JOIN and INNER JOIN? When would you use each?

LEFT JOIN preserves ALL rows from left table, filling non-matching right columns with NULL.

INNER JOIN returns ONLY rows where join condition matches in both tables.

Example: 5 customers, 3 have orders

INNER JOIN customers to orders:

- Returns 3 customers (only those with orders)

- Orders without customers excluded

- Result: 3 rows

LEFT JOIN customers to orders:

- Returns 5 customers (all customers)

- Customers without orders show NULL order columns

- Result: 5 rows (2 with NULL orders)

Use INNER JOIN when: You only want valid relationships (orders must have customers)

Use LEFT JOIN when: You need all left table rows (all customers including those without orders)

Rule: LEFT JOIN for complete data. INNER JOIN for valid relationships only.

How do you find customers who have never placed an order using LEFT JOIN?

Use LEFT JOIN with WHERE IS NULL (anti-join pattern):

SELECT c.id, c.name, c.email

FROM customers c

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

WHERE o.id IS NULL;

How it works:

1. LEFT JOIN preserves all customers

2. Customers with orders: order columns populated

3. Customers without orders: order columns NULL

4. WHERE o.id IS NULL filters to customers without orders

Critical: Check right table PRIMARY KEY (o.id) for NULL, not foreign key (o.customer_id).

Foreign key can be NULL for other reasons (orphaned orders).

This is called "anti-join" - finding rows in left table without matches in right table.

Why is RIGHT JOIN rarely used? How can you rewrite it as LEFT JOIN?

RIGHT JOIN is rarely used because A RIGHT JOIN B is equivalent to B LEFT JOIN A.

RIGHT JOIN (confusing):

SELECT o.id, c.name

FROM customers c

RIGHT JOIN orders o ON c.id = o.customer_id;

Equivalent LEFT JOIN (preferred):

SELECT o.id, c.name

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.id;

Same result, but LEFT JOIN is: