A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Subqueries are often used in situations where a query depends on the results of another query. SQL Server supports noncorrelated and correlated subqueries.
In a noncorrelated subquery, the inner query is independent and gets evaluated first, then passes results to the outer query. A noncorrelated (independent) subquery can be independently evaluated and relies only on its own SELECT clause for instructions.
In a correlated subquery, the outer query provides values for the dependant inner subquery evaluation. SQL Server passes the subquery results back to the outer query for evaluation. A correlated (dependant) subquery receives values from the outer SELECT statement.
Tip
Subqueries are useful for solving complex data retrieval and modification problems; however, this method is often less efficient than performing a join operation.
Typically, the statements that include...