Question 1

Who beats the average?

Accepted Answer

The subquery AVG(salary) = (90000+75000+82000)/3 = 82333.33. The outer query keeps employees whose salary exceeds that average. Only Alice (90000) qualifies. Carol (82000) is below the average, Bob (75000) is well below. A subquery in WHERE that returns a single value is called a scalar subquery.

Question 2

Filter by a department's budget

Accepted Answer

The subquery returns department ids where budget > 100000 - only Eng (id=20). The outer query then keeps employees whose dept_id is in that set - only Bob (dept_id=20). Subquery with IN is equivalent to a JOIN but is often more readable when you only need data from one table in the final result.

Question 3

Who isn't a manager?

Accepted Answer

The subquery returns ('Alice', 'Dave'). NOT IN excludes employees whose name appears in that list. Alice is excluded. Bob and Carol are not in the managers list and are returned. Dave is in managers but not in employees, so he is irrelevant to the outer query.

Question 4

Why does this FROM subquery fail?

Accepted Answer

In standard SQL (and all major databases), every derived table - a subquery used in the FROM clause - must be given a name via AS alias. The fix: add an alias, e.g. ) AS totals at the end. Without it, the database cannot reference the result set and raises a syntax error. PostgreSQL, MySQL, and SQL Server all enforce this. The alias is then used to qualify columns if needed: totals.customer.

Question 5

What is a VIEW?

Accepted Answer

A view is just a named SELECT statement - no data is stored unless you use a materialized view. Every time you query a regular view, the database runs the underlying SELECT fresh. Views are useful for hiding complexity, restricting column access, or giving a stable interface to a table whose schema may change.

Question 6

The vanishing column trick

Accepted Answer

A scalar subquery in the SELECT list executes once per row and must return exactly one value. MAX(salary) across all employees is 90000 (Alice herself). The result row is Alice | 90000 | 90000 - her salary and the overall max happen to be the same. This pattern is useful for adding a reference value (max, average) to every row without a separate JOIN.

Question 7

Top earner per department

Accepted Answer

A correlated subquery references a column from the outer query (e.dept) and re-executes for each outer row. For Alice (Eng), the subquery returns MAX(salary) where dept='Eng' = 90000. Alice's salary matches, so she's included. For Bob (Eng), the subquery still returns 90000 - Bob's 80000 doesn't match. For Carol (HR), the subquery returns MAX(salary) where dept='HR' = 70000. Carol matches and is included. Result: Alice and Carol - the top earner in each department.

Question 8

Customers who ever placed an order

Accepted Answer

EXISTS returns TRUE if the subquery produces at least one row, FALSE if it produces none. For Alice (id=1), the subquery finds two orders - EXISTS is TRUE. For Bob and Carol (ids 2 and 3), the subquery finds no orders - EXISTS is FALSE. The SELECT 1 inside EXISTS is conventional - the actual value doesn't matter, only whether a row exists.

Question 9

Groups above a dynamic threshold

Accepted Answer

The scalar subquery in HAVING computes AVG(amount) * 2 = ((200+400+100+150)/4) * 2 = 212.5 * 2 = 425. The outer query groups by customer: Alice total=600, Bob total=250. HAVING keeps groups where total > 425 - only Alice (600) qualifies. Scalar subqueries in HAVING run once and return a single threshold value, exactly like in WHERE.

Question 10

Too many rows in the SELECT list

Accepted Answer

A scalar subquery is expected to produce a single value. If it returns more than one row, most databases raise 'subquery returned more than one row' at runtime. If it returns zero rows, it produces NULL (not an error). This is a common bug: SELECT name, (SELECT salary FROM employees WHERE dept = e.dept) fails when a dept has multiple employees. The fix: add an aggregate like MAX/MIN, add a LIMIT 1, or restructure as a JOIN.

Question 11

Short-circuit vs set membership

Accepted Answer

EXISTS short-circuits: the moment one matching row is found, it returns TRUE and moves on. It never cares about the actual values in the SELECT list, only that a row exists. IN materializes the subquery result into a set of values first, then checks each outer row against that set. For large subquery results this can be memory-intensive. EXISTS is generally preferred for correlated checks on large datasets; IN is clean and readable for small, uncorrelated value lists. Both can use indexes effectively when written well.

Question 12

WHERE can't see what GROUP BY computes

Accepted Answer

SQL processes clauses in this order: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. WHERE filters individual rows before any grouping happens, so it cannot see per-group aggregates like SUM(amount). HAVING filters after grouping, which is exactly where you need to compare a group's total against a threshold. A subquery in HAVING is the clean solution: HAVING SUM(amount) > (SELECT AVG(total) FROM ...). The subquery runs once and returns the threshold.

Question 13

Filter on an aggregate you just computed

Accepted Answer

A subquery in FROM is called a derived table or inline view. It runs first, producing a temporary result set (aliased as 'totals') that the outer query treats like a real table. The inner query sums orders per customer: Alice=400, Bob=50. The outer WHERE total > 100 keeps only Alice. This pattern is an alternative to CTEs (WITH clauses) and is useful when you need to filter on aggregate results.

Question 14

NOT IN subquery with NULLs

Accepted Answer

NOT IN expands to: id != 1 AND id != 2 AND id != NULL. The last condition is UNKNOWN for every row because nothing can be compared to NULL with !=. AND UNKNOWN evaluates to UNKNOWN, and WHERE only keeps TRUE rows - so zero rows are returned. The fix: add WHERE emp_id IS NOT NULL to the subquery, or use NOT EXISTS instead, which handles NULLs safely.

Question 15

Correlated subquery performance

Accepted Answer

Correlated subqueries execute once per outer row by definition. On 1M rows with no index on the join column, that is 1M full scans - potentially catastrophic. Some optimizers (PostgreSQL, SQL Server) can unnest simple correlated subqueries into JOINs, but this is not guaranteed for complex patterns. The fix: rewrite as a JOIN or CTE with GROUP BY when possible, and ensure the correlated column is indexed when you must keep the correlated form.

Question 16

When NOT IN silently drops all rows

Accepted Answer

NOT IN breaks when the subquery returns any NULL because id != NULL is UNKNOWN, and AND UNKNOWN collapses the entire WHERE to UNKNOWN. The row is silently dropped. NOT EXISTS is a correlated subquery that checks whether a matching row exists - it does not compare values directly, so NULLs in the subquery do not affect it. As a rule: if you cannot guarantee the subquery column is NOT NULL, use NOT EXISTS. The two are logically equivalent when NULLs are absent.

Subqueries