T-SQL Cross Apply vs Derived Table vs Subquery
--Query 1: Get the Max items ordered within an Order Details line via Subquery(Select):
SELECT
soh.SalesOrderID
,soh.DueDate
,MaxOrderQtyPerOrderDetailLine = (SELECT MAX(sod.OrderQty) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID)
FROM AdventureWorks2014.Sales.SalesOrderHeader AS soh
order by MaxOrderQtyPerOrderDetailLine desc
--Query 2: Get the Max items ordered within an Order Details line via Derived table:
SELECT
soh.SalesOrderID
,soh.DueDate
,sod.MaxOrderQtyPerOrderDetailLine
FROM AdventureWorks2014.Sales.SalesOrderHeader AS soh
JOIN
(
SELECT
MaxOrderQtyPerOrderDetailLine = MAX(sod.OrderQty),
SalesOrderID
FROM Sales.SalesOrderDetail AS sod
GROUP BY sod.SalesOrderID --Group By
) sod
ON sod.SalesOrderID = soh.SalesOrderID
order by sod.MaxOrderQtyPerOrderDetailLine desc
--Query 3: Get the Max items ordered within an Order Details line via Cross Apply:
SELECT
soh.SalesOrderID
,soh.DueDate
,sod.MaxOrderQtyPerOrderDetailLine
FROM AdventureWorks2014.Sales.SalesOrderHeader AS soh
CROSS APPLY
(
SELECT
MaxOrderQtyPerOrderDetailLine = MAX(sod.OrderQty)
FROM Sales.SalesOrderDetail AS sod
WHERE soh.SalesOrderID = sod.SalesOrderID
) sod
order by sod.MaxOrderQtyPerOrderDetailLine desc
Image #1: Record Level Data
Image #2: Report
Notes:
Subqueries:
Subqueries are queries within queries (nesting queries). There is an outer query and an inner query("the subquery").
A Subquery can be either self-contained or correlated. Self-contained means it has no dependency on the outer query, whereas a
correlated subquery does have a dependency.
A Subquery can be single-valued(scalar), multivalued or table-valued(table result set)(table expressions).
Self-Contained Subqueries:
Self-Contained Scalar Subquery:
A scalar subquery can appear anywhere in the outer query where a single-valued expression can appear (such as WHERE or SELECT)
For a scalar subquery to be valid it must return one value. If a scalar subquery returns no value it will return a NULL.
Self-Contained Multivalued Subquery:
A multivalued subquery is a subquery that returns multiple values as a single column. Some predicates such as the IN predicate, operate on a multivalued subqueries.
ex. Select..WHERE ORDERID IN (SELECT ORDERID..)
As with any other predicate, you can negate the IN predicate with the NOT logical operator.
ex. Select..WHERE ORDERID NOT IN (SELECT ORDERID..)
Correlated Subqueries:
A correlated subquery refer to attributes from the table that appears in the outer query. Thus the subquery is dependent on the outer query
and cannot be invoked by itself. Logical Processing - the subquery is evaluated separately for each outer row looking for a match.
Cross Apply
- Used also with Table-Valued Functions
|
|
|
|
|
|