Subqueries

Subqueries

What it means

A subquery is a query nested inside another query, used wherever a single value, a list of values, or a table-like result is needed. Common forms: a scalar subquery in WHERE or SELECT (returns one value), IN/NOT IN with a list of values, and EXISTS/NOT EXISTS for existence checks.

Examples

-- Scalar subquery: compare against a single computed value
SELECT Name, Salary FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

-- IN: match against a list produced by another query
SELECT Name FROM Customers
WHERE Id IN (SELECT CustomerId FROM Orders WHERE Amount > 1000);

-- EXISTS: often faster than IN for large sets, checks existence only
SELECT Name FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id);

-- Subquery in the SELECT list
SELECT Name, (SELECT COUNT(*) FROM Orders o WHERE o.CustomerId = c.Id) AS OrderCount
FROM Customers c;

-- Subquery as a derived table (in the FROM clause)
SELECT dept, avg_salary FROM (
    SELECT DepartmentId AS dept, AVG(Salary) AS avg_salary
    FROM Employees GROUP BY DepartmentId
) AS DeptAverages
WHERE avg_salary > 60000;

Common mistake

Using NOT IN with a subquery that can return NULL values — if even one value in the subquery’s result is NULL, the entire NOT IN comparison silently returns zero rows, which is a notorious SQL gotcha.

-- If ANY CustomerId in Orders is NULL, this returns NO rows at all, even for
-- customers who genuinely have no orders -- silently wrong, no error raised
SELECT Name FROM Customers
WHERE Id NOT IN (SELECT CustomerId FROM Orders);

-- Fix: filter out NULLs explicitly, or use NOT EXISTS instead
SELECT Name FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id);

Quick practice

1. What’s the danger of using NOT IN with a subquery that might return NULL values?

   Answer

   If any row in the subquery's result is NULL, the entire NOT IN condition silently evaluates to no matches at all — no error, just an unexpectedly empty result set.

2. What’s the difference between IN and EXISTS?

   Answer

   IN compares a value against a list of values from the subquery; EXISTS just checks whether the subquery returns any rows at all, and is often more efficient (and safer regarding NULLs) for large result sets.

3. Can a subquery be used in the FROM clause of an outer query?

   Answer

   Yes — this is called a derived table, and it must be given an alias, e.g. FROM (SELECT ...) AS AliasName.