Subqueries

Subqueries

-- Scalar subquery (returns one value)
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Subquery in IN
SELECT * FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders WHERE total > 100
);

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

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

-- Subquery in FROM (derived table)
SELECT dept, AVG(salary) AS avg_sal
FROM (SELECT dept, salary FROM employees WHERE is_active = 1) t
GROUP BY dept;