Transact SQL allows you to nest queries within queries. The inner query is known as a subquery, and this is used as an input for the outer query. Unlike queries that use constants, the results of a subquery can change as the underlying table changes.
Subqueries can be categorised into two types – Self-contained or Correlated subqueries. In a Self-contained subqueries there is no dependency between the inner and outer query. The subquery just feeds results to the outer query. In contrast, with correlated subqueries there is a relationship or correlation between the subquery and outer query.
Subqueries can be further categorized by if they return a single value or multiple values. Single valued subqueries are known as scalar subqueries, and not surprisingly those that return multiple values are known as multi-valued subqueries. In this short tutorial I will cover explain and demonstrate all of these types of subqueries.
Download Database used in Tutorial
