1. LEFT JOIN

Use when: You want all records from the left table (A), with matches from the right (B).

SELECT customers.name, orders.id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

2. INNER JOIN

Use when: You only want records that exist in both tables.

SELECT orders.id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

3. RIGHT JOIN

Use when: You want all records from the right table (B), with matches from the left (A).

SELECT products.name, order_items.order_id
FROM order_items
RIGHT JOIN products ON order_items.product_id = products.id;

4. LEFT JOIN + WHERE B IS NULL (Anti-Join)

Use when: You want records in A that do not have matches in B.

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

5. RIGHT JOIN + WHERE A IS NULL

Use when: You want records in B that do not have matches in A.

SELECT products.name
FROM order_items
RIGHT JOIN products ON order_items.product_id = products.id
WHERE order_items.product_id IS NULL;

6. FULL OUTER JOIN

Use when: You want all records from both tables, matched when possible.

SELECT students.name, advisors.name
FROM students
FULL OUTER JOIN advisors ON students.advisor_id = advisors.id;

7. FULL OUTER JOIN + WHERE A IS NULL OR B IS NULL

Use when: You want only non-matching rows from both sides.

SELECT customers.name, orders.id
FROM customers
FULL OUTER JOIN orders ON customers.id = orders.customer_id
WHERE customers.id IS NULL OR orders.customer_id IS NULL;