Sub Queries-

Three basic rules only.

Where you can place sub query?
3 places where you can place your sub query:
SELECT ----- FROM
FROM   ----- WHERE
WHERE  ----->

SELECT ----- FROM
_____________________
(sub query can only return single value & no alias to value returned is required)

Correct:
_________

(select max(val) from abc where ---) Ex output: 10
(select top 1 val from abc where ----) Ex output: abctext

Wrong:
_________
(select val from abc where ----) and result is returning multple rows:

Ex. ouput of above query:
val
___
12
32
45
6
45

Not Required:
_____________

(select max(val) as abccol from abc where -----)

FROM   ----- WHERE
_____________________

(sub query can return multiple rows with multiple columns & alias to column and sub query it self in mandatory)

Correct:
______________
(select -------- From ( select abccol,abccol2,max(abc) as [Col Alias] from abc where ----- ) as table1 where ----------
Ex. output:
abccol1 abccol2 col Alias
_______ _______ _________
row 1    abc    223
row 2    abc    12

 

WHERE  ----->
________________
(sub query can return single and multiple values depends upon operator used (Ex. when using IN operator multiple values are allowed) but sub query is nt allowed to 
return multiple columns & no alias to value returned is required.)

Correct:
__________
(select ------- From ------ Where col in (select abc from abc) and col2 = (select max(abc) from abc)

 

 

Script:

--Rule 1: SELECT ----- FROM
--Task: Getting Last order placed by customer
--Sample 1:
select 
(select top 1 orderid from orders where Orders.CustomerID=cust.CustomerID) [Last Order ID],* 
from Customers cust

--Explanation:
select top 1 orderid from orders where Orders.CustomerID='ANTON'

--Sample 2:
select 
(select SUM(Orders.Freight) [Sum of amount] from orders where Orders.CustomerID=cust.CustomerID) [Sum Amount],* 
from Customers cust

--Explanation:
select SUM(Orders.Freight) from orders where Orders.CustomerID='ALFKI'

--Wrong Queries:
select 
(select * from orders where Orders.CustomerID=cust.CustomerID) [Last Order ID],* 
from Customers cust

select 
(select  Orders.Freight from orders where Orders.CustomerID=cust.CustomerID) [Last Order ID],* 
from Customers cust

--Rule 2: FROM   ----- WHERE

select 'a' ,1 col2
union all select 'b',2
union all select 'c',3
union all select 'd',4
union all select 'e',5

select * from 
(select 'a' col1 ,1  col2
union all select 'b',2
union all select 'c',3
union all select 'd',4
union all select 'e',5) t1

select * from Customers cust inner join 
(select OrderID,OrderDate,CustomerID from [Orders]) t1 on t1.CustomerID=cust.CustomerID

 

select t1.*,cust.* from Customers cust inner join 
(select MAX(OrderID) orderid ,CustomerID from [Orders] group by customerid) t1 on t1.CustomerID=cust.CustomerID

--Rule 3: WHERE  ----->
Select * from orders ord where ord.CustomerID=
(select top 1 CustomerID from Customers)

Select * from orders ord where ord.CustomerID IN 
(select top 5 CustomerID from Customers)

--Wrong:
Select * from orders ord where ord.CustomerID=
(select top 1 customerid from Customers)

Select * from orders ord where ord.CustomerID=
(select  CustomerID from Customers)