Why subqueries are needed in PostgreSQL - Visual Breakdown

Concept Flow - Why subqueries are needed

Start Query

↓

Identify main query

↓

Detect subquery needed?

No→Run main query alone

Yes↓

Run subquery first

↓

Use subquery result in main query

↓

Return final result

The database first runs the subquery to get needed data, then uses that data in the main query to produce the final result.

Execution Sample

PostgreSQL

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

Find employees who work in the Sales department by first finding the Sales department's id.

Execution Table

Step	Action	Query Part	Result/Value
1	Start main query	SELECT name FROM employees WHERE department_id = (...)	Waiting for subquery result
2	Run subquery	SELECT id FROM departments WHERE name = 'Sales'	Returns id = 3
3	Use subquery result in main query	WHERE department_id = 3	Filter employees with department_id = 3
4	Fetch employees	SELECT name FROM employees WHERE department_id = 3	Returns names: Alice, Bob
5	Return final result	Final output	Alice, Bob

💡 Subquery returns department id 3, main query uses it to find employees in Sales.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
subquery_result	null	3	3	3
main_query_filter	null	null	department_id = 3	department_id = 3
final_output	null	null	null	Alice, Bob

Key Moments - 2 Insights

Why do we run the subquery before the main query?

Because the main query needs the subquery's result (department id) to filter employees correctly, as shown in execution_table step 2 and 3.

Can we write the main query without the subquery?

Not easily, because we need the department id for 'Sales', which is only known by querying the departments table first (see execution_table step 2).

Visual Quiz - 3 Questions

Look at the execution table, what is the subquery result at step 2?

A Sales

B 3

C Alice

D department_id

💡 Hint

Check the 'Result/Value' column at step 2 in the execution_table.

At which step does the main query use the subquery result?

A Step 3

B Step 4

C Step 1

D Step 5

💡 Hint

Look for when 'WHERE department_id = 3' is applied in the execution_table.

If the subquery returned id 5 instead of 3, what would change in the execution table?

A Step 3 would be skipped

B Step 4 would return Alice and Bob anyway

C Step 2 result would be 5, and main query filter would be department_id = 5

D Final output would be empty always

💡 Hint

Check how subquery_result and main_query_filter change in variable_tracker.

Concept Snapshot

Subqueries run first to get data needed by the main query.
They let you filter or calculate based on another query's result.
Syntax: Use parentheses () to write subqueries inside main queries.
Subqueries help when you need info from one table to query another.
Without subqueries, some queries would be hard or impossible to write.

Full Transcript

This visual shows why subqueries are needed in SQL. The main query depends on the subquery's result to filter data correctly. First, the subquery runs and finds the department id for 'Sales'. Then, the main query uses that id to find employees in that department. The execution table traces each step, showing how the subquery result is used. Variables track the subquery result and how it filters the main query. Key moments clarify why the subquery runs first and why it can't be skipped. The quiz tests understanding by asking about specific steps and changes if the subquery result changes. The snapshot summarizes the concept simply: subqueries get data needed by the main query to work properly.

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