Movatterモバイル変換

What is the difference between a derived table and a subquery? Explain it with an example.

Both derived tables and subqueries can look the same and many people may even think they are the same thing, but there are definitely differences. Don’t worry, even if you don’t know what a derived table or a subquery is in SQL, it should be clear by the time you are done reading below.

A subquery is a SELECT statement that is nested within another statement – that’s why it’s called asubquery, because it’s like having a query within another query . Subqueries are usually used in the WHERE clause as a way to filter out certain rows returned in the result set of the outer query.

Let’s say that we have a table called employee with columns employee_name, last_name, employee_salary, and employee_number. And we also have another table called department that has columns called manager_employee_number and department_name.

Using those tables as our sample data, here is what a subquery looks like:

An example of a subquery

select employee_name from employeewhere employee_salary >-- this is a subquery:(select avg(employee_salary)        from employee)

The SQL above will find all employees who have a salary that is above average. That SQL could be written much more simply, but it is just for illustrative purposes to show you what a simple subquery would look like.

Another example of a subquery

SELECT last_nameFROM employeeWHERE employee_number IN-- this is also a subquery:(SELECT manager_employee_numberFROM department)

The SQL above can be used to find the last name of all employees who have managers.

Example of a subquery not used with a where clause

It is possible to have a subquery that is not used in conjunction with a WHERE clause. Here we are using a subquery to select an id and name from the student_details table and then insert that data into the math_study_group table.

INSERT INTO math_study_group(id, name) -- this is a subquery:SELECT id, nameFROM student_details WHERE subject= 'Math'

Derived tables

Aderived table is basically a subquery, except it is always in the FROM clause of a SQL statement. The reason it is called a derivedtable is because it essentially functions as a table as far as the entire query is concerned.

But, remember that a derived table only exists in the query in which it is created. So, derived tables are not actually part of the database schema because they are not real tables.

An example of a derived table will help clarify:

Example of a derived table

select max(age) from (-- this part of the query is a derived table:select age from table) as Age -- must give derived table an alias

In the SQL above, you can see that the derived table is inside the FROM portion of the SQL. Theresults of the “Select age from table” query are considered to be the derived table. And, hopefully it’s fairly obvious to you how the derived table essentially acts as a table from which something else is selected.

You may have noticed the “as Age” text on the bottom of the SQL above. We must include that otherwise we will get an error that says something like “Every derived TABLE must have its own alias”.

Of course, instead of using a derived table in the query above, you can simply write something like “select max(age) from table”, but this example was chosen for it’s simplicity in illustrating what a derived table would look like, certainly not for its real world applicability.

Summary of the difference between derived tables and subqueries

In simplest terms, a summary of the differences between derived tables and subqueries are:

- derived tables are used in the FROM clause- subqueries are used in the WHERE clause, but can also be used to select from one table and insert into another as we showed above