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.

Practice

1. What is the main purpose of using a subquery in the FROM clause in PostgreSQL?

easy

A. To create a permanent table in the database

B. To update data in multiple tables at once

C. To delete rows from a table based on a condition

D. To create a temporary table that can be used in the main query

Solution

1. Step 1: Understand the role of subqueries in FROM

   Subqueries in the FROM clause act like temporary tables that exist only during the query execution.

2. Step 2: Compare with other options

   Updating or deleting data and creating permanent tables are not done by subqueries in FROM.

3. Final Answer:

   To create a temporary table that can be used in the main query -> Option D

4. Quick Check:

   Subquery in FROM = temporary table [OK]

Hint: Subquery in FROM = think temporary table [OK]

Common Mistakes:

• Thinking subqueries in FROM create permanent tables
• Confusing subqueries with UPDATE or DELETE statements
• Forgetting subqueries need an alias

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

easy

A. SELECT * FROM (SELECT id FROM users) AS sub;

B. SELECT * FROM users WHERE (SELECT id FROM users);

C. SELECT * FROM users JOIN (SELECT id FROM users);

D. SELECT * FROM users (SELECT id FROM users) AS sub;

Solution

1. Step 1: Identify correct subquery syntax in FROM

   The subquery must be enclosed in parentheses and given an alias using AS.

2. Step 2: Check each option

   SELECT * FROM (SELECT id FROM users) AS sub; correctly uses parentheses and alias. Others either misuse WHERE, JOIN without ON, or lack alias.

3. Final Answer:

   SELECT * FROM (SELECT id FROM users) AS sub; -> Option A

4. Quick Check:

   Subquery in FROM needs parentheses + alias [OK]

Hint: Always alias subqueries in FROM with AS [OK]

Common Mistakes:

• Omitting alias after subquery
• Using subquery in WHERE without proper syntax
• Trying to JOIN without ON condition

3. Given the tables:
users(id, name)
orders(id, user_id, amount)
What will the following query return?

SELECT sub.name, sub.total FROM (SELECT u.name, SUM(o.amount) AS total FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.name) AS sub WHERE sub.total > 100;

medium

A. Syntax error due to missing GROUP BY

B. All users with their total order amount including those with zero

C. Names of users with total order amount greater than 100

D. Empty result because no user has orders

Solution

1. Step 1: Analyze the subquery

   The subquery calculates total order amount per user using LEFT JOIN and GROUP BY user name.

2. Step 2: Apply the outer WHERE filter

   The outer query filters users whose total order amount is greater than 100.

3. Final Answer:

   Names of users with total order amount greater than 100 -> Option C

4. Quick Check:

   Subquery sums orders, outer filters total > 100 [OK]

Hint: Subquery computes totals; outer filters results [OK]

Common Mistakes:

• Ignoring GROUP BY causing syntax error
• Confusing LEFT JOIN with INNER JOIN results
• Assuming all users appear without filtering

4. Identify the error in this query:

SELECT sub.name, sub.order_count FROM (SELECT name, COUNT(*) FROM users GROUP BY name) sub;

medium

A. No error, query is correct

B. Missing alias for COUNT(*) column

C. Incorrect table name 'users'

D. Subquery missing parentheses

Solution

1. Step 1: Check subquery SELECT list

   COUNT(*) needs an alias to be referenced outside the subquery.

2. Step 2: Understand outer query references

   Outer query uses sub.order_count, but COUNT(*) column has no alias, causing error.

3. Final Answer:

   Missing alias for COUNT(*) column -> Option B

4. Quick Check:

   Aggregate columns need aliases in subqueries [OK]

Hint: Always alias aggregate columns in subqueries [OK]

Common Mistakes:

• Forgetting to alias aggregate functions
• Assuming subquery columns auto-named
• Ignoring parentheses around subquery

5. You want to find the average order amount per user but only for users who have placed more than 3 orders. Which query correctly uses a subquery in the FROM clause to achieve this?

hard

A. SELECT sub.user_id, sub.avg_amount FROM (SELECT user_id, AVG(amount) AS avg_amount, COUNT(*) AS order_count FROM orders GROUP BY user_id) AS sub WHERE sub.order_count > 3;

B. SELECT user_id, AVG(amount) FROM orders WHERE COUNT(*) > 3 GROUP BY user_id;

C. SELECT user_id, AVG(amount) FROM (SELECT * FROM orders WHERE COUNT(*) > 3) AS sub GROUP BY user_id;

D. SELECT user_id, AVG(amount) FROM orders GROUP BY user_id HAVING AVG(amount) > 3;

Solution

1. Step 1: Understand the requirement

   We need average order amount per user but only for users with more than 3 orders.

2. Step 2: Analyze each option

   SELECT sub.user_id, sub.avg_amount FROM (SELECT user_id, AVG(amount) AS avg_amount, COUNT(*) AS order_count FROM orders GROUP BY user_id) AS sub WHERE sub.order_count > 3; uses a subquery to calculate average and count per user, then filters users with order_count > 3 outside. Others misuse COUNT in WHERE or HAVING incorrectly.

3. Final Answer:

   SELECT sub.user_id, sub.avg_amount FROM (SELECT user_id, AVG(amount) AS avg_amount, COUNT(*) AS order_count FROM orders GROUP BY user_id) AS sub WHERE sub.order_count > 3; -> Option A

4. Quick Check:

   Subquery calculates aggregates; outer filters by count [OK]

Hint: Filter aggregates in outer query after subquery [OK]

Common Mistakes:

• Using COUNT(*) in WHERE clause directly
• Misplacing HAVING clause conditions
• Not aliasing subquery columns properly