SQL Server provides several operators that act on sets with different effect on the input sets. Common features that need to know before using set operators:
- The input sets must have the same number of columns and compatible data type – implicit conversion or explicit conversion.
- Input sets are the result of a SELECT statements except of ORDER BY clause.
- Some of set operators can have two conceptual forms: DISTINCT and ALL. DISTINCT eliminates duplicate rows while combining sets. ALL combines and shows all rows, even duplicates.
[info]when working with set operators, if you need the sorted results, you should add ORDER BY at the end of query.[/info]
UNION operator combines multiple input sets into a single result.
As mentioned before, the input sets must have the same number of columns and compatible data type.
Following example use UNION operator to combine all rows from each set. Consider that UNION eliminates duplicates and only returns once.
SELECT City, StateProvinceName FROM Sales.vIndividualCustomer UNION SELECT City, StateProvinceName FROM HumanResources.vEmployee
[info] in T-SQL, UNION DISTINCT is not supported but is the implicit default, this means UNION combines all rows from each input set and then filters out duplicate rows.[/info]
UNION ALL Operator
UNION ALL operates very similar to UNION to combine two or more input sets. Unlike UNION, UNION ALL does not filter out duplicate rows.
UNION ALL Example
SELECT City, StateProvinceName FROM Sales.vIndividualCustomer UNION ALL SELECT City, StateProvinceName FROM HumanResources.vEmployee
As you see, in above example, returned result is 18798 rows whereas in previous example returned result was 304 rows.
[info]as UNION ALL, does not perform any filtering of duplicates, UNION ALL will often run faster than UNION on the same data sets.[/info]
For more information about UNION and UNION ALL operators, see:
EXPECT and INTERSECT
While UNION and UNION ALL return combination of all input sets, you may need to return either those rows in one set but not in the other – or rows that are in both sets.
The INTERSECT operator, added in SQL Server 2005, returns only distinct rows that are present in both input sets.
[info]INTERSECT currently only provides an implicit DISTINCT option, that means only returns distinct rows and eliminate duplicates. [/info]
SELECT City, StateProvinceName FROM Sales.vIndividualCustomer INTERSECT SELECT City, StateProvinceName FROM HumanResources.vEmployee
The T-SQL EXCEPT operator, added in SQL Server 2005, returns distinct rows that only appear in first input set.
[info]similar to INTERSECT, EXCEPT currently only provides implicit DISTINCT option.[/info]
SELECT City, StateProvinceName FROM Sales.vIndividualCustomer EXCEPT SELECT City, StateProvinceName FROM HumanResources.vEmployee
If you reverse the order of input set the results are different.
EXCEPT Example- Reversed Order of Input Sets
SELECT City, StateProvinceName FROM HumanResources.vEmployee EXCEPT SELECT City, StateProvinceName FROM Sales.vIndividualCustomer
For more information about INTERSECT and EXCEPT operator, see:
The APPLY operator provides a mechanism to use a table expression from one set to each row in other set. APPLY is a table operator, not a set operator. APPLY is used in the FROM clause, like a JOIN (for more information about JOINs you can refer to Querying Multiple Tables Using JOINs).
Conceptually, the APPLY operator is similar to a correlated subquery. However, APPLY differs from correlated subqueries.
the general syntax for APPLY is as follows:
FROM AS [CROSS]|[OUTER] APPLY AS ;
APPLY supports two different forms: CROSS APPLY, OUTER APPLY.
CROSS APPLY operator
The CROSS APPLY form includes only those rows with results in both the left table and right table are returned.
[info]remember that the CROSS APPLY does not have the same meaning as CROSS JOIN. While a CROSS JOIN returns all possible combination of left and right table source, CROSS APLLY returns only those values that are found in both table source.[/info]
A CROSS APPLY works similar to an INNER JOIN, and almost all T-SQL statements that include INNER JOIN can be rewritten as the statements using CROSS APPLY.
The following example shows the SELECT statement with an INNER JOIN.
CROSS APPLY; INNER JOIN Example
SELECT o.SalesOrderID, o.orderdate, od.productid, od.unitprice, od.OrderQty FROM Sales.SalesOrderHeader AS o INNER JOIN Sales.SalesOrderDetail AS od ON o.SalesOrderID = od.SalesOrderID;
Here is the same statement rewritten to use CROSS APPLY
CROSS APPLY; INNER JOIN Rewritten Example
SELECT o.SalesOrderID, o.OrderDate, od.productid, od.unitprice, od.OrderQty FROM Sales.SalesOrderHeader AS o CROSS APPLY (SELECT sod.ProductID, sod.UnitPrice, sod.OrderQty FROM Sales.SalesOrderDetail AS sod WHERE o.SalesOrderID = sod.SalesOrderID) AS od
OUTER APPLY Operator
The OUTER APPLY operator includes all rows from the left table and equivalent rows in the right table. Where the right table does not contain equivalent row for the left table, right table columns will have a NULL value.
This makes the OUTER APPLY statement similar to a LEFT OUTER JOIN.
The following example shows the SELECT statement with an LEFT OUTER JOIN.
OUTER APPLY; LEFT OUTER JOIN Example
SELECT DISTINCT v.City AS Vendor_City, c.City as Customer_City FROM Purchasing.vVendorWithAddresses AS v LEFT OUTER JOIN Sales.vIndividualCustomer AS c ON c.City = v.City ORDER BY Vendor_City;
Here is the same statement rewritten to use OUTER APPLY
OUTER APPLY; LEFT OUTER JOIN Rewritten Example
SELECT DISTINCT v.City AS Vendor_City, c.City as Customer_City FROM Purchasing.vVendorWithAddresses AS v OUTER APPLY ( SELECT cu.City FROM Sales.vIndividualCustomer AS cu WHERE cu.City = v.City) AS c ORDER BY Vendor_City;
CROSS APPLY and OUTER APPLY Features
As you read in the previous topics, there are many similarities between CROSS APPLY and INNER JOIN, and OUTER APPLY and OUTER LEFT JOIN.
However, the APPLY operators also enable you to join a table to a TVF (Table-Valued Function) or execute some types of query which is not applicable by using JOIN operators. These types of queries assure you the left table being proceed before processing the right table. The following examples are two types of using APPLY operators.
The following example generate a report to show the three most recent orders for each customer:
OUTER APPLY; Three Most Recent Orders Per Customer Example
SELECT C.CustomerID, TopOrders.SalesOrderID, TopOrders.OrderDate FROM Sales.Customer AS C OUTER APPLY (SELECT TOP (3) SalesOrderID, CAST(OrderDate AS date) AS OrderDate FROM Sales.SalesOrderHeader AS O WHERE O.CustomerID = C.CustomerID ORDER BY orderdate DESC, SalesOrderID DESC) AS TopOrders ORDER BY CustomerID;
The Partial Result:
[info]notice that because OUTER APPLY is used here, customers with no orders appears in the result (with NULL in the SalesOrderID and OrderDate columns). If CROSS APPLY were used, customers with no orders would not appear in the result.[/info]
Consider that a TVF should be used as the right table source for APPLY operator.
The following example uses BusinessEntityID column from the left table as a parameter to the TVF named ufnGetContactInformation. If there are any rows in the left table with no corresponded result of TVF, the rows will not appear in the results:
SELECT E.BusinessEntityID, P.FirstName+' ' +P.LastName , E.JobTitle, P.BusinessEntityType FROM HumanResources.Employee AS E CROSS APPLY dbo.ufnGetContactInformation(E.BusinessEntityID) AS P;
The Partial Result
I hope this post was informative for you, please share it with others if you think it worth to read. Stay tuned to learn more about SQL Server.