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
Comments