MySQL Subqueries Scalar, Row, Table Subqueries: Tutorial, Examples, FAQs & Interview Tips

MySQL Subqueries Scalar, Row, Table Subqueries

MySQL in MySQL is best learned by connecting the rule to an order-management database. Start with the smallest SQL statement, observe the output, and then add one realistic constraint so the concept becomes practical.

The key habit for this lesson is to watch table rows, constraints, and indexes as it changes. That makes the topic easier to debug, easier to explain in interviews, and easier to use in real code without memorizing isolated syntax.

What is a Subquery?

A subquery (also called an inner query or nested query) is a SELECT statement embedded inside another SQL statement. Subqueries can appear in the WHERE, FROM, SELECT, and HAVING clauses. They are categorized as scalar (returns one value), row (returns one row), or table (returns multiple rows/columns) subqueries.

Subquery in WHERE - Scalar and IN

-- Scalar subquery: returns a single value
-- Find products priced above the average price
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

-- IN with subquery: find customers who have placed at least one order
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id FROM orders
);

-- NOT IN with subquery: find customers who have NEVER placed an order
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders WHERE customer_id IS NOT NULL
);

-- Subquery with comparison: find the most expensive product
SELECT name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

Subquery in FROM (Derived Table)

A subquery in the FROM clause creates a temporary result set called a derived table. It must be given an alias.

Derived Tables and Subquery in SELECT

-- Derived table: customers with their order count and total spent
SELECT
    d.customer_id,
    d.order_count,
    d.total_spent,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name
FROM (
    SELECT
        customer_id,
        COUNT(*)   AS order_count,
        SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS d
INNER JOIN customers c ON d.customer_id = c.customer_id
WHERE d.order_count >= 2
ORDER BY d.total_spent DESC;

-- Subquery in SELECT clause (scalar subquery per row)
-- Show each product with the count of times it has been ordered
SELECT
    p.product_id,
    p.name,
    p.price,
    (
        SELECT SUM(oi.quantity)
        FROM order_items oi
        WHERE oi.product_id = p.product_id
    ) AS total_ordered
FROM products p
ORDER BY total_ordered DESC;

Correlated Subqueries and EXISTS

A correlated subquery references columns from the outer query - it's re-executed for each row of the outer query. EXISTS returns TRUE if the subquery returns at least one row, and is often more efficient than IN for large datasets.

Correlated Subquery, EXISTS, NOT EXISTS

-- EXISTS: find customers who have at least one delivered order
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id   -- correlated: references outer query
      AND o.status = 'delivered'
);

-- NOT EXISTS: find customers with no orders at all
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- Correlated subquery: find each customer's most recent order
SELECT
    c.customer_id,
    c.first_name,
    o.order_id,
    o.total,
    o.ordered_at
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.ordered_at = (
    SELECT MAX(o2.ordered_at)
    FROM orders o2
    WHERE o2.customer_id = c.customer_id  -- correlated
);

Common table Expressions (CTEs)

A CTE (introduced with the WITH clause in MySQL 8.0) is a named temporary result set that exists only for the duration of a single query. CTEs make complex queries more readable and can be referenced multiple times in the same query. Recursive CTEs can traverse hierarchical data.

WITH Clause - Common table Expressions (CTEs)

-- Simple CTE: customer order summary
WITH customer_summary AS (
    SELECT
        customer_id,
        COUNT(*)   AS order_count,
        SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT
    c.first_name,
    c.last_name,
    cs.order_count,
    cs.total_spent
FROM customers c
INNER JOIN customer_summary cs ON c.customer_id = cs.customer_id
WHERE cs.total_spent > 100
ORDER BY cs.total_spent DESC;

-- Multiple CTEs in one query
WITH
    top_products AS (
        SELECT product_id, SUM(quantity) AS total_sold
        FROM order_items
        GROUP BY product_id
        ORDER BY total_sold DESC
        LIMIT 3
    ),
    product_revenue AS (
        SELECT product_id, SUM(quantity * unit_price) AS revenue
        FROM order_items
        GROUP BY product_id
    )
SELECT
    p.name,
    tp.total_sold,
    pr.revenue
FROM products p
INNER JOIN top_products    tp ON p.product_id = tp.product_id
INNER JOIN product_revenue pr ON p.product_id = pr.product_id
ORDER BY tp.total_sold DESC;

Applied guide for MySQL

Use MySQL when the program needs a clear answer to a specific problem, not because the keyword looks familiar. In a real MySQL task, first name the input, then name the transformation, then name the output. This small discipline shows whether the topic is being used correctly or only copied from an example.

A reliable practice flow is: create the smallest working SQL statement, add one normal case, add one edge case such as missing, repeated, empty, or boundary input, and then confirm the result with result set and EXPLAIN output. If the result surprises you, reduce the code until the behavior is visible again.

The most common trap here is copying the syntax before understanding the behavior. Avoid it by writing one sentence before the code that explains why MySQL is the right choice. After the code runs, verify the lesson by doing this: change one input and explain the changed output.

Identify the exact problem solved by MySQL.
Trace table rows, constraints, and indexes before and after the main operation.
Keep one intentionally broken version and explain the fix.
Connect the example to an order-management database so the idea feels concrete.

Key Takeaways

I can explain where MySQL fits inside an order-management database.
I can point to the exact table rows, constraints, and indexes affected by this topic.
I tested a normal case and an edge case involving missing, repeated, empty, or boundary input.
I verified the result with result set and EXPLAIN output instead of assuming it worked.
I can describe the main mistake: copying the syntax before understanding the behavior.

Common Mistakes to Avoid

WRONG Copying the syntax before understanding the behavior.

RIGHT Write the expected behavior first, then make the example prove it.

A one-line expectation turns the code from copied syntax into a testable idea.

WRONG Practicing only the perfect input.

RIGHT Also test missing, repeated, empty, or boundary input before considering the lesson complete.

The edge case is where most interview follow-up questions begin.

WRONG Looking only at the final output.

RIGHT Trace table rows, constraints, and indexes through each important step.

Tracing makes debugging faster because you can see the first incorrect state.

Practice Tasks

Build one small SQL statement that demonstrates MySQL in an order-management database.
Change the example to include missing, repeated, empty, or boundary input and record the difference.
Break the example by deliberately copying the syntax before understanding the behavior, then write the corrected version.
Explain the finished example in five bullet points: input, operation, output, failure case, and verification.

Frequently Asked Questions

When should I use MySQL?

Use it when the problem matches the behavior shown in the example and when the result can be verified through result set and EXPLAIN output.

How do I avoid mistakes in MySQL?

Start with a tiny case, then test missing, repeated, empty, or boundary input. The main warning sign is copying the syntax before understanding the behavior.

How can I revise MySQL quickly?

Trace table rows, constraints, and indexes, predict the result, run the example, and compare your prediction with the actual output.

Previous Next

MySQL Subqueries Scalar, Row, Table Subqueries: Tutorial, Examples, FAQs & Interview Tips