Derived Table, SubQuery Correlated SubQuery in SQL Server

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.

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

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.