SubQuery and JOIN: A beginner concept

Microsoft define subquery  as "A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. "

Let's look at an example of this subquery and how can we avoid subquery all together by using JOIN functionality.

For this example I am going to use AdventureWork database.

We are going to look at some sales table and show you how you can avoid subquery by using JOIN.

Lets say we want to look at SalesOrderID, SalesOrderDate and Maximum UnitPrice from our sales table
(SalesOrderDetail and SalesOrderHeader ).

So first write down what we want.

--Select SalesOrderID, SalesOrderDate and Maximum UnitPrice from Table SalesOrderDetail and Table SalesOrderHeader 


So let's go ahead and write on query using subquery



SELECT  Ord.SalesOrderID
,Ord.OrderDate
,(
SELECT max(OrdDet.UnitPrice)
FROM AdventureWorks.Sales.SalesOrderDetail OrdDet
WHERE Ord.SalesOrderID = OrdDet.SalesOrderID
) AS MaxUnitPrice
FROM AdventureWorks.Sales.SalesOrderHeader Ord;


Now let's try writing down similar query which will give the same result.


SELECT  Ord.SalesOrderID
,Ord.OrderDate
,max(OrdDet.UnitPrice) AS MaxUnitPrice
FROM AdventureWorks.Sales.SalesOrderHeader Ord
 JOIN AdventureWorks.Sales.SalesOrderDetail OrdDet ON Ord.SalesOrderID = OrdDet.SalesOrderID
GROUP BY Ord.SalesOrderID
,Ord.OrderDate;

If possible try avoiding use of subquery in your T-SQL.

Here is execution plan for the 2 queries.