Exploring SQL Subqueries: Advanced Queries for Data Analysis

A subquery is a SELECT inside another SELECT. It lets you ask questions that depend on the answer to another question -- all in one query.

Think of it as a two-step lookup. Step one: find a value. Step two: use that value to filter or compare.

Countries larger than Russia

SELECT name
FROM world
WHERE population > (
    SELECT population
    FROM world
    WHERE name = 'Russia'
)

The inner query finds Russia's population. The outer query returns every country with a higher population. You could do this in two separate queries, but the subquery handles it in one trip to the database.

European countries richer than the UK (per capita)

SELECT name
FROM world
WHERE continent = 'Europe'
AND gdp/population > (
    SELECT gdp/population
    FROM world
    WHERE name = 'United Kingdom'
)

Same pattern. The subquery computes the UK's per-capita GDP. The outer query filters European countries above that threshold.

When to use subqueries

Subqueries shine when you need to compare rows against a computed value from the same or a different table.

Common patterns:

• Scalar subqueries (return one value): "Find all rows where X is greater than the average"
• IN subqueries (return a list): "Find all rows where ID is in (SELECT id FROM ...)"
• EXISTS subqueries (return true/false): "Find all rows where a matching row exists in another table"

The trade-off

Benefit: Subqueries keep complex logic in a single query. No temp variables, no application-layer glue code.

Cost: Deeply nested subqueries get hard to read fast. If your subquery runs once per row in the outer query (a correlated subquery), performance can degrade on large datasets.

When a subquery gets too complex, consider rewriting it as a JOIN or a Common Table Expression (CTE with WITH). Same result, often clearer and faster.