The difference between subqueries and derived tables in SQL
Posted in Databases on Sep 26, 2005
Many people are confused by SQL terminology, particularly by subqueries and derived tables, and use the terms incorrectly. In fact the official terminology is defined very specifically. It is a good idea to use the right language. Not only does it foster clear understanding, but among people who know the difference, it helps create a good impression of competence and attention to detail.
The official standard defines a derived table as follows:1
A table has an ordered collection of one or more columns and an unordered collection of zero or more rows. Each column has a name and a data type. Each row has, for each column, exactly one value in the data type of that column. SQL-data consists entirely of table variables, called base tables. An operation that references zero or more base tables and returns a table is called a query. The result of a query is called a derived table.
Here is the actual production from the SQL grammar:
<derived table> ::= <table subquery>
This makes it clear that “derived table” is a much more general term than is commonly used by many, especially those familiar with Microsoft SQL Server. Those folks typically mean a subquery in the FROM clause. The real meaning is “the result of a table subquery.”
There are actually three types of subquery: scalar, row, and table. Here is the subquery definition:
Specify a scalar value, a row, or a table derived from a
Format<scalar subquery> ::= <subquery> <row subquery> ::= <subquery> <table subquery> ::= <subquery> <subquery> ::= <left paren> <query expression> <right paren>
There are other common terms for various types of subqueries. Most people use different terms depending on how subqueries are used. For example, a subquery with outer references is often called a correlated subquery. Here is an example:
select emp.Name, emp.Dept, emp.Salary from employees as emp where Salary > ( select avg(Salary) from employees as inn where inn.Dept = emp.Dept);
Notice the distinction between a derived table and a base table. A base table is the actual named database table, whereas a derived table is the result of any table subquery. Some people also use the term anonymous view to denote a derived table, especially one in the FROM clause.
When someone from the Microsoft SQL Server world speaks of derived tables, it is likely s/he is speaking of a subquery in the FROM clause. Just remember, a derived table is defined much more broadly, and this is a specific use of the term.