Subqueries in FROM (derived tables) in PostgreSQL - Step-by-Step Execution

Concept Flow - Subqueries in FROM (derived tables)

Start Query

↓

Execute Subquery in FROM

↓

Create Derived Table with Subquery Result

↓

Use Derived Table in Outer Query

↓

Apply Outer Query Conditions/Aggregations

↓

Return Final Result

The query first runs the subquery inside FROM, creating a temporary table (derived table). Then the outer query uses this derived table to produce the final result.

Execution Sample

PostgreSQL

SELECT dt.department, AVG(dt.salary) AS avg_salary
FROM (SELECT department, salary FROM employees) AS dt
GROUP BY dt.department;

This query calculates the average salary per department by first selecting department and salary from employees as a derived table.

Execution Table

Step	Action	Subquery Result	Derived Table Created	Outer Query Operation	Output
1	Execute subquery SELECT department, salary FROM employees	[{Sales, 5000}, {Sales, 6000}, {HR, 4500}, {HR, 4700}]	Derived table dt with 4 rows	N/A	N/A
2	Use derived table dt in outer query	N/A	dt available	Group rows by department	N/A
3	Calculate AVG(salary) for Sales	N/A	dt available	AVG(5000,6000) = 5500	Sales, 5500
4	Calculate AVG(salary) for HR	N/A	dt available	AVG(4500,4700) = 4600	HR, 4600
5	Return final result	N/A	N/A	N/A	[{Sales, 5500}, {HR, 4600}]

💡 All rows processed and grouped, final averages computed.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
subquery_result	empty	[{Sales,5000},{Sales,6000},{HR,4500},{HR,4700}]	[{Sales,5000},{Sales,6000},{HR,4500},{HR,4700}]	[{Sales,5000},{Sales,6000},{HR,4500},{HR,4700}]	[{Sales,5000},{Sales,6000},{HR,4500},{HR,4700}]	[{Sales,5000},{Sales,6000},{HR,4500},{HR,4700}]
derived_table_dt	none	created with 4 rows	available for grouping	available for grouping	available for grouping	available for grouping
avg_salary_Sales	none	none	none	5500	5500	5500
avg_salary_HR	none	none	none	none	4600	4600

Key Moments - 3 Insights

Why do we need to give the subquery an alias like 'dt'?

The alias 'dt' names the derived table so the outer query can refer to it. Without it, the query will cause an error. See execution_table step 2 where 'dt' is used.

Is the subquery executed before or after the outer query?

The subquery runs first to produce the derived table. Then the outer query uses that result. This is shown in execution_table steps 1 and 2.

Can the outer query use columns directly from the original table?

No, when using a subquery in FROM, the outer query only sees the columns output by the subquery (derived table). It cannot access original tables directly here.

Visual Quiz - 3 Questions

Look at the execution table, what is the average salary for the Sales department at step 3?

A 6000

B 5500

C 5000

D 4600

💡 Hint

Check the 'Outer Query Operation' and 'Output' columns at step 3 in the execution_table.

At which step is the derived table 'dt' created and ready for use?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Look at the 'Derived Table Created' column in the execution_table to find when 'dt' is created.

If the subquery selected an extra column 'employee_id', how would the outer query be affected?

A The outer query cannot use 'employee_id' because it's from the original table.

B The query will fail without aliasing.

C The outer query can use 'employee_id' if included in the derived table.

D The average salary calculation will be incorrect.

💡 Hint

Refer to the concept that the outer query sees only columns output by the subquery (derived table).

Concept Snapshot

Subqueries in FROM create a temporary table (derived table) for the outer query.
Syntax: FROM (subquery) AS alias
The outer query uses alias to refer to subquery results.
Subquery runs first, then outer query processes its output.
Useful for complex queries needing intermediate results.

Full Transcript

This visual execution shows how a subquery inside the FROM clause runs first to produce a derived table. The outer query then uses this derived table to perform grouping and aggregation. We traced a query that calculates average salary per department by first selecting department and salary from employees as a derived table named 'dt'. The execution table shows each step: running the subquery, creating the derived table, grouping by department, calculating averages, and returning the final result. Variables track the subquery result, derived table availability, and average salary calculations. Key moments clarify why aliasing is required, the order of execution, and column visibility. The quiz tests understanding of these steps and effects of changing the subquery. The snapshot summarizes the concept and syntax for quick reference.