Quiz: SQL Subqueries: Scalar, IN, EXISTS and Correlated Subqueries

Total: 7 questions

1. 

What is a subquery in SQL, and where can you use it?

A subquery (a nested query, or inner query) is a SELECT statement in parentheses inside another SQL statement. It can go in the SELECT list, in FROM, in WHERE and HAVING, and inside INSERT, UPDATE and DELETE. What is allowed in each place depends on the shape of the result:

  • scalar — one row with one column, used as a value: WHERE views > (SELECT AVG(views) FROM topics);
  • column — one column, any number of rows: IN, NOT IN, ANY, ALL;
  • row — several columns for a tuple comparison: (topic_id, id) IN (SELECT ...);
  • table — any set of rows: a derived table in FROM, EXISTS.

An independent subquery can run on its own. A correlated one refers to columns of the outer query (m2.topic_id = m.topic_id) and cannot run outside it.

2. 

What is a scalar subquery, and what happens if it returns several rows or none?

A scalar subquery returns one row with one column, and the outer query uses it like an ordinary value — in a comparison or as a column in SELECT: views - (SELECT AVG(views) FROM topics) AS diff.

  • Several rows — ERROR 1242: Subquery returns more than 1 row. The error depends on the data: the query works while only one row matches. The fixes mean different things: IN (any of the values), the aggregate MIN/MAX (one specific value) or ORDER BY ... LIMIT 1 (the first in order). LIMIT 1 without ORDER BY returns an arbitrary row.
  • No rows — no error, the value is NULL. The condition author_id = NULL is never true, and the result is silently empty: a typo in a user name looks like “this user has no threads”. For a default value use COALESCE((SELECT ...), 0).
3. 

How do EXISTS and NOT EXISTS work, and why is NOT EXISTS safer than NOT IN?

EXISTS (subquery) is true if the subquery returns at least one row; NOT EXISTS — if it returns none. Column values are not checked, so by convention the subquery is written as SELECT 1. It is usually correlated:

SELECT username FROM users u
WHERE NOT EXISTS (SELECT 1 FROM messages m WHERE m.author_id = u.id);

EXISTS is a semi-join: a row of the outer table appears once, however many matches it has. NOT EXISTS is an anti-join; LEFT JOIN ... IS NULL gives the same result.

EXISTS is always true or false, never NULL. That is why the query above finds Dave, while NOT IN (SELECT author_id FROM messages) returns Empty set as soon as author_id contains a NULL — for example, after a user is deleted with ON DELETE SET NULL.

4. 

What is a correlated subquery, and how is it executed?

A correlated subquery refers to columns of the outer query and is logically re-evaluated for every row of it. The number of messages of each user:

SELECT u.username,
       (SELECT COUNT(*) FROM messages m WHERE m.author_id = u.id) AS messages
FROM users u;

For Dave the subquery finds no rows, and COUNT(*) over an empty set is 0 — he stays in the result with zero. If one table is needed both outside and inside, give it two aliases (m and m2).

“For every row” is what the query means, not necessarily the plan: MySQL 8 rewrites IN and EXISTS into a semi-join, NOT IN and NOT EXISTS into an anti-join. A correlated scalar subquery usually does run row by row, and on large tables an index on the linking column decides everything. DEPENDENT SUBQUERY in the EXPLAIN output means row-by-row execution.

5. 

What are a derived table and WITH (CTE) in SQL?

A derived table is a subquery in FROM: the outer query selects from, filters and groups its result like an ordinary table. The typical task is an aggregate of an aggregate. AVG(COUNT(*)) gives ERROR 1111, so you count messages per author first and then take the average:

SELECT AVG(cnt) FROM (SELECT author_id, COUNT(*) AS cnt
  FROM messages WHERE author_id IS NOT NULL
  GROUP BY author_id) AS per_author;

In MySQL the alias is mandatory: without AS per_author you get ERROR 1248: Every derived table must have its own alias.

WITH is a Common Table Expression (CTE, since MySQL 8.0): the subquery moves to the front and gets a name, WITH per_author AS (SELECT ...) SELECT .... The query reads top to bottom, and the result can be used more than once, for example in FROM and in the subquery with the average. A CTE exists only within one statement; MySQL 5.7 has no WITH.

6. 

When should you use a subquery, and when a JOIN?

If you need columns from the second table, use a JOIN; if you only need to check whether related rows exist, use EXISTS or IN.

The key difference is the number of rows: a JOIN repeats a row for every match, a subquery in WHERE does not. Alice has two threads, so WHERE id IN (SELECT author_id FROM topics) returns Alice and Bob, while JOIN topics returns Alice, Bob, Alice. To remove the duplicate, the JOIN would need DISTINCT.

  • Comparison with an average or maximum — a scalar subquery is shorter.
  • Rows without a match — NOT EXISTS or LEFT JOIN ... IS NULL; NOT IN only if the subquery has no NULL.
  • An aggregate of an aggregate, reusing a result — a derived table or WITH.

The advice “subqueries are always slower than JOINs” is outdated: since MySQL 5.6 IN runs as a semi-join. Compare the variants with EXPLAIN.

7. 

What are the most common mistakes with SQL subqueries?

The most dangerous ones produce no error message:

  • NOT IN with NULL in the subquery — Empty set. Fix: NOT EXISTS or IS NOT NULL in the subquery.
  • An empty scalar subquery — NULL, rows silently disappear. Fix: COALESCE or a data check.
  • A column without an alias — if the subquery’s table has no such column, MySQL takes it from the outer query, and the subquery silently becomes correlated. Fix: tt.topic_id.

MySQL rejects the rest with an error code:

  • ERROR 1242 — a scalar subquery returned several rows: IN, an aggregate or ORDER BY ... LIMIT 1;
  • ERROR 1248 — a derived table without an alias;
  • ERROR 1093 — UPDATE/DELETE reads the modified table in a subquery: a materialized derived table or UPDATE ... JOIN;
  • ERROR 1235 — LIMIT inside IN/ANY/ALL: wrap the subquery in a derived table.
Page 1 of 1