Database Related

Difference Between A Derived Table And A Subquery?

There is quite a lot of confusion over derived tables and subqueries because of their similar structures. Most people believe that they are the same thing but it’s not true – there are definite and concrete differences between the two objects.

Subqueries:

A subquery is essentially a query within a query. It is a SELECT statement that is nested in another statement. Subqueries are written in the WHERE clause since their results are used by the outer query to extract required records.

Here is an example to better illustrate the syntax and functionality of a subquery:

SELECT NameofProduct

FROM Products

WHERE Sales > 1/2 * (

SELECT AVG(Quantity)

FROM Orders );

 

In the example above, the Products table holds information about each product available for a company i.e. Name, Price, Sales, Description. The Orders table holds details of the sales and quantity of each product of the company i.e. quantity sold, sales revenue, total profits. The SQL statements above are example of a typical subquery structure. The subquery starts under the WHERE clause and the result of this subquery completes the condition for the WHERE clause using which the required records are to be determined. When this SQL query is run, it gives us the Product Name of all products that have Sales (Number of Items) more than half of the quantity of product available.

Derived Tables:

A derived table is essentially a temporary table that is created at the time of execution of a query. Though similar in structure to a subquery, the primary difference is that a derived table is always found in the FROM clause of a SQL query. The results of the statements of a derived table create a table from which the records in a query are selected.  One other important thing to note is that derived tables only have a scope within the query i.e. they exist only in the query and are not part of the entire database schema.

Here is an example of a derived table:

            SELECT MAX(Sales)

FROM (

SELECT Sales

FROM Products

) as Sales

 

From the SQL statements above, it can easily be observed that the derived table is found within the FROM clause of the outer SELECT statement. The result of the SELECT Sales FROM Products query is going to return records which will then be considered to be a table within the query. Also, note the “as Sales” in the bottom query statement – this is the allocation of an alias (or name) to the derived table with which it will be referenced within the query. The purpose of the query above is to get the maximum Sales from the Products table.

Leave a Reply