Sub Query and Corelated subquery

Sub query: A subquery is a query nested inside another statement such as SELECT, INSERT, UPDATE, or DELETE.

select * from [dbo].[FactInternetSales]

where customerkey in (select CustomerKey from DimCustomer where GeographyKey=37)

First, it executes the subquery to get a list of customerkey where geographykey=37

QL Server substitutes customerkey numbers returned by the subquery in the IN operator and executes the outer query to get the final result set.

Nested Subquery:

A subquery can be nested within another subquery. SQL Server supports up to 32 levels of nesting.

select * from [dbo].[FactInternetSales]

where customerkey in (select CustomerKey from DimCustomer where GeographyKey in (select GeographyKey from DimGeography where City in('Matraville','Newcastle',

'East Brisbane','Darlinghurst')))

First sql server get the demography key for city 'Matraville','Newcastle','EastBrisbane','Darlinghurst'

2nd it get the customer key which is the output of first step

3rd it gives the sale data for the customer key which is the result of 2nd step

SQL Server subquery types:

• In place of an expression
• With IN or NOT IN
• With ANY or ALL
• With EXISTS or NOT EXISTS
• In UPDATE, DELETE, orINSERT statement
• In the FROM clause

In place of expression:

select *,(select max(salary) from employee a where a.dept=e.dept) from employee e

With IN or Not In: Refer above example

SQL Server subquery is used with ANY operator:

For example, the following query finds the employee whose salary are greater than or equal to the average salary of any dept

select * from employee where

salary>= any (select avg(salary) from employee group by dept)

SQL Server subquery is used with ALL operator

The following query finds the employee whose salary is greater than or equal to the average salary returned by the subquery:

Note that even though the subquery returns a NULL value, the EXISTS operator is still evaluated to TRUE.

select * from employee where

salary>= all (select avg(salary) from employee group by dept)

SQL Server subquery is used with EXISTS or NOT EXISTS

The EXISTS operator returns TRUE if the subquery return results; otherwise, it returns FALSE.

The NOT EXISTS negates the EXISTS operator.

Exists: 

select * from DimCustomer c where exists (select * from FactInternetSales f where c.CustomerKey=f.CustomerKey and year(DateFirstPurchase)=2017)

If you use the NOT EXISTS instead of EXISTS, you can find the customers who did not buy any products in 2017.

SQL Server subquery in the FROM clause

The query that you place in the FROM clause must have a table alias.

select * from 

(select GeographyKey,count(*) as cnt from DimCustomer group by GeographyKey) 

cus; 

Correlated sub query

if sub query depends upon outer query for its value then that sub query
is called corelated sub query.

Moreover, a correlated subquery is executed repeatedly, once for each row evaluated by the outer query. The correlated subquery is also known as a repeating subquery.

Example: Refer example of exist operator.