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