Interaction Between Sets

The estimated reading time for this post is 7 minutes

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.

when working with set operators, if you need the sorted results, you should add ORDER BY at the end of query.

UNION Operator

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.

UNION Operator

UNION Operator

Following example use UNION operator to combine all rows from each set. Consider that UNION eliminates duplicates and only returns once.

UNION Example

SELECT City, StateProvinceName FROM Sales.vIndividualCustomer
UNION
SELECT City, StateProvinceName FROM HumanResources.vEmployee

The Results:

UNION Example Result Set

UNION Example Result Set

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.

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

The Results:

UNION ALL Example Result Set

UNION ALL Example Result Set

As you see, in above example, returned result is 18798 rows whereas in previous example returned result was 304 rows.

as UNION ALL, does not perform any filtering of duplicates, UNION ALL will often run faster than UNION on the same data sets.

For more information about UNION and UNION ALL operators, see:

MSDN – Set Operators – UNION (Transact-SQL)

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.

INTERSECT Operator

The INTERSECT operator, added in SQL Server 2005, returns only distinct rows that are present in both input sets.

"<yoastmark

INTERSECT currently only provides an implicit DISTINCT option, that means only returns distinct rows and eliminate duplicates.

INTERSECT Example:

SELECT City, StateProvinceName FROM Sales.vIndividualCustomer
INTERSECT
SELECT City, StateProvinceName FROM HumanResources.vEmployee

The result:

"<yoastmark

EXCEPT Operator

The T-SQL EXCEPT operator, added in SQL Server 2005, returns distinct rows that only appear in first input set.

EXPECT Operator

EXCEPT Operator

similar to INTERSECT, EXCEPT currently only provides implicit DISTINCT option.

EXPECT Example:

SELECT City, StateProvinceName FROM Sales.vIndividualCustomer
EXCEPT
SELECT City, StateProvinceName FROM HumanResources.vEmployee

The Result:

EXPECT Example Result Set

EXPECT Example Result Set

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

The result:

EXCEPT Example- Reversed Order of Input Sets Result Set
image-3267

EXCEPT Example- Reversed Order of Input Sets Result Set

For more information about INTERSECT and EXCEPT operator, see:

MSDN – Set Operators – EXCEPT and INTERSECT (Transact-SQL)

APPLY Operator

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:

SELECT <column_list>
FROM <left_table_source> AS <alias>
       [CROSS]|[OUTER] APPLY     
       <right_table_source> AS <alias>;

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.

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.

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;

The result:

CROSS APPLY; INNER JOIN Example Result Set

CROSS APPLY; INNER JOIN Example Result Set

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

The result:

CROSS APPLY Example Result Set

CROSS APPLY Example Result Set

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;

The Result:

OUTER APPLY; LEFT OUTER JOIN Example Result Set

OUTER APPLY; LEFT OUTER JOIN Example Result Set

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;

The Result:

OUTER APPLY Example Result Set

OUTER APPLY Example Result Set

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:

OUTER APPLY Example Result Set

OUTER APPLY Example Result Set

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.

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

CROSS APPLY Example Result Set

CROSS APPLY Example Result Set

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.

 

Hamide B. Abshuri

Hamide B. Abshuri is SQL Server Database Developer and Administrator with extensive of working experience. She started work with SQL Server since year 2015. She is currently Microsoft Certified Professional and Microsoft Certified Solutions Associate: SQL Server 2016 Database Developer.

More Posts - Website