How to Use the EXCEPT and INTERSECT Operators in SQL Server

Introduction

The EXCEPT and INTERSECT operators are new operators introduced in SQL Server. They return distinct values by comparing the results of two queries.

EXCEPT and INTERSECT Operators in SQL Server

EXCEPT returns any distinct values from the left query not found on the right query.

INTERSECT returns any distinct values returned by both the query on the left and right sides of the INTERSECT operand.

The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:

• The number and the order of the columns must be the same in all queries.
• The data types must be compatible.

When the data types of comparable columns returned by the queries to the left and right of the EXCEPT or INTERSECT operands are character data types with various collations, the required comparison is performed according to the rules of collation precedence. The SQL Server Database Engine returns an error if this conversion cannot be performed.

When you compare rows to determine distinct values, two NULL values are considered equal. The column names of the result set that EXCEPT or INTERSECT returns are the same names as those returned by the query on the left side of the operand. Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.

The nullability of any column in the result set returned by EXCEPT or INTERSECT is the same as the nullability of the corresponding column returned by the query on the left side of the operand. If EXCEPT or INTERSECT is used together with other operators in an expression, it is evaluated in the context of the following precedence:

• Expressions in parentheses
• The INTERSECT operand
• EXCEPT and UNION evaluated from left to right based on their position in the expression

Suppose EXCEPT or INTERSECT compares more than two sets of queries. In that case, data type conversion is determined by comparing two queries at a time and following the rules of expression evaluation described previously. EXCEPT and INTERSECT cannot be used in distributed partitioned view definitions, query notifications, or with COMPUTE and COMPUTE BY clauses. EXCEPT and INTERSECT may be used in distributed queries but are only executed on the local server and not pushed to the linked server.

Fast forward-only and static cursors are fully supported in the result set when used with an EXCEPT or INTERSECT operation. Therefore, using EXCEPT and INTERSECT in distributed queries may affect performance. Suppose a keyset-driven or dynamic cursor is used with an EXCEPT or INTERSECT operation. In that case, the cursor of the result set of the operation is converted to a static cursor. When an EXCEPT operation is displayed using the Graphical Showplan feature in SQL Server Management Studio, the operation appears as a left anti semi join, and an INTERSECT operation appears as a left semi join.

Getting Started

I have two database tables in my database: Vendor and Advance.

The following is the Vendor table.

Figure 1.

The following is the Advance table.

Figure 2.

This is how you use EXCEPT.

Use Vendor
GO
SELECT Vendor Id
FROM Vendor
EXCEPT
SELECT Vendor Id
FROM advance
GO

The result looks like the following.

Figure 3.

This is how you use INTERSECT.

Use Vendor
GO
SELECT VendorId
FROM Vendor
INTERSECT
SELECT VendorId
FROM advance
GO

The result looks like the following.

Figure 4.

Summary

In this article, you saw how to use the EXCEPT and INTERSECT operators with code examples in SQL Server.