Advanced SQL joins and query optimization

-- FULL OUTER JOIN: All rows from both tables
SELECT
  COALESCE(a.name, 'No author') as author,
  COALESCE(b.title, 'No book') as book
FROM authors a
FULL OUTER JOIN books b ON a.id = b.author_id;

-- CROSS JOIN: Cartesian product (use carefully!)
SELECT
  sizes.name as size,
  colors.name as color
FROM sizes
CROSS JOIN colors
WHERE sizes.active = true AND colors.active = true;

-- Join with subquery for filtering
SELECT
  u.name,
  u.email,
  recent_orders.order_count
FROM users u
INNER JOIN (
  SELECT
    user_id,
    COUNT(*) as order_count
  FROM orders
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
  HAVING COUNT(*) >= 3
) recent_orders ON u.id = recent_orders.user_id;

-- LATERAL join (PostgreSQL): Correlated subquery per row
SELECT
  u.name,
  latest_order.order_number,
  latest_order.total
FROM users u
CROSS JOIN LATERAL (
  SELECT order_number, total, created_at
  FROM orders
  WHERE orders.user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) latest_order;

-- Anti-join: Find users with NO orders
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- More efficient anti-join with NOT EXISTS
SELECT u.*
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
);

-- Join with conditional logic
SELECT
  p.name as product,
  CASE
    WHEN i.quantity > 100 THEN 'High Stock'
    WHEN i.quantity > 20 THEN 'Medium Stock'
    WHEN i.quantity > 0 THEN 'Low Stock'
    ELSE 'Out of Stock'
  END as stock_status,
  COALESCE(i.quantity, 0) as current_stock
FROM products p
LEFT JOIN inventory i ON p.id = i.product_id;