SQL subquery interview questions, with answers

The questions, with answers

1. 1.What is a subquery, and where can one appear in a statement?

   A subquery is a SELECT nested inside another statement. It can sit in WHERE or HAVING as the right-hand side of IN, EXISTS or a comparison; in FROM as a derived table that the outer query treats like a real table; and in the SELECT list as a scalar subquery that must return exactly one value per outer row. It can also feed INSERT, UPDATE and DELETE. Each position has its own rule about how many rows and columns the subquery may return, and most subquery bugs are a mismatch there.

   -- WHERE: membership
   SELECT name FROM Customers
   WHERE id IN (SELECT customer_id FROM Orders);
   
   -- FROM: derived table (the alias t is required even if unused)
   SELECT customer_id, total
   FROM (SELECT customer_id, SUM(amount) AS total FROM Orders GROUP BY customer_id) t
   WHERE total > 5000;

2. 2.What is the difference between a correlated and a non-correlated subquery?

   A non-correlated subquery is self-contained: it can run on its own, produces one result, and the database evaluates it once. A correlated subquery references a column from the outer query, so logically it is re-evaluated for each outer row — "the average salary of this employee's department" depends on which employee you are looking at.

   The interview follow-up is about cost. Naively that is one inner query per outer row, but modern optimisers usually rewrite a correlated subquery into a join or a semi-join; the logical model is per-row, the physical plan often is not. Say both halves.

   SELECT e1.name
   FROM Employees e1
   WHERE e1.salary > (SELECT AVG(e2.salary)
                      FROM Employees e2
                      WHERE e2.dept_id = e1.dept_id);   -- correlated on e1.dept_id

3. 3.When should you use EXISTS instead of IN?

   Use EXISTS when you are asking "is there at least one matching row" and the subquery is correlated; it stops at the first match and it is unaffected by NULLs. IN reads naturally when the subquery returns a small list of values. The real reason to prefer EXISTS is its negation: NOT EXISTS behaves correctly when the subquery can return NULL, while NOT IN returns no rows at all if a single NULL appears in the list. For positive membership the optimiser usually treats IN and EXISTS the same, so the choice there is readability.

   SELECT d.dept_name
   FROM Departments d
   WHERE EXISTS (SELECT 1 FROM Employees e WHERE e.dept_id = d.dept_id);
   -- departments that have at least one employee; SELECT 1 because only existence matters

4. 4.What is a scalar subquery, and what happens if it returns more than one row?

   A scalar subquery is one used where a single value is expected — in a comparison, in the SELECT list, in an assignment. It must return one column and at most one row. If it returns more than one row, the query fails at runtime: MySQL says "Subquery returns more than 1 row", PostgreSQL "more than one row returned by a subquery used as an expression". If it returns no rows, the value is NULL, which then makes a comparison unknown and silently drops the row. So a scalar subquery should be anchored on something unique — a primary key, an aggregate — or guarded with LIMIT 1 when a stray duplicate is acceptable.

5. 5.What is a derived table, and why must it have an alias?

   A derived table is a subquery in the FROM clause; the outer query reads its result set as if it were a table, which lets you aggregate first and filter or join afterwards. MySQL and PostgreSQL insist on an alias for it — MySQL's message is literally "Every derived table must have its own alias" — because the outer query needs a name to refer to its columns, and because two derived tables in one FROM would otherwise be indistinguishable. A common table expression (WITH name AS (...)) is the same idea with the name declared up front and reusable more than once, which is why most people reach for a CTE when the derived table gets long.

6. 6.Is a subquery slower than a JOIN?

   Not by itself. The optimiser rewrites most IN, EXISTS and derived-table subqueries into joins or semi-joins and picks the plan on cost, so an equivalent JOIN and subquery frequently produce the same plan. The cases that do get slow are correlated subqueries the optimiser cannot flatten, and scalar subqueries in the SELECT list that really do run once per row. The honest decision rule: use a JOIN when you need columns from both tables in the output, a subquery when you are only testing membership or comparing against an aggregate — and read the EXPLAIN plan when it matters rather than guessing.

7. 7.How do you find the second-highest salary without window functions?

   Ask for the largest salary that is smaller than the largest salary. The inner query finds the maximum; the outer query finds the maximum among salaries strictly below it. Because it compares distinct salary values, two people sharing the top salary still give the right answer, and if every salary is the same the outer MAX is NULL rather than a wrong number. The LIMIT/OFFSET version — SELECT DISTINCT salary ORDER BY salary DESC LIMIT 1 OFFSET 1 — works too, but interviewers often ban it to see whether you can express it as a subquery.

   SELECT MAX(salary) AS second_highest
   FROM Employees
   WHERE salary < (SELECT MAX(salary) FROM Employees);

8. 8.What do ALL and ANY mean with a subquery, and what are the empty-set edge cases?

   x > ALL (subquery) is true when x is greater than every value the subquery returns; x > ANY (or SOME) is true when x is greater than at least one of them. So "salary > ALL (salaries in Sales)" means higher than the top Sales salary; "> ANY" means higher than the lowest. The edge cases interviewers like: against an empty subquery, ALL is true for every x and ANY is false for every x. And a NULL in the subquery result can make ALL unknown — no rows returned — which is why the MAX/MIN rewrite (salary > (SELECT MAX(salary) ...)) is usually clearer and safer.

How the diagnostic asks it

One question from the SQL bank, exactly as a sitting would show it. The bank has 6 on subqueries and 60 across SQL.

SELECT name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees);