MySQL Subqueries

Types of Subqueries

SELECT name 
WHERE age > 
(SELECT AVG(age) 
FROM users);

SELECT * 
WHERE (col1, col2) = 
(SELECT col1, col2 
FROM table);

SELECT * 
WHERE id IN 
(SELECT user_id 
FROM orders);

SELECT * FROM 
(SELECT * FROM users
WHERE active = 1) 
AS active_users;

🔹 Subquery in WHERE Clause

The most common use of subqueries is in the WHERE clause to filter results based on calculated values or data from other tables. The subquery executes first, then its result is used to filter the main query's results.

-- Find employees earning above average
SELECT name, salary, department
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Find products never ordered
SELECT product_name, price
FROM products
WHERE id NOT IN (SELECT DISTINCT product_id FROM orders);

🔹 Subquery in SELECT Clause

Use subqueries in the SELECT clause to calculate values for each row in the result set. This is useful for adding computed columns like counts, averages, or related data without joining tables. Each subquery executes once per row.

-- Show customer with their order count
SELECT 
    customer_name,
    email,
    (SELECT COUNT(*) 
     FROM orders 
     WHERE orders.customer_id = customers.id) AS order_count
FROM customers;

-- Calculate percentage of total
SELECT 
    product_name,
    price,
    (price / (SELECT SUM(price) FROM products) * 100) AS price_percentage
FROM products;

🔹 Subquery in FROM Clause

Subqueries in the FROM clause create temporary result sets that you can query like regular tables. This technique is called a derived table or inline view. It's useful for pre-filtering or pre-aggregating data before the main query processes it.

-- Query a derived table
SELECT dept, avg_salary
FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_averages
WHERE avg_salary > 50000;

-- Multiple subqueries in FROM
SELECT a.name, b.total
FROM (SELECT * FROM customers WHERE active = 1) AS a
JOIN (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) AS b
ON a.id = b.customer_id;

🔹 Correlated Subquery

A correlated subquery references columns from the outer query, executing once for each row processed by the outer query. While powerful, they can be slower than regular subqueries. Use them when you need row-by-row comparisons with related data.

-- Find employees earning more than their department average
SELECT name, salary, department
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

-- Find customers with above-average order totals
SELECT customer_name
FROM customers c
WHERE (
    SELECT AVG(total)
    FROM orders o
    WHERE o.customer_id = c.id
) > 100;

🔹 EXISTS and NOT EXISTS

EXISTS checks if a subquery returns any rows, returning true or false. It's more efficient than IN for large datasets because it stops searching once it finds a match. Use EXISTS for checking relationships between tables without retrieving actual data.

-- Find customers who have placed orders
SELECT customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.id
);

-- Find products with no orders
SELECT product_name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 
    FROM order_items oi 
    WHERE oi.product_id = p.id
);

🔹 IN and NOT IN

IN checks if a value matches any value in a subquery result set. It's intuitive and readable but can be slower than EXISTS for large datasets. Use IN when you need to match against a specific list of values returned by a subquery.

-- Find employees in specific departments
SELECT name, department
FROM employees
WHERE department IN (
    SELECT department_name 
    FROM departments 
    WHERE budget > 100000
);

-- Find products not in any category
SELECT product_name
FROM products
WHERE category_id NOT IN (
    SELECT id FROM categories WHERE active = 1
);

🔹 ANY and ALL Operators

ANY returns true if the condition is true for at least one value in the subquery. ALL returns true only if the condition is true for every value. These operators are useful for comparing a value against multiple results from a subquery.

-- Find products cheaper than ANY product in category 1
SELECT product_name, price
FROM products
WHERE price < ANY (
    SELECT price FROM products WHERE category_id = 1
);

-- Find products more expensive than ALL products in category 2
SELECT product_name, price
FROM products
WHERE price > ALL (
    SELECT price FROM products WHERE category_id = 2
);