How to Use Subqueries in MySQL (Complete Examples)

Imagine you need to find products priced above the average price of all products. Or to find customers who have made a purchase above a certain amount. For needs like these, you need a Subquery — a query written inside another query. Subqueries are one of the most powerful SQL features you must master. This article covers how to use them in various situations.

What Is a Subquery?

A subquery (also called an inner query or nested query) is a SELECT placed inside another query. The query that "wraps" the subquery is called the outer query. A subquery is always enclosed in parentheses () and is usually executed first, before the outer query.

Preparing the Example Data

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    sale_date DATE NOT NULL
) ENGINE=InnoDB;

INSERT INTO products (name, category, price, stock) VALUES
('ASUS Laptop', 'Electronics', 9500000, 10),
('Logitech Mouse', 'Electronics', 350000, 50),
('Mechanical Keyboard', 'Electronics', 750000, 30),
('Wooden Desk', 'Furniture', 2200000, 5),
('Ergonomic Chair', 'Furniture', 3500000, 8),
('MySQL Book', 'Books', 95000, 100),
('27" Monitor', 'Electronics', 4800000, 15);

INSERT INTO sales (product_id, quantity, sale_date) VALUES
(1, 2, '2024-01-10'), (2, 10, '2024-01-11'),
(3, 5, '2024-01-12'), (1, 1, '2024-01-13'),
(5, 2, '2024-01-14'), (7, 3, '2024-01-15');

Subquery in the WHERE Clause

The most common use is placing a subquery in the WHERE clause to compare values:

-- Find products priced above the average of all products
SELECT name, category, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

-- Find the highest-priced product in its category
SELECT name, category, price
FROM products
WHERE price = (SELECT MAX(price) FROM products WHERE category = 'Electronics')
AND category = 'Electronics';

Subquery with IN and NOT IN

Use IN when the subquery returns more than one value:

-- Find products that have been sold
SELECT id, name, price
FROM products
WHERE id IN (SELECT DISTINCT product_id FROM sales);

-- Find products that have never been sold at all
SELECT id, name, price
FROM products
WHERE id NOT IN (SELECT DISTINCT product_id FROM sales);

Subquery in the FROM Clause (Derived Table)

A subquery can serve as a temporary table in the FROM clause. This is called a Derived Table:

-- Calculate total sales per product, then filter those above the average
SELECT
    p.name,
    summary.total_sold,
    summary.total_revenue
FROM products p
INNER JOIN (
    SELECT
        product_id,
        SUM(quantity) AS total_sold,
        SUM(s.quantity * pr.price) AS total_revenue
    FROM sales s
    INNER JOIN products pr ON s.product_id = pr.id
    GROUP BY product_id
) AS summary ON p.id = summary.product_id
WHERE summary.total_sold > (
    SELECT AVG(total) FROM (
        SELECT SUM(quantity) AS total
        FROM sales
        GROUP BY product_id
    ) AS avg_table
)
ORDER BY summary.total_sold DESC;

Subquery in the SELECT Clause (Scalar Subquery)

A subquery can be used as a column in the SELECT clause, but it must return exactly one value (scalar):

-- Show each product along with its category's average price
SELECT
    name,
    category,
    price,
    (SELECT ROUND(AVG(price), 2)
     FROM products p2
     WHERE p2.category = p1.category) AS category_avg_price,
    price - (SELECT ROUND(AVG(price), 2)
             FROM products p2
             WHERE p2.category = p1.category) AS diff_from_avg
FROM products p1
ORDER BY category, price;

Correlated Subquery

A correlated subquery is a subquery that references a column from the outer query. It is re-executed for each row of the outer query:

-- Find products priced higher than their own category's average
SELECT name, category, price
FROM products p1
WHERE price > (
    SELECT AVG(price)
    FROM products p2
    WHERE p2.category = p1.category  -- reference to the outer query
)
ORDER BY category, price DESC;

Subquery with EXISTS and NOT EXISTS

EXISTS checks whether the subquery returns at least one row. It is more efficient than IN for large datasets:

-- Find products that have been sold using EXISTS
SELECT id, name, price
FROM products p
WHERE EXISTS (
    SELECT 1 FROM sales s
    WHERE s.product_id = p.id
);

-- Find products that have never been sold using NOT EXISTS
SELECT id, name, price
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM sales s
    WHERE s.product_id = p.id
);

Subquery vs JOIN: Which Is Faster?

• For most cases in modern MySQL, JOINs tend to be faster because the MySQL optimizer is better at optimizing JOINs.
• Use EXISTS instead of IN for subqueries that return many rows.
• Use EXPLAIN to compare query execution plans.
• Derived Tables in the FROM clause (MySQL 8) can often be replaced with a more readable CTE (WITH).

Conclusion

Subqueries are a powerful tool for writing complex MySQL queries. You can use them in the WHERE clause (with a single comparison, IN, or EXISTS), in the FROM clause as a derived table, or in the SELECT clause as a scalar subquery. Understand the difference between a regular subquery and a correlated subquery, and always use EXPLAIN to ensure your query performance is optimal. By mastering subqueries, your data analysis capabilities with MySQL will improve dramatically.