Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Table-valued sub query

Table-valued sub query:

Table-valued sub query returns a whole table; multiple columns, multiple rows. This is mainly used with derived tables. Table-valued sub query is also used with aggregate and ranking functions like DENSE_RANK(),RANK(),ROW_NUMBER().



-- Get total order amount in shop by month wise and year wise

-- This sub query is a table-valued sub query

SELECT ROW_NUMBER() OVER (ORDER BY OrderSummery.OrderYear, OrderSummery.OrderMonth) AS [No.]

    , OrderSummery.OrderYear, OrderSummery.OrderMonth

    , OrderSummery.OrderAmountByMonth


(SELECT YEAR(OrderDate) OrderYear

    , MONTH(OrderDate) OrderMonth

    , SUM(Total) OrderAmountByMonth

FROM [Order]

GROUP BY YEAR(OrderDate), MONTH(OrderDate)

) OrderSummery


Correlated Sub queries

Correlated Sub queries:

Sub query depends on the outer query for its values. This means that the sub query is executed repeatedly, once for each row that might be selected by the outer query.


  Like join outer query one or more columns matches with sub query one or more columns, and sub query returns matching records.


 In below example Outer query CategoryId is compared with sub query CategoryId  ifor matching records Category name value  is returned by sub query  and that returned value is used in outer select statement.


SELECT p.ProductName,p.UnitPrice,(SELECT categoryname FROM Categories c WHERE CategoryID=p.CategoryID) AS Category FROM Products AS p





Self contained sub query

Self contained sub query:

Sub query is completely independent and do not require any input from outer query called self contained sub query. Self contained Sub queries can be categorized based on their return type. I  

Self contained scalar sub query: A self contained sub query that returns single value called self contained scalar sub query.  Scalar sub query can appear anywhere in query where an expression resulting in a scalar value expected.

Scalar sub query is valid when it returns single value and also when it returns no value or null value.


Below sub query returns no value, but scalar sub query is valid

SELECT * FROM  Products WHERE CategoryID=(SELECT CategoryID FROM Categories WHERE CategoryName='xyz')

Below Scalar sub query returns single value  

SELECT * FROM  Products WHERE CategoryID=(SELECT CategoryID FROM Categories WHERE CategoryName='Shoes')