LEFT JOIN and RIGHT JOIN

-- LEFT JOIN: ALL rows from left table, matched rows from right
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
  ON e.department_id = d.id;

-- Employees with no department get NULL for department_name
-- INNER JOIN would have excluded them

LEFT JOIN keeps all rows from the LEFT (first) table. If no match exists in the right table, NULL fills those columns. Use when you cannot afford to lose left-table rows.

SELECT e.name, e.department_id
FROM employees e
LEFT JOIN departments d
  ON e.department_id = d.id
WHERE d.id IS NULL;

This "anti-join" pattern finds rows with no match. Left join, then filter WHERE right-table key IS NULL. Very common for finding orphan records or unassigned data.

SELECT d.department_name,
       COUNT(e.id) AS emp_count
FROM departments d
LEFT JOIN employees e
  ON d.id = e.department_id
GROUP BY d.department_name;

By making departments the LEFT table, all departments appear even with 0 employees. COUNT(e.id) returns 0 for empty departments (not COUNT(*) which would return 1).

-- RIGHT JOIN keeps all rows from the RIGHT table:
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d
  ON e.department_id = d.id;

-- This is equivalent to swapping tables in a LEFT JOIN:
SELECT e.name, d.department_name
FROM departments d
LEFT JOIN employees e
  ON d.id = e.department_id;

RIGHT JOIN is rarely used in practice — most developers just swap the table order and use LEFT JOIN. But you should know it exists for interviews.

SELECT c.customer_name,
       o.order_id,
       o.amount
FROM customers c
LEFT JOIN orders o
  ON c.id = o.customer_id
ORDER BY c.customer_name;

Classic e-commerce query. Customers with no orders show NULL for order_id and amount. This is the correct choice over INNER JOIN when you need all customers.

SELECT p.product_name
FROM products p
LEFT JOIN order_items oi
  ON p.id = oi.product_id
WHERE oi.product_id IS NULL;

Anti-join pattern again — very useful for inventory management, finding dead stock, or unmatched records. Interviewers love this question at e-commerce companies.