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
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.
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.
EX: 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. Ex: SELECT last_name ,department_number AS deptno ,salary_amount 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. Ex: SELECT last_name ,salary_amount (FORMAT ‘$,$$$,$99.99’) ,avgsal (FORMAT ‘$,$$$,$99.99’) FROM (SELECT AVG (salary_amount) FROM employee) my_temp (avgsal) ,employee WHERE salary_amount > avgsal ORDER BY 2 DESC;
here my_temp is a derived table whih eomplyee table is accessing from its FROM clause.
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) Right?