What is subquery?

Subquery is  just a query inside of a query.

Why Subquery?

• Break down complex logic
• Simplify reading

Subqueries can be replaced by joins. But some times Joins can be complex. Other thing is we are not allowed to put aggregates in WHERE. But we can put it aggreagates inside subquery .

Where to Use?

We can put sub queries in  three main places in query.

• SELECT
• FROM
• WHERE

Using in SELECT

• Access informations from tables  withing select query
• Query must return a single(scalar) value

Creates a dynamic table. Useful for  breaking down queries. Query must be aliased.

Note : I have used Adventureworks 2012 database for following queries 

SELECT so.[SalesOrderID]
,so.OrderDate
,(SELECT COUNT([SalesOrderDetailID])
FROM [Sales].[SalesOrderDetail]sod
WHERE sod.SalesOrderID = so.SalesOrderID ) AS 'OrderLineCount'
FROM [Sales].[SalesOrderHeader] so

Using with FROM

SELECT so.[SalesOrderID]
,so.OrderDate
,X.OrderQty AS 'TotalItems'
FROM [Sales].[SalesOrderHeader] so
INNER JOIN (SELECT
SUM([OrderQty]) AS 'OrderQty'
, [SalesOrderID]
FROM [Sales].[SalesOrderDetail] sod
GROUP BY [SalesOrderID]) AS X
ON so.[SalesOrderID] = X.[SalesOrderID]

With WHERE

Useful for comparing values from other tables

Predicates used with subqueries

IN

• Confirm column value exist in subquery
• Similar to inner join

SELECT [AccountNumber]
FROM [Sales].[Customer] sc
WHERE sc.CustomerID IN ( SELECT DISTINCT [CustomerID] FROM [Sales].[SalesOrderHeader] soh )

EXISTS

• Returns true if subquery returns value
• Used with correlated queries

Correlated subquery

Pass outer query column into subquery

</h4>
SELECT [AccountNumber]
FROM [Sales].[Customer] sc
WHERE EXISTS ( SELECT 1
FROM [Sales].[SalesOrderHeader] soh
WHERE sc.CustomerID = soh.CustomerID )

ALL

• Compares column values to all items returned by subquery
• Subquery must return only one column

ANY or SOME

• Compare column value to any item returned by subquery
• Subquery must return only one column
• ANY and SOME are identical

SELECT [AccountNumber]
FROM [Sales].[Customer] sc
WHERE sc.CustomerID = ANY ( SELECT DISTINCT [CustomerID] FROM [Sales].[SalesOrderHeader] soh )

SELECT [AccountNumber] FROM [Sales].[Customer] sc

WHERE sc.CustomerID = SOME( SELECT DISTINCT [CustomerID] FROM [Sales].[SalesOrderHeader] soh )