Subqueries: Single-Row, Multi-Row, and Correlated

• Subquery – a query nested inside another query, appearing in WHERE, FROM, SELECT, or HAVING.
• Single-row (scalar) – returns exactly one value (one row, one column); compared with =, <, >; errors if it returns more than one row.
• Multi-row – returns a set of values; used with IN, ANY (SOME), or ALL.
• Correlated – references a column from the outer query; logically re-evaluates for each outer row.
• NOT IN trap – if the subquery returns any NULL, NOT IN silently returns no rows; prefer NOT EXISTS.

What is a subquery?

A subquery is a SELECT statement nested inside another SQL statement. It can appear in several places: in the WHERE clause as a filter, in the FROM clause as a derived table, in the SELECT list as a scalar value, and in a HAVING clause. The outer query uses the subquery's result as input.

-- Sample data at National Bank of India
CREATE TABLE accounts (
    account_id VARCHAR(10) PRIMARY KEY,
    holder_name VARCHAR(100),
    balance     DECIMAL(15,2)
);

CREATE TABLE blocked_accounts (
    account_id VARCHAR(10) PRIMARY KEY
);

INSERT INTO accounts VALUES
('SAV2001', 'Rajesh Kumar', 150000.00),
('SAV2002', 'Priya Sharma',  85000.00),
('SAV2003', 'Pankaj Gupta', 320000.00),
('SAV2004', 'Amit Verma',    45000.00);

INSERT INTO blocked_accounts VALUES ('SAV2004');

What is a single-row (scalar) subquery?

A scalar subquery returns exactly one value – one row and one column. You compare it with a scalar operator like =, <, or >. If it returns more than one row, the database raises an error. Scalar subqueries typically wrap an aggregate like AVG or MAX to guarantee one row.

-- Accounts with balance above the bank-wide average
SELECT account_id, holder_name, balance
FROM   accounts
WHERE  balance > (SELECT AVG(balance) FROM accounts);

 account_id | holder_name  | balance
------------+--------------+-----------
 SAV2001    | Rajesh Kumar | 150000.00
 SAV2003    | Pankaj Gupta | 320000.00

The inner query computes one number (the average: 150000.00), and the outer query filters against it.

-- Scalar subquery in SELECT list
SELECT account_id,
       balance,
       (SELECT AVG(balance) FROM accounts) AS bank_avg
FROM   accounts;

 account_id | balance    | bank_avg
------------+------------+-----------
 SAV2001    | 150000.00  | 150000.00
 SAV2002    |  85000.00  | 150000.00
 SAV2003    | 320000.00  | 150000.00
 SAV2004    |  45000.00  | 150000.00

What happens if a scalar subquery returns more than one row?

The database raises an error. This is why scalar subqueries almost always wrap an aggregate function.

-- ERROR: subquery returns more than one row
SELECT * FROM accounts
WHERE balance = (SELECT balance FROM accounts WHERE balance > 100000);
-- ERROR: more than one row returned by a subquery used as an expression

What is a multi-row subquery?

A multi-row subquery returns a set of values and must be used with a set operator: IN, ANY (SOME), or ALL.

-- IN: accounts that are in the blocked list
SELECT * FROM accounts
WHERE account_id IN (SELECT account_id FROM blocked_accounts);

 account_id | holder_name | balance
------------+-------------+----------
 SAV2004    | Amit Verma  | 45000.00

-- ANY: balance greater than at least one blocked account's balance
-- (equivalent to: greater than the minimum)
SELECT * FROM accounts
WHERE balance > ANY (SELECT balance FROM accounts WHERE account_id IN
    (SELECT account_id FROM blocked_accounts));

-- ALL: balance greater than every blocked account's balance
-- (equivalent to: greater than the maximum)
SELECT * FROM accounts
WHERE balance > ALL (SELECT balance FROM accounts WHERE account_id IN
    (SELECT account_id FROM blocked_accounts));

What are the equivalences between IN, ANY, and ALL?

Two useful identities that interviewers ask about:

| Expression | Equivalent To | Meaning |
|---|---|---|
| = ANY (subquery) | IN (subquery) | Matches at least one value |
| <> ALL (subquery) | NOT IN (subquery) | Matches none of the values |
| > ANY (subquery) | > MIN(subquery) | Greater than the smallest |
| > ALL (subquery) | > MAX(subquery) | Greater than the largest |

-- These two are identical
SELECT * FROM accounts WHERE account_id = ANY
    (SELECT account_id FROM blocked_accounts);

SELECT * FROM accounts WHERE account_id IN
    (SELECT account_id FROM blocked_accounts);

What is a correlated subquery?

A correlated subquery references a column from the outer query, so it cannot run independently. It is logically re-evaluated for each outer row. The classic patterns are EXISTS and NOT EXISTS.

-- EXISTS: accounts that have at least one transaction
SELECT a.account_id, a.holder_name
FROM   accounts a
WHERE  EXISTS (
    SELECT 1
    FROM   transactions t
    WHERE  t.account_id = a.account_id
);

-- NOT EXISTS: accounts with no transactions
SELECT a.account_id, a.holder_name
FROM   accounts a
WHERE  NOT EXISTS (
    SELECT 1
    FROM   transactions t
    WHERE  t.account_id = a.account_id
);

What is the NOT IN trap with NULLs?

If the subquery returns any NULL, NOT IN silently returns no rows. This is because x <> NULL is unknown, and anything AND unknown is never true.

-- Dangerous: if blocked_accounts has a NULL account_id
INSERT INTO blocked_accounts VALUES (NULL);

-- NOT IN returns ZERO rows (silently wrong)
SELECT * FROM accounts
WHERE account_id NOT IN (SELECT account_id FROM blocked_accounts);
-- Result: 0 rows

-- SAFE: NOT EXISTS handles NULLs correctly
SELECT a.*
FROM   accounts a
WHERE  NOT EXISTS (
    SELECT 1 FROM blocked_accounts b
    WHERE  b.account_id = a.account_id
);
-- Returns non-blocked accounts correctly

How do you use subqueries in FROM (derived tables)?

A subquery in the FROM clause is called a derived table or inline view. It produces a result set that the outer query treats as a table.

-- Derived table: find accounts with above-average balance
SELECT s.account_id, s.balance, s.bank_avg
FROM (
    SELECT account_id, balance,
           (SELECT AVG(balance) FROM accounts) AS bank_avg
    FROM   accounts
) s
WHERE s.balance > s.bank_avg;

What about performance of correlated subqueries?

Correlated subqueries can be slow on large tables because the naive execution model runs the inner query per outer row. An index on the correlated column is essential. Modern optimizers often rewrite correlated subqueries into joins (decorrelation).

-- Slow without an index on transactions.account_id
SELECT a.account_id
FROM   accounts a
WHERE  EXISTS (
    SELECT 1 FROM transactions t
    WHERE  t.account_id = a.account_id
);

-- Fix: create an index
CREATE INDEX idx_txn_account ON transactions (account_id);

What should a senior engineer know?

• EXISTS short-circuits on the first match, which can be faster than a JOIN plus DISTINCT for semi-join semantics.
• The optimizer often rewrites IN (subquery) into a semi-join and NOT IN (subquery) into an anti-join, so performance may be equivalent to EXISTS/NOT EXISTS.
• Lateral subqueries (PostgreSQL LATERAL, SQL Server CROSS APPLY) reference the outer query in a FROM-clause subquery, combining the power of correlated subqueries with derived tables.
• For per-row aggregate comparisons ("transactions above their own account's average"), a window function is often better than a correlated subquery.

What are common interview follow-up questions?

• Where can subqueries appear? (WHERE, FROM, SELECT, HAVING, and even INSERT INTO ... SELECT.)
• What is the difference between a subquery and a CTE? (A CTE is named, scoped to one statement, and reusable by name; a subquery is inline and anonymous.)
• When should you rewrite a correlated subquery as a join? (When the correlated column lacks an index or when the optimizer does not decorrelate it.)