Sub Query

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.


Paranthesis

• A sub‐query must be enclosed in the parenthesis

comparison operator

• A sub‐query must be put in the right hand of the comparison operator

Order By

• A sub‐query cannot contain an ORDER‐BY clause

Number Of SubQuery's

• A query can contain more than one sub‐query

Types

• Sub Query
• Correlated Sub Query

Results

• 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),
Product VARCHAR(30),
SaleDate SMALLDATETIME,
SalePrice MONEY
)

SELECT SaleDate, Product, SalePrice,
ISNULL(
(
SELECT
SUM(SalePrice)
FROM
SalesHistory sh1
WHERE
Sh1.Product = sh.Product AND
Sh1.SaleID < sh.SaleID
),0) AS RunningTotal
FROM SalesHistory sh
ORDER BY Product, SaleID

____________________________________________________