Subquery VS Derived Table


Subquery VS Derived Table

Hi All,
What is the exact difference between subquery and derived table. I read somewhere that if a query is use in:
> SELECT than its a subquery
> FROM than its a Derived table
> WHERE than its a Correlated subquery

Re: Subquery VS Derived Table

The Standard Subquery and a Derived table is that a subquery is used in a select list return data, while a derived table is also used to filter data.
Ie the both Subquery and Dervied Tables are used to filter data.

The difference is that a subquery is used in a WHERE clause and a derived table is used in a FROM clause.

So to summarize:
If it is in a
SELECT list --> Subquery
FROM --> Derived table
WHERE --> Correlated subquery.

Re: Subquery VS Derived Table

In simple terms, a sub query is a query within another query. If the outer part of the query does not have relation with inner part of the quesry, then its called not sub query.

SELECT last_name
FROM employee
WHERE employee_number IN
(SELECT manager_employee_number
FROM department);

If the outer query has reference with inner query (using alias names) then its called correlated sub query.
SELECT last_name
,department_number AS deptno
FROM employee ee
WHERE salary_amount =
(SELECT MAX (salary_amount)
FROM employee em
WHERE ee.department_number
= em.department_number);

NOTE: In a subquery, one table accesses another table using its WHERE clause irrespective whether its a normal subquery or correlated.

But in a derived table, one table accesses another from its FROM clause.
SELECT last_name
,salary_amount (FORMAT ‘$,$$$,$99.99’)
,avgsal (FORMAT ‘$,$$$,$99.99’)
FROM (SELECT AVG (salary_amount)
FROM employee) my_temp (avgsal)
WHERE salary_amount > avgsal

here my_temp is a derived table whih eomplyee table is accessing from its FROM clause.

Re: Subquery VS Derived Table

Okay it means that whenever we ues a query in the FROM clause, we would be referring it as a dervied table because we would be accessing the fields in the outer query from the one in the FROM clause.
In all the other cases it would be either sub query or corelated sub query (depending on the conditions)