Why subqueries are needed in SQL - Visual Breakdown

Concept Flow - Why subqueries are needed

Start Query

↓

Identify need for intermediate result

↓

Write subquery to get intermediate data

↓

Use subquery result in main query

↓

Execute subquery first

↓

Use subquery output in main query execution

↓

Return final result

Subqueries let us get intermediate results inside a bigger query, so we can use those results to filter or calculate more complex answers.

Execution Sample

SQL

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

This query finds employees who work in the 'Sales' department by first finding the department's id using a subquery.

Execution Table

Step	Action	Query Part	Result/Value
1	Start main query	SELECT name FROM employees WHERE department_id = (subquery)	No rows yet
2	Execute subquery	SELECT id FROM departments WHERE name = 'Sales'	Returns id = 3
3	Use subquery result	WHERE department_id = 3	Filter employees with department_id = 3
4	Retrieve matching employees	SELECT name FROM employees WHERE department_id = 3	Returns names: Alice, Bob
5	Return final result	Full query result	Alice, Bob

💡 Subquery executed first to get department id; then main query filters employees using that id.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
subquery_result	None	3	3	3
filtered_employees	None	None	Alice, Bob	Alice, Bob

Key Moments - 2 Insights

Why do we run the subquery before the main query filters employees?

Because the main query needs the department id from the subquery to know which employees to select, as shown in execution_table step 2 and 3.

Can we write this query without a subquery?

Not easily, because we need to find the department id dynamically based on the department name, which requires the subquery to get that intermediate value.

Visual Quiz - 3 Questions

Look at the execution_table, what is the subquery result at step 2?

A Alice

B 3

C Sales

D department_id

💡 Hint

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

At which step does the main query filter employees using the subquery result?

A Step 3

B Step 5

C Step 1

D Step 2

💡 Hint

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

If the subquery returned no rows, what would happen to the main query result?

A It would return all employees

B It would cause an error

C It would return no employees

D It would return employees from all departments except Sales

💡 Hint

Think about how filtering by a non-existent department_id affects the main query result.

Concept Snapshot

Subqueries let you run a query inside another query.
They help get intermediate results to use in the main query.
The subquery runs first, then its result filters or calculates in the main query.
Use subqueries to handle complex filtering or calculations that need data from another query.
Example: find employees in a department by first getting the department id with a subquery.

Full Transcript

Subqueries are needed when you want to use the result of one query inside another. For example, to find employees in the 'Sales' department, you first find the department's id with a subquery. This id is then used to filter employees in the main query. The subquery runs first and returns the department id. Then the main query uses that id to select employees. This process allows complex queries to be broken down into smaller steps, making them easier to write and understand. Without subqueries, you would struggle to get intermediate data needed for filtering or calculations inside a single query.