Nested Subqueries and Derived Tables in SQL

Table of Contents

1. Introduction
2. What is a Nested Subquery?
3. What is a Derived Table?
4. Key Differences Between Nested Subqueries and Derived Tables
5. When to Use Nested Subqueries
6. When to Use Derived Tables
7. Syntax of Nested Subqueries
8. Syntax of Derived Tables (Subquery in FROM)
9. Scalar vs Table Subqueries
10. Nested Subqueries in SELECT
11. Nested Subqueries in WHERE
12. Nested Subqueries in HAVING
13. Derived Tables in FROM Clause
14. Using Aliases in Derived Tables
15. Aggregation in Derived Tables
16. Derived Table vs CTE
17. Performance Implications
18. Common Mistakes to Avoid
19. Best Practices
20. Summary and What’s Next

1. Introduction

When working with complex datasets, we often need intermediate results within our SQL queries. Nested subqueries and derived tables allow us to write modular, readable, and efficient queries by layering logic within the same statement.

2. What is a Nested Subquery?

A nested subquery is a query inside another query. It can appear in SELECT, FROM, WHERE, or HAVING clauses, and it allows for step-by-step logical evaluations.

3. What is a Derived Table?

A derived table is a subquery used in the FROM clause, and it acts like a temporary table that you can immediately query from, join with, or filter further.

4. Key Differences Between Nested Subqueries and Derived Tables

5. When to Use Nested Subqueries

Use when:

• Filtering based on aggregates
• Selecting a single value (scalar subquery)
• Comparing a value from the outer query against a result from an inner one

6. When to Use Derived Tables

Use when:

• You need to join with intermediate results
• You want to reuse aggregated or transformed data
• You want to improve query structure and readability

7. Syntax of Nested Subqueries

Example:

sqlCopyEditSELECT name
FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees
);

This returns employees earning above the average salary.

8. Syntax of Derived Tables (Subquery in FROM)

Example:

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

This returns departments with above-average salaries.

9. Scalar vs Table Subqueries

• Scalar Subquery: Returns one value (used in SELECT, WHERE)
• Table Subquery (Derived Table): Returns multiple rows and columns (used in FROM)

10. Nested Subqueries in SELECT

sqlCopyEditSELECT name,
  (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

Shows each employee with the overall average salary.

11. Nested Subqueries in WHERE

sqlCopyEditSELECT name
FROM customers
WHERE id IN (
  SELECT customer_id FROM orders WHERE total > 500
);

Filters customers based on matching order records.

12. Nested Subqueries in HAVING

sqlCopyEditSELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > (
  SELECT AVG(salary) FROM employees
);

Filters departments with above-company-average salaries.

13. Derived Tables in FROM Clause

sqlCopyEditSELECT t.category, MAX(t.sales) AS top_sale
FROM (
  SELECT category, SUM(amount) AS sales
  FROM orders
  GROUP BY category
) AS t
GROUP BY t.category;

You can use aggregation, filtering, and joins with derived tables.

14. Using Aliases in Derived Tables

Mandatory: Every derived table must have an alias.

sqlCopyEditFROM (SELECT …) AS alias_name

Without an alias, the query will throw an error.

15. Aggregation in Derived Tables

You can create derived tables to group and summarize data:

sqlCopyEditSELECT d.dept, COUNT(*) AS num_employees
FROM (
  SELECT department AS dept
  FROM employees
  WHERE status = 'active'
) AS d
GROUP BY d.dept;

16. Derived Table vs CTE

17. Performance Implications

• Nested subqueries can be slow if not optimized (especially correlated ones)
• Derived tables are often more efficient and readable
• Use indexes wherever possible in subqueries
• Consider query planner hints for large subqueries in production systems

18. Common Mistakes to Avoid

19. Best Practices

• Use derived tables for modular and clean queries
• Alias all subqueries clearly
• Avoid deeply nested subqueries unless necessary
• Use scalar subqueries sparingly on large datasets
• If complexity grows, refactor into CTEs

20. Summary and What’s Next

Nested subqueries and derived tables are invaluable for creating layered, modular queries. Use nested subqueries when filtering or computing one-off values. Use derived tables when creating reusable or joinable sets of intermediate data. Mastering both helps you handle complex SQL logic effectively.