Mastering Subqueries

Part 1: Concept Notes: Subqueries

Introduction

What is it? A subquery is a SELECT statement written inside another SQL statement.

• The outer query is the main query you run.
• The inner query (the subquery) is the SELECT written inside it.
• SQL works out the inner query, and then uses its result to finish the outer query.

Why do we need it? Some questions cannot be answered in one simple step. "Which orders are above the average order amount?" needs two things: first the average, and then the comparison. "Which users have never placed an order?" needs a look at one table while reading another. A subquery lets you ask the smaller question inside the bigger one, all in a single statement.

Real-world analogy A manager asks: "Give me the names of everyone who earns more than the team average." You cannot answer that until you first calculate the team average. So you do the small job first (find the average), and then use that number to do the big job (list the people). The small job is the subquery. The big job is the outer query.

Business use case A shop wants a list of customers who have never bought anything, so it can send them a welcome coupon. The outer query reads the customers table. The subquery checks the orders table for each customer. Only customers with no matching order are kept.

Dataset Used

Three connected tables: users, products, and orders. Each order belongs to one user and (optionally) one product.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  name    VARCHAR(50) NULL
) ENGINE=InnoDB;

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product    VARCHAR(50) NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  order_id   INT PRIMARY KEY,
  user_id    INT NOT NULL,
  product_id INT NULL,
  amount     DECIMAL(10,2) NULL,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(user_id),
  CONSTRAINT fk_orders_product
    FOREIGN KEY (product_id) REFERENCES products(product_id)
) ENGINE=InnoDB;

INSERT INTO users (user_id, name) VALUES
(1, 'Ayaan'), (2, 'Sneha'), (3, 'Rohit'), (4, 'Kavya'),
(5, 'Zoya'), (6, 'Arjun'), (7, ''), (8, NULL);

INSERT INTO products (product_id, product) VALUES
(10, 'Tablet'), (11, 'Headphones'), (12, 'Keyboard');

INSERT INTO orders (order_id, user_id, product_id, amount) VALUES
(101, 1, 10, 1200.00),
(102, 1, 11,  300.00),
(103, 1, 12,  150.00),
(104, 3, 10, 1100.00),
(105, 3, 11,  350.00),
(106, 4, 11,  400.00),
(107, 1, 11,  250.00),
(108, 1, 12,  NULL),
(109, 3, 11,  349.00),
(110, 3, 11,   50.00),
(111, 4, NULL,  50.00),
(112, 4, 11,   10.00);

users

products

orders

Things to notice in this data, because they matter later:

• Users 2, 5, 6, 7, and 8 have no orders. User 7 has an empty-string name, and user 8 has a NULL name.
• Order 108 has a NULL amount, and order 111 has a NULL product_id.
• Only users 1, 3, and 4 have placed orders.

Syntax

SELECT columns
FROM table
WHERE column operator (
    SELECT column
    FROM other_table
    WHERE condition
);

Where a subquery can be written

 SELECT   ... (subquery) ...    scalar subquery: must give ONE value per row
 FROM     (subquery) AS alias   derived table: a temporary table, alias required
 WHERE    col = (subquery)      scalar subquery: one value
          col IN (subquery)     list of values
          EXISTS (subquery)     TRUE or FALSE
 HAVING   COUNT(*) = (subquery) compare a group result with one value

Types of subqueries by what they return

Two styles by how they are connected to the outer query

Mental Model

Think of nested boxes, or Russian dolls. The big box is the outer query. Inside it sits a smaller box, the inner query, which answers a smaller question.

 +-----------------------------------------------------+
 | OUTER QUERY                                         |
 |   SELECT order_id, amount                           |
 |   FROM orders                                       |
 |   WHERE amount > (                                  |
 |        +-------------------------------------+      |
 |        | INNER QUERY (subquery)              |      |
 |        |   SELECT AVG(amount) FROM orders    |      |
 |        +-------------------------------------+      |
 |   );                                                |
 +-----------------------------------------------------+

How SQL Executes It

Non-correlated subquery: inner first, then outer

 Step 1. The inner query runs ONCE
         SELECT AVG(amount) FROM orders   ---->  382.64
                                                    |
 Step 2. The outer query uses that value            v
         SELECT ... WHERE amount > 382.64

Correlated subquery: the inner query depends on each outer row

 Outer row: order 101 (user_id = 1)
         |
         |  passes user_id = 1 into the subquery
         v
 Subquery: SELECT name FROM users WHERE user_id = 1   --->  'Ayaan'
         |
         v
 Output row: (101, 'Ayaan')

 The same steps then repeat for order 102, order 103, and so on.

This "one inner run per outer row" is the logical picture. Real databases may rewrite the query into a faster plan, but the result always matches this logic.

1. Scalar Subquery in SELECT

Problem Fetch each order along with the user's name.

SQL Query

SELECT
  o.order_id,
  (SELECT u.name FROM users u WHERE u.user_id = o.user_id) AS name
FROM orders o;

Explanation

1. The outer query reads each row from orders (alias o) and outputs o.order_id. So it produces one output row per order.
2. For every order row, the subquery runs:

   SELECT u.name FROM users u WHERE u.user_id = o.user_id

   Copy

   • It uses the current order's o.user_id.
   • It finds the matching row in users.
   • It returns that user's name.

Output

Why it works A subquery in the SELECT list must return exactly one value for each outer row. Here, user_id is the primary key of users, so there is at most one match.

• If the subquery finds no row, the result is NULL.
• If it finds more than one row, MySQL raises an error: "Subquery returns more than 1 row".

The same result with a JOIN

SELECT o.order_id, u.name
FROM orders o
LEFT JOIN users u ON u.user_id = o.user_id;

2. Scalar Subquery in WHERE (extra example)

Problem Show the orders whose amount is higher than the average order amount.

SQL Query

SELECT order_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

Explanation

• The inner query runs once and returns one number: the average of all non-NULL amounts.
  • The 11 non-NULL amounts add up to 4209.00, so the average is about 382.64.
  • AVG ignores NULL, so order 108 is not counted.
• The outer query keeps orders with amount > 382.64.

Output

Why it works This is a non-correlated subquery. It does not depend on the outer row, so the database can calculate the average once and reuse it. Order 108 (NULL amount) never passes amount > 382.64, because a comparison with NULL is UNKNOWN, not TRUE.

3. Subquery with IN

The IN operator checks whether a value exists in a list. When that list is produced by a subquery, SQL builds the list on the fly and uses it as a filter.

 Inner query builds a list          Outer query checks each row against the list

 SELECT DISTINCT user_id            user_id = 1  -> in list?  yes -> keep
 FROM orders                        user_id = 2  -> in list?  no  -> skip
        |                           user_id = 3  -> in list?  yes -> keep
        v                           ...
   list: (1, 3, 4)

Scenario 1: Users who have any order

SQL Query

SELECT u.user_id
FROM users u
WHERE u.user_id IN (
  SELECT DISTINCT o.user_id
  FROM orders o
);

Explanation The inner query collects all unique user_id values that appear in orders: 1, 3, 4. The outer query returns only users whose user_id is in that list.

Output

Why it works DISTINCT is optional here. IN only cares whether a value is present, so repeated values in the list make no difference.

Scenario 2: Users who purchased a Tablet

SQL Query

SELECT o.user_id, o.product_id
FROM orders o
WHERE o.product_id IN (
  SELECT p.product_id
  FROM products p
  WHERE p.product = 'Tablet'
);

Explanation The inner query finds the product_id of the Tablet (10). The outer query returns orders whose product_id is in that list.

Output

Why it works The subquery turns a product name into a product ID, so the outer query does not need to know the ID in advance. If the Tablet's ID ever changes, the query still works.

4. EXISTS

EXISTS checks whether the subquery returns at least one row. It gives TRUE or FALSE, not rows. The database can stop searching the moment it finds the first match. Since the actual values do not matter, SELECT 1 is the usual style.

 User 1 (Ayaan): look in orders for user_id = 1 ... found order 101 -> STOP -> TRUE
 User 2 (Sneha): look in orders for user_id = 2 ... nothing found   -> FALSE

Scenario 1: Users who have placed at least one order

SQL Query

SELECT
  u.user_id,
  u.name
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.user_id
);

Explanation

• EXISTS returns TRUE or FALSE, not rows.
• It stops as soon as it finds the first matching order (efficient).
• The subquery is correlated: it uses u.user_id from the outer query.

Output

Scenario 2: Users with at least one high-value order (amount >= 500)

SQL Query

SELECT u.user_id
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.user_id
    AND o.amount >= 500
);

Explanation Same idea as before, with one extra condition inside the subquery: o.amount >= 500. A user is returned if at least one of their orders passes it. Rows with amount = NULL never satisfy >= 500.

Output

Why it works User 1 has an order of 1200, and user 3 has an order of 1100. User 4's largest order is 400, so user 4 is not included.

5. NOT EXISTS

NOT EXISTS makes sure that no matching row exists. This is the most reliable way to write "never happened" or "nothing is missing" logic.

Scenario 1: Users who have never placed an order

SQL Query

SELECT
  u.user_id,
  u.name
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.user_id
);

Explanation For each user, the query looks in the orders table. If it finds no order for that user, the user is shown.

Output

Why it works Users 7 and 8 have an empty name and a NULL name, but they are still returned, because the check is about orders, not names.

Scenario 2: Users who bought all products

This one uses two NOT EXISTS, one inside the other. Read it as a double negative:

Find users for whom there is no product that they have not bought.

 For each USER u:
     For each PRODUCT p:
         Did u buy p?   (is there an order row for u and p?)
     If there is ANY product that u did NOT buy  -> reject u
     Otherwise                                   -> keep u

SQL Query

SELECT
  u.user_id,
  u.name
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM products p
  WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.user_id
      AND o.product_id = p.product_id
  )
);

Explanation

• The innermost subquery asks: did this user buy this product?
• The middle query loops over every product and keeps the ones the user did not buy.
• The outer NOT EXISTS keeps the user only if that "not bought" list is empty.

The grid behind the answer

Output

Why it works Only Ayaan has an order for every product. A single missing product is enough to remove a user.

6. Correlated Subquery

A correlated subquery uses columns from the outer query. Logically, the inner query runs once for each row the outer query processes.

Scenario 1: The highest order amount placed by each user

SQL Query

SELECT
  u.user_id,
  u.name,
  (
    SELECT MAX(o.amount)
    FROM orders o
    WHERE o.user_id = u.user_id
  ) AS max_amount
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.user_id
)
ORDER BY max_amount DESC;

Explanation

• For each user, the subquery in the SELECT list finds MAX(o.amount) from that user's orders.
• WHERE EXISTS removes users with no orders. Without it, users 2, 5, 6, 7, and 8 would appear with a NULL max_amount.
• MAX() ignores NULL, so user 1's NULL amount does not affect the maximum.
• The results are sorted by max_amount, highest first.

Output

Scenario 2: Which order produced the maximum amount

SQL Query

SELECT
  o.user_id,
  o.product_id,
  o.amount
FROM orders o
WHERE o.amount = (
  SELECT MAX(o2.amount)
  FROM orders o2
  WHERE o2.user_id = o.user_id
);

Explanation

• The inner query finds the largest order amount for the current user (same user_id).
• The outer query keeps only the order rows whose amount is exactly equal to that user's largest amount.
• The result shows the actual order entries behind each user's highest purchase.
• If several orders share the same highest amount for a user, all of those tied orders appear.

Output

Why it works Order 108 (user 1, NULL amount) is never returned, because NULL = 1200.00 is UNKNOWN. Each user here has one unique top order, so each user contributes one row.

7. Subquery in FROM: Derived Table (extra example)

A subquery in the FROM clause acts like a temporary table that exists only for this query. MySQL requires every derived table to have an alias.

Problem Find the average of each user's total spending.

SQL Query

SELECT ROUND(AVG(t.total_spent), 2) AS avg_total_spent
FROM (
  SELECT user_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY user_id
) AS t;

Explanation

1. The inner query builds a temporary table with one row per user:

   user_id	total_spent
   1	1900.00
   3	1849.00
   4	460.00

2. The outer query averages the total_spent column of that temporary table: (1900 + 1849 + 460) / 3 = 1403.

Output

Why it works You cannot write AVG(SUM(amount)) directly, because an aggregate cannot be placed inside another aggregate in one step. The derived table does the first aggregation (SUM per user), and the outer query does the second (AVG of those sums).

8. IN vs EXISTS vs NOT IN vs NOT EXISTS

The NULL trap with NOT IN (a live example)

Problem Find the products that user 4 has never bought. The right answer is Tablet (10) and Keyboard (12), because user 4 only bought Headphones (and one order with a NULL product).

Wrong: NOT IN

SELECT p.product_id, p.product
FROM products p
WHERE p.product_id NOT IN (
  SELECT o.product_id
  FROM orders o
  WHERE o.user_id = 4
);

Output

Why it fails User 4's orders have product IDs 11, NULL, 11. So the list is (11, NULL, 11). For product 10, SQL checks:

 10 NOT IN (11, NULL, 11)
 =  10 <> 11   AND   10 <> NULL   AND   10 <> 11
 =  TRUE       AND   UNKNOWN      AND   TRUE
 =  UNKNOWN   ->  the row is NOT returned

Any comparison with NULL is UNKNOWN, and one UNKNOWN makes the whole NOT IN UNKNOWN. So every product is dropped, and you get zero rows, with no error.

Correct option 1: NOT EXISTS

SELECT p.product_id, p.product
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = 4
    AND o.product_id = p.product_id
);

Output

Correct option 2: keep NOT IN but remove NULLs from the list

WHERE p.product_id NOT IN (
  SELECT o.product_id
  FROM orders o
  WHERE o.user_id = 4 AND o.product_id IS NOT NULL
)

Why NOT EXISTS is safer NOT EXISTS only asks "is there a matching row?" and never compares a value with NULL directly, so a NULL in the data cannot break it.

Deep Dive

Internal behavior For a non-correlated subquery, the inner result does not change, so it only needs to be worked out once. For a correlated subquery, the result depends on the outer row, so logically it is worked out for each outer row. Modern MySQL often rewrites IN and EXISTS subqueries into join-like plans (called semi-joins), which means the speed gap between them is often small. The bigger reasons to choose between them are clarity and NULL safety.

NULL handling

Performance considerations

• A correlated subquery in the SELECT list can be slow on big tables, because it may run for every outer row. A JOIN with GROUP BY is often faster.
• Put an index on the column used to connect the inner and outer query (for example, orders.user_id).
• EXISTS can stop at the first match. That is why it suits "does any related row exist?" questions.

Subquery or JOIN?

Database behavior Subquery syntax is standard SQL. Small differences exist: MySQL requires an alias on every derived table, and the exact error messages differ between databases.

Real-world Use Cases

• Win-back campaigns: find users who have never ordered (NOT EXISTS).
• Above-average reports: orders, employees, or products above the average (scalar subquery in WHERE).
• Top record per group: the highest order per user, or the highest salary per department (correlated subquery).
• Loyalty and completion checks: customers who bought every product, students who finished every course (double NOT EXISTS).
• Name-to-ID lookups: filter by a product name without hard-coding its ID (IN with a subquery).
• Two-step aggregation: an average of totals, or a count of groups (derived table in FROM).

Review Tips

Interviewers commonly ask:

• "What is a subquery, and what is the difference between correlated and non-correlated?" — A correlated subquery uses outer-query columns and logically runs per outer row. A non-correlated one runs once on its own.
• "IN vs EXISTS: when do you use which?" — IN compares against a list of values. EXISTS only checks if a matching row exists, and it can stop at the first match.
• "Why is NOT IN dangerous?" — A NULL in the list makes the result UNKNOWN for every row, so it can return nothing. Use NOT EXISTS, or filter NULLs out.
• "What happens if a scalar subquery returns more than one row?" — MySQL raises an error.
• "How would you find users who bought all products?" — Double NOT EXISTS, or GROUP BY with HAVING COUNT(DISTINCT product) = (SELECT COUNT(*) FROM products).
• "What does SELECT 1 inside EXISTS mean?" — The selected value is ignored. Only the existence of a row matters.
• "Can you write a subquery in FROM? What must you add?" — Yes. It is a derived table and needs an alias.
• "Can you rewrite a subquery as a JOIN?" — Often yes. Discuss readability and performance.

Common Mistakes to Avoid

Mistake 1: Using NOT IN when the list can contain NULL

Why it happens: NOT IN reads naturally.

Wrong:

WHERE p.product_id NOT IN (SELECT o.product_id FROM orders o WHERE o.user_id = 4)

The NULL product ID in user 4's orders makes the result empty.

Correct: Use NOT EXISTS, or add AND o.product_id IS NOT NULL inside the subquery.

Mistake 2: A scalar subquery that returns more than one row

Why it happens: The subquery is written without thinking about how many rows it finds.

Wrong:

SELECT order_id, (SELECT name FROM users) AS name FROM orders;

MySQL raises "Subquery returns more than 1 row".

Correct: Add a condition that limits it to one row, such as WHERE u.user_id = o.user_id, or use an aggregate like MAX().

Mistake 3: Returning two columns from an IN subquery

Why it happens: Adding an extra column "for reference".

Wrong:

WHERE user_id IN (SELECT user_id, amount FROM orders)

MySQL raises "Operand should contain 1 column(s)".

Correct: The subquery used with IN must return exactly one column.

Mistake 4: Forgetting the alias on a derived table

Why it happens: Aliases feel optional.

Wrong:

SELECT AVG(total_spent) FROM (SELECT user_id, SUM(amount) AS total_spent FROM orders GROUP BY user_id);

MySQL raises "Every derived table must have its own alias".

Correct: Add AS t after the closing parenthesis.

Mistake 5: Forgetting the correlation inside EXISTS

Why it happens: The inner query is written as a standalone query.

Wrong:

SELECT u.user_id FROM users u
WHERE EXISTS (SELECT 1 FROM orders o);

The subquery does not mention u.user_id, so it is TRUE for every user as soon as any order exists anywhere.

Correct: Connect the two queries: WHERE o.user_id = u.user_id.

Mistake 6: Expecting a tie to return only one row

Why it happens: "The highest order" sounds like one row.

Why it matters: In the "order with the maximum amount" query, if two orders of the same user share the top amount, both are returned.

Correct: If you need only one row per user, add a tie-breaker rule, such as the lowest order_id.

Mistake 7: Expecting NULL rows to match a comparison

Why it happens: Order 108 has a NULL amount and feels like it should be "the smallest".

Why it matters: NULL = anything and NULL > anything are UNKNOWN, so such rows never appear in a comparison result.

Correct: Decide how to treat NULL explicitly, for example with COALESCE(amount, 0).

Mistake 8: Using a correlated subquery in SELECT on a huge table without an index

Why it happens: It works fine on small test data.

Why it hurts: The inner query may run once per outer row, scanning the inner table each time.

Correct: Index the joining column, or rewrite with JOIN and GROUP BY.

Key Takeaways

• A subquery is a SELECT inside another SQL statement. The outer query is the main one, and the inner query is the subquery.
• A non-correlated subquery runs on its own (once). A correlated subquery uses outer-query columns and logically runs once per outer row.
• A subquery can sit in SELECT (one value per row), FROM (a derived table with an alias), WHERE (a value, a list, or an existence check), and HAVING (a value to compare a group against).
• A scalar subquery must return exactly one column and at most one row. No row gives NULL, and more than one row gives an error.
• IN compares a value against a list built by the subquery. The subquery must return exactly one column.
• EXISTS checks whether any row matches, stops at the first match, and ignores what is selected (SELECT 1).
• NOT EXISTS is the safe way to write "never happened", even when NULLs are present.
• NOT IN returns no rows if the list contains a NULL. Avoid it unless the column is guaranteed NOT NULL.
• "Bought all products" logic can be written as a double NOT EXISTS.
• Aggregates (MAX, AVG, SUM) ignore NULL, but comparisons with NULL never match.
• Sometimes a JOIN is faster. Choose by clarity first, then check performance.