Subquery Patterns

-- Subquery in WHERE with IN
SELECT * 
FROM users 
WHERE id IN (
  SELECT user_id 
  FROM orders 
  WHERE status = 'completed'
);

-- NOT IN subquery
SELECT * 
FROM users 
WHERE id NOT IN (
  SELECT user_id 
  FROM orders
);

-- Subquery with comparison
SELECT * 
FROM products 
WHERE price > (
  SELECT AVG(price) 
  FROM products
);

-- Correlated subquery
SELECT 
  u.name,
  (SELECT COUNT(*) 
   FROM orders o 
   WHERE o.user_id = u.id) AS order_count
FROM users u;

-- EXISTS subquery
SELECT * 
FROM users u
WHERE EXISTS (
  SELECT 1 
  FROM orders o 
  WHERE o.user_id = u.id
);

-- NOT EXISTS
SELECT * 
FROM users u
WHERE NOT EXISTS (
  SELECT 1 
  FROM orders o 
  WHERE o.user_id = u.id
);

-- Subquery in FROM (derived table)
SELECT 
  category,
  avg_price
FROM (
  SELECT 
    category,
    AVG(price) AS avg_price
  FROM products
  GROUP BY category
) AS product_stats
WHERE avg_price > 100;

-- Subquery with JOIN
SELECT 
  u.name,
  recent_orders.order_count
FROM users u
JOIN (
  SELECT 
    user_id,
    COUNT(*) AS order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
) AS recent_orders ON u.id = recent_orders.user_id;

-- Multiple subqueries
SELECT 
  name,
  (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS orders,
  (SELECT SUM(total) FROM orders WHERE user_id = users.id) AS total_spent
FROM users;

-- Subquery with ANY
SELECT * 
FROM products 
WHERE price > ANY (
  SELECT price 
  FROM products 
  WHERE category = 'electronics'
);

-- Subquery with ALL
SELECT * 
FROM products 
WHERE price > ALL (
  SELECT price 
  FROM products 
  WHERE category = 'books'
);