Sub Query in Sql

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

insideselect

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]

infrom

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 )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s