SQL Derived Tables & Inline Views | Subqueries in FROM Clause

Derived Tables

While working with real-world databases, we often face situations where:

A query becomes too complex to read

Calculations are needed before filtering

Aggregated data must be reused in the same query

In such cases, writing everything in a single SELECT statement can make the SQL difficult to understand and maintain.

This is where derived tables and inline views become useful. They allow us to create temporary result sets inside a query and use them just like a table.

What Are Derived Tables and Inline Views?

A derived table (also called an inline view) is:

A subquery written in the FROM clause

Treated as a temporary table

Available only for the duration of the query

It does not exist physically in the database.

It exists only while the query is executing.

In simple words:

A query creates a table that table is immediately used and then discarded

Why Do We Need Derived Tables?

Derived tables are helpful when:

You want to simplify complex queries

Aggregated data needs further filtering

You want better readability and structure

Calculations must be done before applying conditions

Common real-world examples include:

Finding top performers after grouping

Filtering aggregated sales data

Applying conditions on computed columns

Syntax

SELECT *
FROM (
    SELECT column1, column2
    FROM table_name
) AS alias_name;

Important Rules

The subquery must have an alias

Without an alias, SQL throws an error

The alias acts like a table name

Example: Departmetn wise Average Salary

SELECT *
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg;

Explanation

Inner query calculates average salary per department

Result is treated as a temporary table

Outer query selects data from it

At this stage, the derived table behaves exactly like a normal table.

Using Derived Tables for Filtering Aggregated Data

One major limitation of SQL is that:

You cannot use aggregate functions directly in the WHERE clause.

Derived tables solve this problem.

Example: Departments with Average Salary Above 50,000

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

Why This Works

Aggregation happens first

Filtering happens afterward

Logic becomes clear and readable

Inline Views Vs Normal Subqueries

Although both are subqueries, their roles differ.

Aspect	Subquery	Inline View
Location	WHERE / HAVING	FROM clause
Output usage	Single or multiple values	Table like structure
Alias required	Not always	Always
Best for	Conditions	Data organization

Example: Top Earning per Department

SELECT e.emp_name, e.department_id, e.salary
FROM employees e
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) AS dept_max
ON e.department_id = dept_max.department_id
AND e.salary = dept_max.max_salary;

What This Query Does

Inner query finds highest salary per department

Outer query matches employees with those salaries

Final result shows top earners per department

This pattern is widely used in production systems.

Readability and Maintainability Benefits

Using derived tables:

Breaks large queries into logical steps

Makes debugging easier

Improves code readability

Helps teams understand SQL faster

This is especially important in enterprise-level projects.

Derived Tables vs CTEs

Derived tables are temporary and inline.

CTEs (Common Table Expressions):

Are defined using WITH

Improve readability further

Can be reused within the same query

CTEs are often preferred for very complex logic, but derived tables are still widely used.

Two Minute Drill

Derived tables are subqueries in the FROM clause
Also called inline views
Act like temporary tables
Exist only during query execution
Must have an alias
Used for filtering aggregated or calculated data
Improve readability and structure