SQL subqueries

Scalar, table, and EXISTS

A scalar subquery returns one value, such as the average current salary. A table subquery belongs in FROM or JOIN and should usually be a CTE once it grows. EXISTS asks whether any row satisfies a condition, which is the right tool for retention.

Correlation

A correlated subquery refers to the outer row. The latest salary pattern uses MAX(effective_date) WHERE employee_id equals the employee currently being considered. It runs conceptually once per employee.

Example

SELECT e.name
FROM employees AS e
JOIN salaries AS s ON s.employee_id = e.id
WHERE s.effective_date = (
  SELECT MAX(s2.effective_date)
  FROM salaries AS s2
  WHERE s2.employee_id = e.id
);

The inner query sees e.id from the outer row. That is the correlation.

Practice

• MediumHR
  Above-average current salaries

  List employees whose latest salary is above the average latest salary.

• HardSaaS
  Workspace cohorts and active accounts

  For each workspace creation month, count workspaces and how many are currently active.

• HardSaaS
  Second-month user retention

  Find users who were active in their signup month and the following calendar month.

• HardHR
  Rank employees by current salary

  Rank every employee by their current annual salary.

• HardHR
  Current salary for each employee

  Return one current salary row per employee.

Common mistakes

• A scalar subquery that returns more than one row.
• Filtering history with a global MAX instead of a per-employee MAX.
• Using IN on a nullable column and being surprised by the three-valued logic.
• Repeating a long subquery instead of naming it with a CTE.

FAQ

When is a CTE better than a subquery?

When you reference the intermediate result more than once, or when a reviewer needs a name for the business rule. Customer spend and workspace cohorts in QueryLab are written that way.

Is EXISTS faster than IN?

Often similar after planning. EXISTS is clearer when you only care that a match exists, and it avoids pulling a column you will not display.

Related practice

• SQL practice
• Beginner SQL practice
• SQL JOINs
• GROUP BY