Subqueries

Use subqueries to perform complex queries by nesting SELECT statements.

Subquery in WHERE Clause

-- Find products more expensive than average
SELECT product_id, name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Subquery with IN

-- Find users who have placed orders
SELECT user_id, name, email
FROM users
WHERE user_id IN (
  SELECT DISTINCT user_id
  FROM orders
);

Subquery with NOT IN

-- Find users who haven't placed orders
SELECT user_id, name, email
FROM users
WHERE user_id NOT IN (
  SELECT DISTINCT user_id
  FROM orders
);

Subquery with EXISTS

-- Find products that have been ordered
SELECT product_id, name, price
FROM products p
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.product_id = p.product_id
);

Subquery with NOT EXISTS

-- Find products never ordered
SELECT product_id, name, price
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.product_id = p.product_id
);

Correlated Subquery

-- Find products with above-category-average price
SELECT p1.product_id, p1.name, p1.price, p1.category
FROM products p1
WHERE p1.price > (
  SELECT AVG(p2.price)
  FROM products p2
  WHERE p2.category = p1.category
);

Subquery in FROM Clause (Derived Table)

-- Get average order value by user
SELECT u.user_id, u.name, avg_order.avg_total
FROM users u
JOIN (
  SELECT user_id, AVG(total) as avg_total
  FROM orders
  GROUP BY user_id
) avg_order ON u.user_id = avg_order.user_id;

Subquery in SELECT Clause

-- Get user with order count
SELECT
  u.user_id,
  u.name,
  (SELECT COUNT(*)
   FROM orders o
   WHERE o.user_id = u.user_id) as order_count
FROM users u;

Multiple Row Subquery with ANY

-- Find products cheaper than ANY premium product
SELECT product_id, name, price
FROM products
WHERE price < ANY (
  SELECT price
  FROM products
  WHERE category = 'Premium'
);

Multiple Row Subquery with ALL

-- Find products cheaper than ALL premium products
SELECT product_id, name, price
FROM products
WHERE price < ALL (
  SELECT price
  FROM products
  WHERE category = 'Premium'
);

Complex Nested Subquery

-- Find users whose total spending exceeds average
SELECT u.user_id, u.name
FROM users u
WHERE (
  SELECT SUM(o.total)
  FROM orders o
  WHERE o.user_id = u.user_id
) > (
  SELECT AVG(total_spending)
  FROM (
    SELECT user_id, SUM(total) as total_spending
    FROM orders
    GROUP BY user_id
  ) user_totals
);