A lot of people are confused that Derived tables are subquery, although its looking quite similar but there is difference between derived table and subquery.
SubQuery:It’s a normal T-SQL that is nested inside another query, or you can say a subquery is a SELECT statement that is nested within another statement.
Subqueries are generally used to fill one of few needs.
- To break a query into a series of logical steps.
- To provide the listing to be target of where clause together with [IN, EXIST, ANY, ALL]
- To Provide lookup driven by each individual record in the parent query.
-- Selecting Email address of employees who have manager.
WHERE EmpID IN
-- subquery Example
Correlated subquery: This is also known as synchronized subquery. Correlated Subquery is a subquery that uses values from the outer query. In this case the inner query has to be executed for every row of outer query. A Correlated Subquery should refer its parent(main Query) Table in it.
Example of Nth Max salary of an employee table
FROM Employee E1
WHERE N-1 = (SELECT COUNT(*)
FROM Employee E2
WHERE E1.salary <E2.Salary)
Difference between Correlated Vs Nested-SubQueries.
1. Looping:Correlated sub-query loop under main-query; whereas nested not; therefore correlated subquery executes on each iteration of main query. Whereas in case of Nested-query; subquery executes first then outer query executes next. Hence, the maximum no. of executes are NXM for correlated subquery and N+M for subquery.
2. Processing Dependency: In the case of co-related subquery, inner query depends on outer query for processing whereas in normal subquery, Outer query depends on inner query.
3.Performance:Using Correlated subquery performance decreases, since, it performs NXM iterations instead of N+M iterations as in Correlated Subquery Execution.
There are multiple result type of a subquery like scalar, row, and table.
Derived tables : The derived tables are similar to subquery but it’s subquery in the FROM clause. Derived table also called Anonymous view.
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.