Subqueries

Explanation

A subquery is a SELECT nested inside another query. You use it when you need a value (or a set of values) computed first, then fed into the outer query. Common homes for a subquery:

• In WHERE, to filter against a computed value (salary > (subquery)).
• In FROM, as a derived table you join or select from.
• In SELECT, to compute a per-row related value.

A correlated subquery references a column from the outer query and re-runs for each row; a non-correlated subquery runs once and is reused.

Syntax

SELECT col
FROM table_a
WHERE col > (SELECT AVG(col) FROM table_a);

Interactive Example

Find employees earning above the company average. Then rank each employee against their department average using a subquery in SELECT.

Common Mistakes

• Using = with a multi-row subquery. Switch to IN when the inner query can return several rows.
• Correlated subqueries in tight loops. They execute once per outer row; for big tables a JOIN or window function is often faster.
• Aliasing confusion. Give the outer and inner tables distinct aliases so the database knows which salary you mean.

Best Practices

• Start with a non-correlated subquery when possible — it's easier to read and the optimizer can run it once.
• Use IN (subquery) for membership tests against a set.
• When a subquery feels heavy, try rewriting it as a JOIN or a WITH CTE (see the CTE lesson).

Practice Question

Using a subquery, list the departments (their name) whose average salary is above the overall company average. Hint: group inside the subquery and use IN.

Summary

Subqueries let you compute intermediate results inside a query, used in WHERE, FROM, or SELECT. Match the operator to the result shape (= for one value, IN for many), and watch correlated subqueries for performance on large tables.

FAQ