Subqueries and nested queries in DBMS Theory - Step-by-Step Execution

Concept Flow - Subqueries and nested queries

Start Outer Query

↓

Detect Subquery

↓

Execute Subquery

↓

Return Subquery Result

↓

Use Result in Outer Query

↓

Complete Outer Query Execution

The outer query detects a subquery, runs it first, then uses its result to complete the main query.

Execution Sample

DBMS Theory

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.

Analysis Table

Step	Action	Query Part	Result/Value
1	Start outer query	SELECT name FROM employees WHERE department_id = (...)	Waiting for subquery result
2	Detect subquery	SELECT id FROM departments WHERE name = 'Sales'	Subquery identified
3	Execute subquery	SELECT id FROM departments WHERE name = 'Sales'	Returns id = 3
4	Use subquery result	WHERE department_id = 3	Filter employees with department_id = 3
5	Execute outer query	SELECT name FROM employees WHERE department_id = 3	Returns employee names in Sales
6	End	Query complete	Final list of employee names in Sales

💡 Subquery returns department id 3, outer query uses it to filter employees, then finishes.

State Tracker

Variable	Start	After Step 3	After Step 5	Final
subquery_result	None	3	3	3
outer_query_filter	None	None	department_id = 3	department_id = 3
final_output	None	None	List of employee names	List of employee names

Key Insights - 3 Insights

Why does the subquery run before the outer query filters employees?

Because the outer query needs the subquery's result to know which department_id to filter by, as shown in execution_table step 3 and 4.

Can the subquery return multiple values here?

No, because the outer query uses '=' which expects a single value; if multiple values were returned, it would cause an error or need 'IN' instead.

What happens if the subquery returns no rows?

The outer query's filter becomes department_id = NULL, which matches no employees, so the final output is empty.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what value does the subquery return?

A department_id

B Sales

C 3

D NULL

💡 Hint

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

At which step does the outer query apply the subquery result to filter employees?

A Step 4

B Step 1

C Step 2

D Step 6

💡 Hint

Look for when 'Use subquery result' happens in the execution_table.

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

A List of all employees

B Empty list

C Error message

D List of departments

💡 Hint

Refer to key_moments explanation about subquery returning no rows and its effect on final_output.

Concept Snapshot

Subqueries run inside outer queries.
The subquery executes first and returns a value.
Outer query uses this value to filter or compare.
Subqueries can return single or multiple values.
Use '=' for single value, 'IN' for multiple.
If subquery returns no rows, outer query may return empty result.

Full Transcript

Subqueries and nested queries work by running the inner query first to get a result. This result is then used by the outer query to filter or select data. For example, to find employees in the Sales department, the subquery finds the Sales department's id. Then the outer query uses this id to find matching employees. The execution steps show the subquery running first, returning id 3, then the outer query filtering employees with department_id 3. Variables track the subquery result and how it is used. Common confusions include why the subquery runs first, what happens if it returns multiple or no values. Quizzes test understanding of these steps and results. Remember, subqueries must return appropriate values for the outer query to work correctly.

Practice

1. What is a subquery in a database query?

easy

A. A query nested inside another query

B. A query that updates data

C. A query that deletes tables

D. A query that creates indexes

Solution

1. Step 1: Understand the definition of subquery

   A subquery is a query written inside another query to help filter or calculate data.

2. Step 2: Identify the correct description

   Among the options, only "A query nested inside another query" matches the definition of a subquery.

3. Final Answer:

   A query nested inside another query -> Option A

4. Quick Check:

   Subquery = Nested query [OK]

Hint: Subquery means query inside another query [OK]

Common Mistakes:

• Confusing subquery with update or delete operations
• Thinking subquery creates indexes
• Mixing subquery with table creation

2. Which of the following is the correct syntax to use a subquery in the WHERE clause?

easy

A. SELECT * FROM table WHERE column = (SELECT value FROM other_table);

B. SELECT * FROM table WHERE SELECT value FROM other_table = column;

C. SELECT * FROM table WHERE column IN SELECT value FROM other_table;

D. SELECT * FROM table WHERE column = SELECT value FROM other_table;

Solution

1. Step 1: Review correct subquery syntax in WHERE clause

   The subquery must be enclosed in parentheses and used as a value to compare with a column.

2. Step 2: Check each option

   SELECT * FROM table WHERE column = (SELECT value FROM other_table); correctly uses parentheses around the subquery. Options A, B, and D have syntax errors or missing parentheses.

3. Final Answer:

   SELECT * FROM table WHERE column = (SELECT value FROM other_table); -> Option A

4. Quick Check:

   Subquery in WHERE uses parentheses [OK]

Hint: Subquery in WHERE must be inside parentheses [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Placing subquery before column
• Using IN without parentheses

3. Consider the tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What does this query return?

SELECT name FROM Employees WHERE dept_id = (SELECT dept_id FROM Departments WHERE dept_name = 'Sales');

medium

A. Names of employees who do not work in Sales

B. Names of all employees regardless of department

C. Names of employees who work in the Sales department

D. Department names of all employees

Solution

1. Step 1: Understand the subquery

   The subquery finds the dept_id for the department named 'Sales'.

2. Step 2: Apply the subquery result in the main query

   The main query selects employee names whose dept_id matches the Sales dept_id.

3. Final Answer:

   Names of employees who work in the Sales department -> Option C

4. Quick Check:

   Subquery filters Sales dept_id, main query filters employees [OK]

Hint: Subquery finds Sales dept_id, main query filters employees [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing employee names with department names
• Ignoring subquery filtering

4. Identify the error in this query:

SELECT name FROM Employees WHERE dept_id = SELECT dept_id FROM Departments WHERE dept_name = 'HR';

medium

A. Wrong table name used

B. Missing parentheses around the subquery

C. Using = instead of IN

D. Missing WHERE clause in subquery

Solution

1. Step 1: Check subquery syntax

   The subquery must be enclosed in parentheses when used in WHERE clause comparisons.

2. Step 2: Identify the error in the query

   The query misses parentheses around the subquery, causing a syntax error.

3. Final Answer:

   Missing parentheses around the subquery -> Option B

4. Quick Check:

   Subquery needs parentheses [OK]

Hint: Always put subqueries in parentheses [OK]

Common Mistakes:

• Omitting parentheses
• Confusing = with IN unnecessarily
• Incorrect table names

5. You want to find all employees who earn more than the average salary in their department. Which query correctly uses a subquery to achieve this?

hard

A. SELECT emp_name FROM Employees WHERE salary > (SELECT MAX(salary) FROM Employees);

B. SELECT emp_name FROM Employees WHERE salary > AVG(salary);

C. SELECT emp_name FROM Employees WHERE salary > (SELECT salary FROM Employees);

D. SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id);

Solution

1. Step 1: Understand the requirement

   We need to compare each employee's salary to the average salary of their own department.

2. Step 2: Analyze each option

   SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id); uses a correlated subquery that calculates the average salary for the employee's department. Options A, B, and C do not correctly calculate the average per department or use wrong aggregates.

3. Final Answer:

   SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id); -> Option D

4. Quick Check:

   Correlated subquery compares salary to dept average [OK]

Hint: Use correlated subquery with dept_id to get average salary [OK]

Common Mistakes:

• Using global average instead of per department
• Using MAX instead of AVG
• Not correlating subquery with outer query