T-SQL New features in SQL Server 2005 - Part 5

In continuation to the series that I have on new features in SQL Server 2005, today I will be discussing about two new operators- Intersect and Except. I believe, most of you know that Oracle already has these operators, called Intersect and Minus. It was good to learn that now these are supported in SQL Server as well. Before going any further, lets discuss what these operators help achieve. To give you a brief idea - both these operators combine the result of two select statements having same number of columns and outputs result based on the operator used.

Intersect:
As mentioned above, it combines the result of two select statements and gets the distinct rows that exists in both the queries. To make it more clear, lets take an example. Suppose, we have a table that contains the details about Products and another table that contains the details about the Orders. Now, we want to get all the product IDs for which we have at least one order. Following graphics shows the structure and data of Products and Orders tables:


Products:


Orders:


Now, we wish to get all the Product IDs which have at least one order associated with it. Using the Intersect operator, we can get the desired output:

SELECT ProductID FROM Production.Product
INTERSECT
SELECT ProductID FROM Sales.SalesOrderDetail

This lists all the distinct products that are associated to any Order. Its also worth mentioning here, that whatever can be done using Intersect and Except, can be done achieved using Exists as well as shown below:

SELECT DISTINCT ProductID FROM Production.Product P
WHERE EXISTS
(SELECT ProductID FROM Sales.SalesOrderDetail S WHERE S.ProductID = P.ProductID)

Note that the output of both the queries are identical. However, we are required to place Distinct in the query using Exists, so as to remove the duplicate rows. Intersect, on the other hand, handles this automatically and provides a more straight forward way to get the desired result set.

Except:
Contrary to Intersect, Except combines output from both the supplied queries and give the result as the records which are present in result set of first query and not in that of the second query. Lets take an example. Suppose we wish to get the list of slow moving products from the Products table shown above. In order to get such a list, we need to check for such products which exists, but have no orders associated with them. In essence, we are reversing the example discussed above:

SELECT ProductID FROM Production.Product
EXCEPT
SELECT ProductID FROM Sales.SalesOrderDetail

Here, we will get a list of all those items which do not have any orders associated with them. As mentioned above, this can be achieved using Exists as well.

SELECT DISTINCT ProductID FROM Production.Product P
WHERE NOT EXISTS
(SELECT ProductID FROM Sales.SalesOrderDetail S WHERE S.ProductID = P.ProductID)

Looking at the queries above, it is clear that the Intersect and Except provides a neat and straight forward mechanism to get the records as discussed in our examples.

On a final note, we do have some limitations using the Intersect and Except operators. You can use Order By only on the overall result set whereas Group By and Having can only be used with the queries involved and not with the result set.