Subqueries

A subquery is simply a SELECT query nested inside another query, used wherever a single value, a list of values, or an entire mini result set is needed. This lesson covers the three places a subquery commonly appears, and how to tell when a subquery is clearer than a join, or when it's the other way around.

Learning Objectives

• Use a subquery inside WHERE to filter against a computed list of values.
• Use a scalar subquery inside SELECT to pull in a single computed value per row.
• Explain what makes a subquery "correlated," and compare subqueries to joins.

Subquery in WHERE

The inner query runs first, producing a list of department IDs. The outer query's IN then filters employees against that list, exactly like IN (1, 2, 3), except the values come from a query instead of being typed out by hand.

SELECT first_name, department
FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE name IN ('Engineering', 'Research')
);

Scalar Subquery in SELECT

A scalar subquery returns exactly one value, here, the company-wide average salary, repeated on every row alongside each employee's own salary for easy comparison. If a subquery used this way could return more than one row, the database raises an error, a scalar subquery must collapse to a single value.

SELECT first_name, salary,
  (SELECT AVG(salary) FROM employees) AS company_avg_salary
FROM employees;

Subquery in FROM (a Derived Table)

The inner query produces a complete mini result set, department averages, and the outer query treats it exactly like a regular table, giving it an alias (department_averages) and querying it further. This is useful when you need to filter or sort on a value that itself required aggregation, avg_salary here can't be filtered with WHERE directly, but it can once it's a column in a derived table.

SELECT department, avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS department_averages
WHERE avg_salary > 90000;

Correlated Subqueries

A correlated subquery references a column from the outer query (e.department here), which means it can't run once up front, it has to run again for every row the outer query considers, each time with that row's own department. This finds employees earning more than their own department's average, something a plain (non-correlated) subquery can't express.

SELECT first_name, salary, department
FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees WHERE department = e.department
);

Subquery vs Join

Many queries can be written either way. As a rule of thumb: reach for a join when you need columns from both tables in the final result, and a subquery when you just need to filter or compare against a computed value or list, without actually displaying anything from the inner query.

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Use IN rather than = when a subquery could plausibly return more than one row.
• Reach for EXISTS instead of IN for a correlated "does a matching row exist" check, it's typically faster.
• Consider rewriting a slow correlated subquery as a join with a derived table before assuming the query just can't be made faster.
• Always alias a derived table in FROM, even a short one-letter alias, it's required syntax, not optional style.

Interview Questions

Summary

This lesson covered subqueries in WHERE (filtering against a computed list), SELECT (a scalar value per row), and FROM (a derived table), what makes a subquery correlated, and how to decide between a subquery and a join for a given question.

That completes Module 2, Working With Data: writing data with INSERT, UPDATE, and DELETE, choosing correct data types, handling NULL properly, and summarizing and combining data with aggregate functions, GROUP BY, joins, and subqueries.

What's Next?

Module 3, Database Design, is next: primary and foreign keys, creating tables with constraints, indexes and performance, and transactions.