intro
Let’s explore everything you need to know about the SQL derived table mechanism—a way to create virtual tables within the FROM clause.
Sometimes, the data you need is not stored in a single table. Instead, you may need to retrieve it through aggregations, multiple joins, or advanced filtering. The solution? An SQL derived table—a temporary, inline table created within a query for simplified data retrieval.
In this article, you will learn what a derived table is in SQL, how to use it effectively, and when it can be most helpful.
Let’s dive in!
What Is a Derived Table in SQL?
A derived table is an SQL expression that generates a table within a FROM clause of a query. In other words, it is a temporary result set created on the fly during the query execution and used in the FROM clause.
Derived tables support complex operations like joins, filtering, and aggregation without needing to create permanent tables. Once the query finishes its execution, the derived table is discarded as it is not persisted in the database. So, an SQL derived table is essentially a subquery that acts like a virtual table for the duration of the query execution. Learn more in our guide about SQL subqueries and for specific details, refer to the documentation links:
Derived Table SQL Syntax and Characteristics
This is the syntax for creating derived tables in standard SQL:
1
SELECT ...
2
FROM (subquery) [AS] derived_table_name
3
[WHERE ...]
Where:
Notes:
Let's now explore what specific DBMS technologies offer when it comes to derived tables.
MySQL
MySQL provides another way to create a derived table via the JSON_TABLE() function:
1
SELECT ...
2
FROM JSON_TABLE(arg_list) [AS] derived_table_name
3
[WHERE ...]
If you are not familiar with that function, JSON_TABLE() extracts data from a JSON document and returns it as a relational table having the specified columns.
For example, you can use it as below:
1
SELECT employee_id, name, salary
2
FROM JSON_TABLE(
3
'[{"employee_id": 1, "name": "Alice", "salary": 50000},
4
{"employee_id": 2, "name": "Bob", "salary": 60000}]',
5
"$[*]" COLUMNS (
6
employee_id INT PATH "$.employee_id",
7
name VARCHAR(50) PATH "$.name",
8
salary DECIMAL(10, 2) PATH "$.salary"
9
)
10
) AS employee_data
11
WHERE salary > 55000;
The above query populates a derived table with employee data from a JSON string. Then, it retrieves all employees whose salary is greater than 55000.
Notes:
PostgreSQL
There are the aspects to keep in mind when using derived tables in PostgreSQL:
An example of a derived table SQL definition query with column alias names is:
1
SELECT dept_name, avg_salary
2
FROM (
3
SELECT department_id, AVG(salary)
4
FROM employees
5
GROUP BY department_id
6
) AS dept_avg(dept_id, avg_salary)
7
JOIN departments ON departments.id = dept_avg.dept_id
8
WHERE avg_salary > 50000;
Here, department_id and AVG(salary) are renamed as dept_id and avg_salary, respectively.
SQL Server
Here are the key characteristics of derived tables in SQL Server:
Here is an example of using the table value constructor to list rows and columns directly within the derived table SQL Server definition:
1
SELECT employee_id, name, salary
2
FROM (
3
VALUES
4
(1, 'Alice', 50000),
5
(2, 'Bob', 60000),
6
(3, 'Carol', 55000)
7
) AS employees(employee_id, name, salary)
8
WHERE salary > 55000;
Use Cases for an SQL Derived Table
A derived table in SQL can be used in a variety of scenarios. Time to analyze two of the most useful ones!
1. Aggregating Data for Filtering in Outer Queries
In some scenarios, you need to filter records based on aggregate values. Derived tables can simplify this process by enabling you to calculate aggregates inline and use them in the WHERE clause of the main query.
For example, the following query retrieves the employees who earn more than the average salary in their department:
1
SELECT e.id, e.name, e.salary, dept_avg.avg_salary
2
FROM (
3
SELECT department_id,
4
AVG(salary) AS avg_salary
5
FROM employees
6
GROUP BY department_id
7
) dept_avg
8
JOIN employees e ON e.department_id = dept_avg.department_id
9
WHERE e.salary > dept_avg.avg_salary;
2. Simplifying Complex JOINs
When working with multiple tables, an SQL derived table can be used to greatly simplify complex JOIN statements by encapsulating part of the query logic in a virtual table. The resulting query will be easier to read and manage.
For instance, this query retrieves the list of employees along with their department names and the region of their department location, assuming the region of their department is in America:
1
SELECT e.id AS employee_id,
2
e.name AS employee_name,
3
dept_locations.department_name,
4
r.name AS region_name
5
FROM employees e
6
JOIN (
7
SELECT d.id AS department_id,
8
d.name AS department_name,
9
l.region_id AS region_id
10
FROM departments d
11
JOIN locations l ON d.location_id = l.location_id
12
WHERE l.name = 'America'
13
) dept_locations ON e.department_id = dept_locations.department_id
14
JOIN regions r ON dept_locations.region_id = r.region_id;
First, the query creates a derived table called dept_locations that includes department names and region IDs, filtering for locations in America. Then, it joins it with the employees table to retrieve employee information.
While it is longer, some users may find the above query easier to follow than the equivalent one below due to its formatting:
1
SELECT e.id AS employee_id,
2
e.name AS employee_name,
3
d.name AS department_name,
4
r.name AS region_name
5
FROM employees e
6
JOIN departments d ON e.department_id = d.id JOIN locations l ON d.location_id = l.location_id JOIN regions r ON l.region_id = r.region_id
7
WHERE l.name = 'America';
More Readable Queries With DbVisualizer
As you might have noticed, derived tables can make your queries extremely lengthy and difficult to read. For example, consider this complex query:
1
SELECT e.employee_id, e.name, e.salary, dept_avg.avg_salary, dept.name
2
FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) dept_avg JOIN employees e ON e.department_id = dept_avg.department_id JOIN departments dept ON e.department_id = dept.id
3
WHERE e.salary > dept_avg.avg_salary
4
ORDER BY e.salary DESC, dept.name ASC;
The above SQL code is so poorly formatted that it is hard to follow. The solution? Proper indentation to make it more organized and readable. This is where DbVisualizer comes in!
DbVisualizer is a top-rated, fully-featured, powerful database client with extensive support for over 50 databases. Its capabilities also include automatic and configurable SQL formatting.
To format your query, download DbVisualizer, install it, launch it, and paste your query into the SQL Editor:
Right-click on it, select the “Format SQL” option and then “Format Buffer:”
DbVisualizer will then format the query for you, making it easier to understand. We received the following query:
1
SELECT
2
e.employee_id,
3
e.name,
4
e.salary,
5
dept_avg.avg_salary,
6
dept.name
7
FROM
8
( SELECT
9
department_id,
10
AVG(salary) AS avg_salary
11
FROM
12
employees
13
GROUP BY
14
department_id) dept_avg
15
JOIN
16
employees e
17
ON
18
e.department_id = dept_avg.department_id
19
JOIN
20
departments dept
21
ON
22
e.department_id = dept.id
23
WHERE
24
e.salary > dept_avg.avg_salary
25
ORDER BY
26
e.salary DESC,
27
dept.name ASC;
Much more readable, don’t you find? Try DbVisualizer with a free trial to test its large set of powerful features!
Conclusion
In this guide, you learned what the derived table SQL construct is, how it works, and how to use it. Thanks to some real-world scenarios, you also understood when to use derived tables in your queries.
As shown here, DbVisualizer simplifies the management of queries involving derived tables. This powerful database client supports various DBMS technologies and offers advanced features such as query optimization, SQL formatting, and ERD-like schema generation. Try DbVisualizer for free today!
FAQ
Are SQL derived tables supported by all major DBMS technologies?
SQL derived tables are supported by most major DBMS technologies, including MySQL, PostgreSQL, SQL Server, and Oracle. At the same time, there could be some variations in syntax and performance optimizations between DBMSs. So, always refer to the official documentation of your specific DBMS for details.
What is the difference between an SQL derived table and a subquery?
An SQL derived table is a temporary result set used within a query's FROM clause. On the contrary, a subquery is a query nested inside another query. Compared to a derived table, a subquery can appear in various clauses, such as WHERE, FROM, or SELECT. So, a subquery that specifically appears in the FROM clause acts as a derived table.
Can derived tables be stored on disk?
No, derived tables cannot be stored on disk as they are temporary, in-memory result sets created during the execution of a query. They exist only for the duration of the query and are not persisted beyond that. If you need to store the result of a derived table, you should use a regular table, a temporary table, or a materialized view.
Can a derived table use indexes?
Well, it depends. If the derived table is based on an underlying table that has indexes, those indexes can be used in the subquery to create the derived table. Otherwise, if the original or joined tables the derived table originates from do not have indexes, the derived table will not benefit from indexes. In short, the efficiency of the derived table relies on the indexing of the underlying data.
What is the difference between a regular derived table and a lateral derived table in MySQL?
In MySQL, a regular derived table is a subquery in the FROM clause that is evaluated independently of the outer query. That means it cannot reference columns from the outer query. In contrast, a lateral derived table—created by specifying the LATERAL keyword before the subquery—can reference columns from preceding tables in the same FROM clause. This allows it to depend on values from earlier JOINs, making lateral derived tables ideal for complex, dependent subqueries within a JOIN context.
