JOIN Query Patterns

-- INNER JOIN (only matching rows)
SELECT 
  users.name,
  orders.order_number,
  orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- LEFT JOIN (all from left table)
SELECT 
  users.name,
  orders.order_number,
  orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- Find users with no orders
SELECT users.name
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;

-- Multiple JOINs
SELECT 
  users.name,
  orders.order_number,
  products.name AS product_name,
  order_items.quantity
FROM users
INNER JOIN orders ON users.id = orders.user_id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id;

-- JOIN with WHERE
SELECT 
  users.name,
  orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id
WHERE orders.status = 'completed'
  AND orders.created_at > '2024-01-01';

-- Self JOIN (hierarchical data)
SELECT 
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- CROSS JOIN (Cartesian product)
SELECT 
  colors.name,
  sizes.name
FROM colors
CROSS JOIN sizes;