Subquery in FROM clause (derived table) in SQL - Step-by-Step Execution

Concept Flow - Subquery in FROM clause (derived table)

Start Query

↓

Identify FROM clause

↓

Detect Subquery

↓

Execute Subquery

↓

Create Derived Table

↓

Use Derived Table in Outer Query

↓

Execute Outer Query

↓

Return Final Result

The database first runs the subquery inside the FROM clause, treats its result as a temporary table, then runs the outer query using this temporary table.

Execution Sample

SQL

SELECT dept, avg_salary
FROM (SELECT department AS dept, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department) AS dept_avg
WHERE avg_salary > 50000;

This query calculates average salaries per department using a subquery in FROM, then selects departments with average salary over 50000.

Execution Table

Step	Action	Subquery Result	Outer Query Action	Output Rows
1	Start query execution	N/A	N/A	N/A
2	Execute subquery: SELECT department AS dept, AVG(salary) AS avg_salary FROM employees GROUP BY department	[{dept: 'HR', avg_salary: 48000}, {dept: 'IT', avg_salary: 60000}, {dept: 'Sales', avg_salary: 52000}]	Create derived table dept_avg	N/A
3	Apply outer query WHERE avg_salary > 50000 on derived table	N/A	Filter rows where avg_salary > 50000	[{dept: 'IT', avg_salary: 60000}, {dept: 'Sales', avg_salary: 52000}]
4	Return final result	N/A	Output filtered rows	[{dept: 'IT', avg_salary: 60000}, {dept: 'Sales', avg_salary: 52000}]

💡 Outer query finishes after filtering derived table rows by avg_salary > 50000

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
subquery_result	N/A	[{dept: 'HR', avg_salary: 48000}, {dept: 'IT', avg_salary: 60000}, {dept: 'Sales', avg_salary: 52000}]	[{dept: 'HR', avg_salary: 48000}, {dept: 'IT', avg_salary: 60000}, {dept: 'Sales', avg_salary: 52000}]	Same as After Step 2
derived_table	N/A	Created from subquery_result	Same	Same
filtered_rows	N/A	N/A	[{dept: 'IT', avg_salary: 60000}, {dept: 'Sales', avg_salary: 52000}]	Same

Key Moments - 3 Insights

Why does the subquery run before the outer query?

Because the subquery in the FROM clause creates a temporary table (derived table) that the outer query needs to use. This is shown in execution_table step 2 where the subquery runs first.

Is the derived table stored permanently in the database?

No, the derived table exists only during the query execution as a temporary result from the subquery, as seen in variable_tracker where 'derived_table' is created after step 2 and used immediately.

Can the outer query filter on columns from the subquery?

Yes, the outer query can filter on any columns produced by the subquery, as shown in execution_table step 3 where the WHERE clause filters on avg_salary.

Visual Quiz - 3 Questions

Look at the execution_table, what is the subquery_result after step 2?

A [{dept: 'HR', avg_salary: 48000}, {dept: 'IT', avg_salary: 60000}, {dept: 'Sales', avg_salary: 52000}]

B [{dept: 'HR', avg_salary: 60000}, {dept: 'IT', avg_salary: 48000}]

C Empty set

D N/A

💡 Hint

Check the 'Subquery Result' column in execution_table row with Step 2

At which step does the outer query apply the filter avg_salary > 50000?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Outer Query Action' column in execution_table to find when filtering happens

If the subquery returned no rows, what would the final output be?

A All rows from employees table

B Empty result set

C Error because no rows

D Only departments with salary > 50000

💡 Hint

Derived table is empty means outer query filters nothing, see variable_tracker for filtered_rows

Concept Snapshot

Subquery in FROM clause creates a temporary table (derived table).
Database runs subquery first, then outer query uses its result.
Derived table can be filtered or joined in outer query.
Useful for complex aggregations or intermediate results.
Syntax: FROM (subquery) AS alias
Always alias the subquery result.

Full Transcript

This visual execution shows how a subquery inside the FROM clause works. First, the database runs the subquery to get a temporary table called a derived table. Then, the outer query uses this derived table to filter or select data. For example, the subquery calculates average salaries per department. The outer query then selects only departments with average salary above 50000. The execution table traces each step: running the subquery, creating the derived table, filtering rows, and returning the final result. Variables track the subquery result, derived table, and filtered rows. Key moments clarify why the subquery runs first, that the derived table is temporary, and that the outer query can filter on subquery columns. The quiz tests understanding of these steps and outcomes. This helps beginners see how subqueries in FROM clauses work step-by-step.

Practice

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

easy

A. To update values in a table

B. To permanently store data in the database

C. To delete rows from a table

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

Solution

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

   Subqueries in the FROM clause act like temporary tables that the main query can use to simplify complex operations.

2. Step 2: Differentiate from other SQL operations

   Unlike DELETE or UPDATE, subqueries in FROM do not modify data but help organize data for selection.

3. Final Answer:

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

4. Quick Check:

   Subquery in FROM = temporary table [OK]

Hint: Subquery in FROM creates a temp table for main query use [OK]

Common Mistakes:

• Thinking subquery stores data permanently
• Confusing subquery with DELETE or UPDATE commands
• Forgetting subquery is temporary, not permanent

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

easy

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

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

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

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

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 misuse WHERE, alias placement, or parentheses.

3. Final Answer:

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

4. Quick Check:

   Subquery in FROM needs parentheses + alias [OK]

Hint: Subquery in FROM needs parentheses and alias [OK]

Common Mistakes:

• Omitting alias for subquery
• Placing subquery in WHERE instead of FROM
• Incorrect alias placement

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

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

medium

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

B. All users with their total order amount

C. Syntax error due to missing alias

D. Empty result because no users have orders

Solution

1. Step 1: Understand the subquery

   The subquery calculates total order amount per user by joining users and orders and grouping by user name.

2. Step 2: Apply the outer WHERE filter

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

3. Final Answer:

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

4. Quick Check:

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

Hint: Subquery calculates totals; outer query filters results [OK]

Common Mistakes:

• Ignoring the WHERE filter on total
• Assuming all users are returned
• Confusing alias usage

4. Identify the error in this query:

SELECT sub.name, sub.total FROM (SELECT name, SUM(amount) AS total FROM users JOIN orders ON users.id = orders.user_id) sub;

medium

A. Missing alias for subquery

B. Incorrect JOIN syntax

C. Missing GROUP BY clause in subquery

D. Using subquery in WHERE clause instead of FROM

Solution

1. Step 1: Analyze the subquery aggregation

   The subquery uses SUM(amount) but does not group by name, which is required when selecting non-aggregated columns.

2. Step 2: Confirm alias and JOIN syntax

   The subquery has an alias 'sub' and JOIN syntax is correct, so these are not errors.

3. Final Answer:

   Missing GROUP BY clause in subquery -> Option C

4. Quick Check:

   Aggregation needs GROUP BY for non-aggregated columns [OK]

Hint: Aggregation needs GROUP BY for other selected columns [OK]

Common Mistakes:

• Forgetting GROUP BY with aggregation
• Confusing alias requirement
• Misreading JOIN syntax

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 user_id, AVG(amount) FROM (SELECT * FROM orders WHERE COUNT(*) > 3) AS sub GROUP BY user_id;

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

C. SELECT user_id, AVG(amount) FROM orders WHERE COUNT(*) > 3 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 FROM orders GROUP BY user_id HAVING COUNT(*) > 3) AS sub; correctly uses a subquery to group orders by user_id, filters users with more than 3 orders using HAVING, then calculates average amount.

3. Final Answer:

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

4. Quick Check:

   Subquery filters users by order count, outer selects average [OK]

Hint: Use HAVING in subquery to filter groups before outer select [OK]

Common Mistakes:

• Using WHERE with aggregation functions
• Placing HAVING outside GROUP BY context
• Not using subquery to filter groups first