LEFT OUTER JOIN

-- All users, even those with no orders
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY order_count DESC;

-- Find rows with NO match in right table (anti-join pattern)
SELECT u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;  -- users who never ordered

-- LEFT JOIN with additional filter (be careful with WHERE vs ON!)
-- Filter in WHERE (converts LEFT to INNER for that condition):
SELECT u.name, o.total FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'pending';  -- excludes users with NO orders!

-- Filter in ON clause (preserves all left rows):
SELECT u.name, o.total FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'pending';
-- Users with no pending orders still appear (o.total = NULL)

-- Complete user engagement report (include users with no activity)
SELECT
  u.id,
  u.email,
  u.created_at                                   AS joined_date,
  COUNT(DISTINCT o.id)                           AS total_orders,
  COALESCE(SUM(o.total_amount), 0)               AS total_spent,
  MAX(o.created_at)                              AS last_order_date,
  COUNT(DISTINCT r.id)                           AS reviews_written,
  AVG(r.rating)                                  AS avg_rating_given,
  CASE
    WHEN MAX(o.created_at) IS NULL THEN 'Never ordered'
    WHEN MAX(o.created_at) < NOW() - INTERVAL '90 days' THEN 'Churned'
    ELSE 'Active'
  END AS customer_status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
LEFT JOIN reviews r ON u.id = r.user_id
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.email, u.created_at
ORDER BY total_spent DESC;