Why subqueries are needed in SQL - Why It Works This Way

Overview - Why subqueries are needed

What is it?

Subqueries are queries written inside another query. They let you use the result of one query as input for another. This helps break complex questions into smaller parts that the database can solve step-by-step. Subqueries can appear in places like SELECT, WHERE, or FROM clauses.

Why it matters

Without subqueries, writing complex database questions would be much harder and messier. You would have to write many separate queries and combine results manually. Subqueries let you ask layered questions, making your data searches more powerful and easier to understand. This saves time and reduces errors in real-world data work.

Where it fits

Before learning subqueries, you should understand basic SQL SELECT statements and simple filtering with WHERE. After mastering subqueries, you can learn about JOINs, advanced filtering, and query optimization techniques.

Mental Model

Core Idea

A subquery is a question inside another question, letting you use one answer to help find another.

Think of it like...

Imagine you want to find the tallest person in a group, but only among those who scored above 90 on a test. First, you ask 'Who scored above 90?' Then, from that smaller group, you ask 'Who is the tallest?' The first question is like a subquery inside the bigger question.

Main Query
  ├─ WHERE condition uses
  │    └─ Subquery result
  └─ SELECT columns

Example:
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)

Build-Up - 7 Steps

1

Foundation

Basic SQL Query Structure

Concept: Understanding how a simple SQL query works is essential before adding subqueries.

A basic SQL query asks the database to give you data from a table. For example, SELECT name FROM employees; asks for all employee names. You can add conditions like WHERE salary > 50000 to filter results.

Result

You get a list of employee names, possibly filtered by salary.

Knowing how simple queries work helps you see how subqueries fit as smaller questions inside bigger ones.

2

Foundation

Filtering Data with WHERE Clause

Concept: The WHERE clause lets you pick rows that meet certain conditions.

For example, SELECT name FROM employees WHERE department = 'Sales'; returns only employees in Sales. This filtering is the first step to understanding how subqueries can provide dynamic conditions.

Result

You get names of employees only in the Sales department.

Understanding filtering shows why sometimes you need more complex conditions that subqueries can provide.

3

Intermediate

Introducing Subqueries in WHERE Clause

🤔Before reading on: do you think a subquery can return multiple rows or just one? Commit to your answer.

Concept: Subqueries can be used inside WHERE to compare a value against a dynamic result from another query.

Example: SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); Here, the subquery finds the average salary, and the main query finds employees earning more than that average.

Result

You get names of employees who earn more than the average salary.

Understanding that subqueries can provide dynamic values for filtering makes queries more flexible and powerful.

4

Intermediate

Subqueries in SELECT and FROM Clauses

🤔Before reading on: can subqueries be used to create temporary tables inside a query? Commit to your answer.

Concept: Subqueries can appear in SELECT to compute values or in FROM to act as temporary tables for the main query.

Example in FROM: SELECT dept, avg_salary FROM (SELECT department AS dept, AVG(salary) AS avg_salary FROM employees GROUP BY department) AS dept_avg WHERE avg_salary > 50000; This uses a subquery as a temporary table.

Result

You get departments with average salary above 50000.

Knowing subqueries can create temporary tables helps you organize complex data calculations inside one query.

5

Intermediate

Correlated vs Non-Correlated Subqueries

🤔Before reading on: do you think a subquery can depend on each row of the outer query? Commit to your answer.

Concept: Non-correlated subqueries run once and provide a fixed result; correlated subqueries run once per row of the outer query, using values from that row.

Example correlated subquery: SELECT e1.name FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department); Here, the subquery depends on each employee's department.

Result

You get employees earning more than their department's average salary.

Understanding correlation explains how subqueries can adapt dynamically per row, enabling more precise filtering.

6

Advanced

Performance Considerations with Subqueries

🤔Before reading on: do you think subqueries always run faster than JOINs? Commit to your answer.

Concept: Subqueries can sometimes slow down queries, especially correlated ones, because they may run multiple times. Knowing when to use subqueries or JOINs affects performance.

Example: Correlated subqueries run once per outer row, which can be slow on large tables. Sometimes rewriting as JOIN improves speed.

Result

You learn that query speed depends on structure, not just correctness.

Knowing performance tradeoffs helps you write efficient queries and avoid slow database responses.

7

Expert

Subqueries in Complex Real-World Queries

🤔Before reading on: can subqueries be nested multiple levels deep? Commit to your answer.

Concept: Subqueries can be nested inside other subqueries multiple times, enabling very complex data questions. However, this can make queries hard to read and maintain.

Example: SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location_id IN (SELECT id FROM locations WHERE country = 'USA')); This finds employees in departments located in the USA.

Result

You get employees working in USA-based departments.

Understanding deep nesting shows the power and complexity subqueries can bring, but also warns about maintainability challenges.

Under the Hood

When a query with a subquery runs, the database first executes the inner subquery to get its result. Then it uses that result in the outer query. For correlated subqueries, the inner query runs repeatedly for each row of the outer query, which can be costly. The database query planner tries to optimize this by rewriting queries or caching results.

Why designed this way?

Subqueries were designed to let users write complex questions in a natural, layered way. Before subqueries, users had to write multiple separate queries and combine results manually. Subqueries simplify this by embedding one query inside another, making SQL more expressive and closer to human thinking.

┌───────────────┐
│ Outer Query   │
│ ┌───────────┐ │
│ │ Subquery  │ │
│ └───────────┘ │
└───────┬───────┘
        │
        ▼
  Subquery Result
        │
        ▼
 Outer Query uses result

Myth Busters - 4 Common Misconceptions

Quick: Do you think subqueries always run faster than JOINs? Commit to yes or no.

Common Belief: Subqueries are always faster and better than JOINs.

Reality: Subqueries, especially correlated ones, can be slower than JOINs because they may run multiple times. JOINs often let the database optimize better.

Why it matters: Believing this can lead to writing slow queries that hurt application performance and user experience.

Quick: Can a subquery return multiple columns when used in WHERE? Commit yes or no.

Common Belief: Subqueries in WHERE can return multiple columns.

Reality: Subqueries in WHERE must return a single column or a single value. Returning multiple columns causes errors.

Why it matters: Misunderstanding this causes syntax errors and confusion when writing filters.

Quick: Do you think subqueries can only be used in WHERE clauses? Commit yes or no.

Common Belief: Subqueries can only appear in WHERE clauses.

Reality: Subqueries can appear in SELECT, FROM, and HAVING clauses as well, serving different purposes.

Why it matters: Limiting subqueries to WHERE restricts query design and misses powerful SQL features.

Quick: Can subqueries be nested infinitely without issues? Commit yes or no.

Common Belief: You can nest subqueries as deep as you want without problems.

Reality: Deeply nested subqueries can cause performance issues and make queries hard to read and maintain.

Why it matters: Ignoring this leads to complex, slow queries that are difficult to debug or change.

Expert Zone

1

Some databases optimize correlated subqueries by transforming them into JOINs internally, improving performance without changing query logic.

2

Using EXISTS with subqueries can be more efficient than IN when checking for existence, especially with large datasets.

3

Subqueries in the FROM clause create derived tables that can be indexed temporarily, which affects query planning and speed.

When NOT to use

Avoid subqueries when simple JOINs can express the same logic more clearly and efficiently. For very large datasets, consider using temporary tables or materialized views instead of deeply nested subqueries.

Production Patterns

In real systems, subqueries are often used for filtering based on aggregates, checking existence of related data, and creating temporary result sets. Experts balance subqueries with JOINs and use query profiling tools to optimize performance.

Connections

Functional Programming

Both use nested expressions where one function's output feeds another's input.

Understanding subqueries as nested queries parallels how functions compose in programming, helping grasp layered logic.

Mathematical Set Theory

Subqueries often represent subsets or conditions on sets, similar to set operations in math.

Seeing subqueries as set filters clarifies how SQL manipulates groups of data logically.

Russian Nesting Dolls (Matryoshka)

Subqueries nest inside queries like dolls inside dolls, each smaller one fitting inside a bigger one.

This cross-domain view highlights the layered nature of subqueries and the importance of managing complexity.

Common Pitfalls

#1 Using a subquery that returns multiple columns in a WHERE clause expecting a single value.

Wrong approach:

SELECT name FROM employees WHERE salary > (SELECT department, AVG(salary) FROM employees GROUP BY department);

Correct approach:

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Root cause:

Misunderstanding that WHERE expects a single column or value from subqueries.

#2 Writing a correlated subquery that runs inefficiently on large tables without indexing.

Wrong approach:

SELECT name FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department);

Correct approach:

Create an indexed temporary table with department averages, then JOIN it:
WITH dept_avg AS (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department)
SELECT e.name FROM employees e JOIN dept_avg d ON e.department = d.department WHERE e.salary > d.avg_salary;

Root cause:

Not realizing correlated subqueries run repeatedly and need optimization.

#3 Assuming subqueries can replace all JOINs without performance cost.

Wrong approach:

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY');

Correct approach:

SELECT e.name FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.location = 'NY';

Root cause:

Believing subqueries are always simpler and faster than JOINs.

Key Takeaways

Subqueries let you write layered questions by embedding one query inside another, making complex data retrieval easier.

They can appear in WHERE, SELECT, and FROM clauses, each serving different purposes like filtering, computing, or creating temporary tables.

Correlated subqueries depend on each row of the outer query and can impact performance if not used carefully.

Understanding when to use subqueries versus JOINs is key to writing efficient and readable SQL queries.

Deeply nested subqueries increase complexity and can slow down queries, so balance power with maintainability.