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

Overview - Why subqueries are needed

What is it?

A subquery is a query written inside another query. It lets you use the result of one query as input for another. This helps break complex questions into smaller, manageable parts. Subqueries can appear in SELECT, FROM, or WHERE clauses.

Why it matters

Without subqueries, you would struggle to ask layered questions in a database. They let you find answers that depend on other answers, like finding customers who bought the most expensive product. Without them, queries would be longer, harder to write, and less clear.

Where it fits

Before learning subqueries, you should understand basic SELECT queries and filtering with WHERE. After subqueries, you can learn about JOINs, window functions, and query optimization to handle even more complex data questions.

Mental Model

Core Idea

A subquery is a question inside a question that helps solve complex data problems step-by-step.

Think of it like...

Imagine you want to find the tallest person in a room, then find who is sitting next to them. First, you find the tallest person (subquery), then you use that answer to find their neighbor (outer query).

┌───────────────┐
│ Outer Query   │
│  Uses result  │
│  from subquery│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Subquery      │
│  Finds data   │
│  needed first │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Basic SELECT Query Review

Concept: Understanding how to retrieve data from a table using simple SELECT statements.

A SELECT query asks the database to give you certain columns from a table. For example, SELECT name FROM customers; returns all customer names.

Result

A list of names from the customers table.

Knowing how to write simple queries is essential before adding complexity like subqueries.

2

Foundation

Filtering Data with WHERE Clause

Concept: Learn how to narrow down results using conditions.

The WHERE clause lets you pick only rows that meet certain criteria. For example, SELECT name FROM customers WHERE age > 30; returns names of customers older than 30.

Result

A filtered list of customer names based on age.

Filtering is the first step to asking more precise questions, which subqueries build upon.

3

Intermediate

Introducing Subqueries in WHERE

🤔Before reading on: do you think you can use a query inside WHERE to compare values? Commit to yes or no.

Concept: Using a query inside the WHERE clause to compare each row against a set of values or a single value.

You can write a query inside WHERE to filter rows based on another query's result. For example, SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total > 100); finds customers who made orders over 100.

Result

Names of customers who placed expensive orders.

Understanding that subqueries can dynamically filter data based on other data makes queries more powerful and flexible.

4

Intermediate

Subqueries in FROM Clause

🤔Before reading on: do you think a subquery can act like a temporary table in FROM? Commit to yes or no.

Concept: Using a subquery as a temporary table to organize or summarize data before the main query uses it.

A subquery in FROM creates a temporary result set. For example, SELECT avg_total FROM (SELECT customer_id, AVG(total) AS avg_total FROM orders GROUP BY customer_id) AS avg_orders WHERE avg_total > 50; finds customers with average order totals above 50.

Result

List of average order totals above 50 per customer.

Seeing subqueries as temporary tables helps break complex queries into understandable parts.

5

Intermediate

Subqueries in SELECT Clause

🤔Before reading on: can a subquery return a single value for each row in SELECT? Commit to yes or no.

Concept: Using subqueries to calculate values for each row in the main query's result.

You can put a subquery in SELECT to get related data. For example, SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.id) AS order_count FROM customers; shows each customer's name and how many orders they made.

Result

A list of customers with their order counts.

Using subqueries in SELECT lets you enrich each row with extra calculated data.

6

Advanced

Correlated vs Non-Correlated Subqueries

🤔Before reading on: do you think subqueries can depend on each row of the outer query? Commit to yes or no.

Concept: Understanding the difference between subqueries that run once (non-correlated) and those that run for each row (correlated).

Non-correlated subqueries run once and return a result used by the outer query. Correlated subqueries use values from the outer query and run repeatedly. For example, a correlated subquery counts orders per customer by referencing the outer customer's id.

Result

More precise and dynamic query results depending on row context.

Knowing this difference helps write efficient queries and avoid performance issues.

7

Expert

Performance Implications and Optimization

🤔Before reading on: do you think all subqueries perform equally well? Commit to yes or no.

Concept: Understanding how subqueries affect query speed and how to optimize or rewrite them.

Subqueries, especially correlated ones, can slow down queries because they run multiple times. Sometimes rewriting subqueries as JOINs or using WITH (CTE) clauses improves performance. PostgreSQL query planner decides the best way but knowing this helps write better queries.

Result

Faster queries and better resource use in real applications.

Understanding performance helps avoid slow queries and scale databases effectively.

Under the Hood

When a query with a subquery runs, the database executes the inner query first (for non-correlated subqueries) or repeatedly for each row (for correlated subqueries). The results are stored temporarily and used by the outer query to filter, join, or calculate values. The query planner analyzes the whole query to decide the best execution plan, sometimes flattening subqueries or converting them to joins.

Why designed this way?

Subqueries were introduced to let users express complex questions naturally and modularly. Instead of writing one huge query, breaking it into parts makes it easier to understand and maintain. Alternatives like joins existed but subqueries offer more flexibility in some cases, especially for filtering and aggregation.

┌───────────────┐
│ Outer Query   │
│  Uses subquery│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Subquery      │
│  Executes     │
│  First        │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Temporary     │
│  Result Set   │
└───────────────┘

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 can be slower, especially correlated ones, because they may run multiple times. Joins are often more efficient.

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

Quick: Can subqueries return multiple columns in WHERE clause? Commit to yes or no.

Common Belief: Subqueries in WHERE can return multiple columns to compare with outer query.

Reality: Subqueries in WHERE must return a single column or a single value to compare properly.

Why it matters: Trying to return multiple columns causes errors and confusion in query writing.

Quick: Do you think subqueries always run before the outer query? Commit to yes or no.

Common Belief: All subqueries run completely before the outer query starts.

Reality: Correlated subqueries run repeatedly for each row of the outer query, not just once before it.

Why it matters: Misunderstanding this leads to unexpected slow queries and difficulty optimizing.

Quick: Can subqueries replace all joins? Commit to yes or no.

Common Belief: Subqueries can always replace joins without any difference.

Reality: Some queries are more naturally and efficiently expressed with joins; subqueries are not always a substitute.

Why it matters: Using subqueries blindly can make queries harder to read and slower.

Expert Zone

1

Some correlated subqueries can be internally transformed by PostgreSQL into joins for better performance, but this depends on query structure.

2

Using WITH (Common Table Expressions) can make complex subqueries more readable and sometimes optimize execution by materializing results.

3

Subqueries in SELECT that return multiple rows cause errors; understanding scalar vs set-returning subqueries is key for advanced query writing.

When NOT to use

Avoid subqueries when performance is critical and the same result can be achieved with joins or window functions. For large datasets, correlated subqueries often slow down queries. Instead, use JOINs, CTEs, or window functions for better efficiency.

Production Patterns

In real systems, subqueries are used for filtering based on aggregated data, calculating per-row metrics, and isolating complex logic. Developers often combine subqueries with CTEs for clarity and maintainability. Query planners in PostgreSQL optimize many subqueries, but understanding when to rewrite them is crucial for production performance.

Connections

Functional Programming

Subqueries are like nested function calls where one function's output feeds another.

Understanding subqueries as nested operations helps grasp how data flows and dependencies work in queries.

Mathematical Set Theory

Subqueries often represent subsets or filtered sets used within larger sets.

Knowing set operations clarifies how subqueries filter or aggregate data within the whole dataset.

Project Management

Breaking a big task into smaller subtasks mirrors how subqueries break complex queries into parts.

Seeing subqueries as modular steps helps appreciate their role in managing complexity.

Common Pitfalls

#1 Using a subquery in WHERE that returns multiple columns.

Wrong approach:

SELECT name FROM customers WHERE (SELECT id, age FROM orders) = customers.id;

Correct approach:

SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders);

Root cause:

Misunderstanding that WHERE expects a single column or value for comparison.

#2 Writing a correlated subquery that causes slow performance.

Wrong approach:

SELECT name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id AND orders.total > 100);

Correct approach:

WITH expensive_orders AS (SELECT DISTINCT customer_id FROM orders WHERE total > 100) SELECT name FROM customers JOIN expensive_orders ON customers.id = expensive_orders.customer_id;

Root cause:

Not realizing correlated subqueries run once per row, causing inefficiency.

#3 Expecting subqueries to always run before outer query.

Wrong approach:

Assuming SELECT * FROM customers WHERE id = (SELECT max(id) FROM customers); runs subquery once but using correlated subquery instead.

Correct approach:

SELECT * FROM customers WHERE id = (SELECT max(id) FROM customers);

Root cause:

Confusing correlated and non-correlated subqueries and their execution timing.

Key Takeaways

Subqueries let you ask layered questions by embedding one query inside another.

They make complex data retrieval easier to write and understand by breaking problems into parts.

Subqueries can appear in WHERE, FROM, or SELECT clauses, each serving different purposes.

Understanding correlated versus non-correlated subqueries is key to writing efficient queries.

Knowing when to use subqueries versus joins or CTEs helps optimize performance in real databases.

Practice

1. Why do we use subqueries in PostgreSQL?
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY');

easy

A. To change data types of columns

B. To speed up the database server automatically

C. To ask a question inside another query and filter results based on it

D. To create new tables from existing ones

Solution

1. Step 1: Understand the role of the subquery

   The subquery (SELECT id FROM departments WHERE location = 'NY') finds department IDs located in NY.

2. Step 2: See how the main query uses the subquery

   The main query selects employees whose department_id matches any ID from the subquery result.

3. Final Answer:

   To ask a question inside another query and filter results based on it -> Option C

4. Quick Check:

   Subqueries filter data using inner query results [OK]

Hint: Subqueries answer inner questions to filter outer query [OK]

Common Mistakes:

• Thinking subqueries speed up queries automatically
• Confusing subqueries with table creation
• Using subqueries to change data types

2. Which of the following is the correct syntax for a subquery in PostgreSQL?

easy

A. SELECT * FROM table WHERE column == (SELECT MAX(column) FROM table);

B. SELECT * FROM table WHERE column = (SELECT MAX(column) FROM table);

C. SELECT * FROM table WHERE column = SELECT MAX(column) FROM table;

D. SELECT * FROM table WHERE column IN SELECT MAX(column) FROM table;

Solution

1. Step 1: Identify correct subquery syntax

   Subqueries must be enclosed in parentheses and used with comparison operators like = or IN.

2. Step 2: Check each option

   SELECT * FROM table WHERE column = (SELECT MAX(column) FROM table); correctly uses parentheses and = with a subquery. The other options have syntax errors like using == instead of =, omitting parentheses entirely, or missing them after IN.

3. Final Answer:

   SELECT * FROM table WHERE column = (SELECT MAX(column) FROM table); -> Option B

4. Quick Check:

   Subqueries need parentheses and proper operators [OK]

Hint: Subqueries always go inside parentheses [OK]

Common Mistakes:

• Using double equals (==) instead of single equals (=)
• Omitting parentheses around subqueries
• Using IN without parentheses

3. What will be the output of this query?

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

medium

A. Syntax error due to subquery

B. Names of employees earning less than the average salary

C. All employee names

D. Names of employees earning more than the average salary

Solution

1. Step 1: Understand the subquery

   The subquery (SELECT AVG(salary) FROM employees) calculates the average salary of all employees.

2. Step 2: Apply the condition in the main query

   The main query selects employee names where their salary is greater than this average.

3. Final Answer:

   Names of employees earning more than the average salary -> Option D

4. Quick Check:

   Subquery returns average; main query filters salaries above it [OK]

Hint: Subqueries can return single values to compare in main query [OK]

Common Mistakes:

• Thinking it returns employees below average
• Assuming syntax error due to subquery
• Confusing AVG with SUM

4. Identify the error in this query:

SELECT name FROM employees WHERE department_id = SELECT id FROM departments WHERE name = 'Sales';

medium

A. Missing parentheses around the subquery

B. Wrong comparison operator used

C. Subquery returns multiple rows causing error

D. Incorrect table name used

Solution

1. Step 1: Check subquery syntax

   The subquery must be enclosed in parentheses to be valid in the WHERE clause.

2. Step 2: Identify the missing parentheses

   The query misses parentheses around SELECT id FROM departments WHERE name = 'Sales', causing syntax error.

3. Final Answer:

   Missing parentheses around the subquery -> Option A

4. Quick Check:

   Subqueries need parentheses to be valid [OK]

Hint: Always put subqueries inside parentheses [OK]

Common Mistakes:

• Forgetting parentheses around subqueries
• Assuming wrong operator causes error
• Thinking table name is incorrect

5. You want to find employees who earn more than the average salary in their own department. Which query correctly uses a subquery to achieve this?

hard

A. SELECT name, salary, department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);

B. SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

C. SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees);

D. SELECT name FROM employees WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);

Solution

1. Step 1: Understand the requirement

   We need to compare each employee's salary to the average salary of their own department.

2. Step 2: Analyze each option

   SELECT name, salary, department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id); uses a correlated subquery that calculates average salary per employee's department. SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); compares to overall average, ignoring departments. SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees); compares salary to all salaries, not averages. SELECT name FROM employees WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id); compares to a list of averages but uses ANY incorrectly.

3. Final Answer:

   SELECT name, salary, department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id); -> Option A

4. Quick Check:

   Correlated subquery compares salary to department average [OK]

Hint: Use correlated subqueries to compare within groups [OK]

Common Mistakes:

• Comparing to overall average instead of department average
• Using ALL or ANY incorrectly with averages
• Not correlating subquery with outer query