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.