SQL Subqueries Explained: Correlated, Scalar, and Inline Views

A subquery is a SELECT statement nested inside another SQL statement. Subqueries can appear in the SELECT list (scalar subqueries), in the FROM clause (inline views or derived tables), in the WHERE or HAVING clause (filter subqueries), or inside an EXISTS or IN predicate. Each placement has different performance and semantic implications.

Scalar Subqueries in SELECT

SELECT Name, Salary,
       (SELECT AVG(Salary) FROM Employee) as CompanyAvgSalary,
       Salary - (SELECT AVG(Salary) FROM Employee) as DiffFromAvg
FROM Employee;

A scalar subquery returns exactly one row and one column. It can appear anywhere a single value can appear in a query. In this example, the same subquery is written twice, but most optimizers will evaluate it once and cache the result. If the subquery could return more than one row, the database raises an error at runtime.

Correlated Subqueries: References to the Outer Query

-- Find employees earning above their department average
SELECT Name, DeptID, Salary
FROM Employee e1
WHERE Salary > (
  SELECT AVG(Salary)
  FROM Employee e2
  WHERE e2.DeptID = e1.DeptID  -- reference to outer row
);

A correlated subquery references a column from the outer query (e1.DeptID). This means it must be re-evaluated for every row in the outer query, making it equivalent to a nested loop. For large tables, correlated subqueries are often slow. In interviews, you should know both how to write them and how to rewrite them using window functions or JOINs for better performance.

Rewriting Correlated Subqueries with Window Functions

-- Equivalent to the correlated subquery above, but faster:
SELECT Name, DeptID, Salary
FROM (
  SELECT Name, DeptID, Salary,
         AVG(Salary) OVER (PARTITION BY DeptID) as DeptAvg
  FROM Employee
) enriched
WHERE Salary > DeptAvg;

The window function version computes DeptAvg for all rows in a single pass over the table, then the outer query filters. This is dramatically more efficient for large tables because it avoids the per-row subquery execution. Demonstrating this rewrite in a data engineering interview signals strong query optimization skills.

EXISTS vs IN: When to Use Each

EXISTS returns TRUE as soon as the subquery produces one row - it stops searching. IN evaluates the subquery completely and builds a list, then checks membership. For large subquery result sets, EXISTS is generally faster because it short-circuits. However, when the subquery result set is small and cached, IN can be comparable or faster. NULL handling also differs: IN with NULLs in the subquery result can produce unexpected FALSE results, while EXISTS is unaffected by NULLs.