MySQL Subqueries Cheatsheet

This cheatsheet covers the usage of subqueries in MySQL, including different types of subqueries and common use cases.

Basic Subquery Structure
Types of Subqueries
- Scalar Subquery
- Row Subquery
- Column Subquery
- Table Subquery
Subquery Operators
- IN / NOT IN
- EXISTS / NOT EXISTS
- ANY / SOME
- ALL
Correlated Subqueries
Subqueries in SELECT Clause
Subqueries in FROM Clause
Common Table Expressions (CTEs)

Basic Subquery Structure

A subquery is a query nested inside another query.

SELECT column(s)
FROM table1
WHERE column_name operator (SELECT column_name FROM table2 WHERE condition);

Types of Subqueries

Scalar Subquery

Returns a single value and can be used with comparison operators.

-- Example: Find employees who earn more than the average salary
SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Row Subquery

Returns a single row of values.

-- Example: Find the employee with the highest salary in each department
SELECT *
FROM employees e1
WHERE (department_id, salary) = (
    SELECT department_id, MAX(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
    GROUP BY department_id
);

Column Subquery

Returns a single column of multiple rows.

-- Example: Find all departments that have at least one employee
SELECT department_name
FROM departments
WHERE department_id IN (SELECT DISTINCT department_id FROM employees);

Table Subquery

Returns multiple columns and multiple rows.

-- Example: Find the top 3 highest paid employees in each department
SELECT department_id, employee_name, salary
FROM (
    SELECT department_id, employee_name, salary,
           RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
    FROM employees
) ranked_employees
WHERE salary_rank <= 3;

Subquery Operators

IN / NOT IN

Checks if a value matches any value in the subquery result.

-- Example: Find all products that have been ordered
SELECT product_name
FROM products
WHERE product_id IN (SELECT DISTINCT product_id FROM order_items);

EXISTS / NOT EXISTS

Checks if the subquery returns any rows.

-- Example: Find customers who have placed at least one order
SELECT customer_name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

ANY / SOME

Returns TRUE if any of the subquery values meet the condition.

-- Example: Find products more expensive than any product in category 1
SELECT product_name, price
FROM products
WHERE price > ANY (SELECT price FROM products WHERE category_id = 1);

ALL

Returns TRUE if all of the subquery values meet the condition.

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

Correlated Subqueries

A subquery that depends on the outer query for its values.

-- Example: Find employees who earn more than the average salary in their department
SELECT employee_name, department_id, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

Subqueries in SELECT Clause

You can use subqueries in the SELECT clause to calculate values for each row.

-- Example: Show each employee's salary and the difference from their department's average
SELECT 
    employee_name,
    department_id,
    salary,
    salary - (
        SELECT AVG(salary)
        FROM employees e2
        WHERE e2.department_id = e1.department_id
    ) AS salary_difference
FROM employees e1;

Subqueries in FROM Clause

Subqueries in the FROM clause are also known as derived tables or inline views.

-- Example: Rank employees by salary within their department
SELECT employee_name, department_id, salary, salary_rank
FROM (
    SELECT 
        employee_name,
        department_id,
        salary,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
    FROM employees
) AS ranked_employees
WHERE salary_rank <= 3;

Common Table Expressions (CTEs)

CTEs provide a more readable alternative to complex subqueries.

-- Example: Find the total sales for each product category
WITH product_sales AS (
    SELECT p.category_id, oi.product_id, oi.quantity * oi.price AS sale_amount
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
)
SELECT c.category_name, SUM(ps.sale_amount) AS total_sales
FROM product_sales ps
JOIN categories c ON ps.category_id = c.category_id
GROUP BY c.category_id, c.category_name;

📚Cheatsheets