The SQL subquery feature lets you use the results of one query as part of another query.
A subquery is a query within a query.
A sub-query is a SQL Server statement embedded inside of another SQL Server statement.
Sub-query execution is dependent upon the nesting level of the query. The execution tree goes from inner-most queries to outer-most queries. The higher nested queries can access the results returned by the lower nested queries.
- A sub‐query must be enclosed in the parenthesis
- A sub‐query must be put in the right hand of the comparison operator
- A sub‐query cannot contain an ORDER‐BY clause
- A query can contain more than one sub‐query
- Sub Query
- Correlated Sub Query
- Single Row
- Multipe Row
- Multiple Columns
Correlated Sub Query Sample
A correlated sub-query is dependent upon the outer query. The outer query and the sub-query are related typically through a WHERE statement located in the sub-query. The way a correlated sub-query works is when a reference to the outer query is found in the sub-query, the outer query will be executed and the results returned to the sub-query. The sub-query is executed for every row that is selected by the outer query.
CREATE TABLE SalesHistory
SaleID INT IDENTITY(1,1),
SELECT SaleDate, Product, SalePrice,
Sh1.Product = sh.Product AND
Sh1.SaleID < sh.SaleID
),0) AS RunningTotal
FROM SalesHistory sh
ORDER BY Product, SaleID