Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. There are several ways you can categorize subqueries:

- by the number of results they return

-whether they’re correlated (linked to the outer query)

-or where they’re used within a DML statement.

Files for download

SQL Server allow three types of subqueries:

1. Single Row subquery: Subquery returns only one row

2. Multiple Row subquery: Subquery returns multiple rows

3. Multiple column subquery: Subquery returns multiple columns.

Correlated subquery: subquery is dependent on outer query and can not be executed independently.

Noncorrelated subquery: subquery is not dependent on outer query. Can be executed as independent SQL code.

First example is about noncorrelated subquery. Inner code can be run separately. You can add a subquery to a SELECT clause as a column expression in the SELECT list. The subquery must return a scalar (single) value for each row returned by the outer query.

SELECT ProductName, ProductPrice

FROM AW_Products

WHERE ProductPrice < (SELECT AVG(ProductPrice) AS ProductPrice

FROM AW_Products

WHERE ProductSubcategoryKey = 2)

AND ProductSubcategoryKey = 2

Well in this case we’ve got 26 rows affected out of 43. AVG function returned 1529.64. SQL Server has traditionally shied away from providing native solutions to some of the more common statistical questions, such as calculating a median. According to Wikipedia, median is described as the numerical value separating the higher half of a sample, a population, or a probability distribution, from the lower half. I’m pretty sure we could get more accurate value with another simple noncorrelated subquery:

SELECT ProductName, ProductPrice

FROM AW_Products

WHERE ProductPrice >

(

SELECT x.ProductPrice

FROM

(

SELECT ProductPrice,

Count(1) OVER (partition BY 'A') AS TotalRows,

Row_number() OVER (ORDER BY ProductPrice ASC) AS ProductPrice2

FROM AW_Products p

WHERE ProductSubcategoryKey = 2

) x

WHERE x.ProductPrice2 = (x.TotalRows+1)/2)

AND ProductSubcategoryKey = 2

Now we got median = 1457.99 and 21 values are less than median and 17 are greater than median. 5 values are exactly 1457.99. These values weren’t displayed with > or < operator. I have used (x.TotalRows+1) / 2) formula because the sample of data is an odd number .

Correlated Subqueries cannot be executed independently of the outer query. If the subquery depends on the outer query for its values, then that sub query is called as a correlated subquery.

SELECT

ProductName,

ProductPrice,

(

SELECT ISNULL(SUM(OrderQuantity),0) OrderQuantityTotal

FROM AW_Sales

WHERE ProductKey=AW_Products.ProductKey

) AS OrdersTotal

FROM AW_Products

WHERE ProductSubcategoryKey = 2

One difference in using a join rather than a subquery for this and similar problems is that the join lets you show columns from more than one table in the result.

SELECT p.ProductName,

p.ModelName,

P.ProductPrice,

(

SELECT SUM(OrderQuantity) x

FROM AW_Sales

WHERE ProductKey=p.ProductKey

) AS OrdersTotal

FROM AW_Products p

LEFT JOIN AW_Sales S

ON p.ProductKey = s.ProductKey

WHERE ProductSubcategoryKey = 2

GROUP BY p.ProductName, p.ModelName, P.ProductPrice,p.ProductKey