EXCEPT and INTERSECT (Transact-SQL)
Returns distinct values by comparing the results of two queries.
EXCEPT returns any distinct values from the left query that are not also found on the right query.
INTERSECT returns any distinct values that are 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.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
{ <query_specification> | ( <query_expression> ) }
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }
Arguments
<query_specification> | ( <query_expression> )
Is a query specification or query expression that returns data to be compared with the data from another query specification or query expression. The definitions of the columns that are part of an EXCEPT or INTERSECT operation do not have to be the same, but they must be comparable through implicit conversion. When data types differ, the type that is used to perform the comparison and return results is determined based on the rules for data type precedence.When the types are the same but differ in precision, scale, or length, the result is determined based on the same rules for combining expressions. For more information, see Precision, Scale, and Length (Transact-SQL).
The query specification or expression cannot return xml, text, ntext, image, or nonbinary CLR user-defined type columns because these data types are not comparable.
- EXCEPT
Returns any distinct values from the query to the left of the EXCEPT operand that are not also returned from the right query.
- INTERSECT
Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.
Remarks
When the data types of comparable columns that are returned by the queries to the left and right of the EXCEPT or INTERSECT operands are character data types with different collations, the required comparison is performed according to the rules of collation precedence. If this conversion cannot be performed, the Microsoft SQL Server 2005 Database Engine returns an error.
When you compare rows for determining distinct values, two NULL values are considered equal.
The column names of the result set that are returned by EXCEPT or INTERSECT 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 that is 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
If EXCEPT or INTERSECT is used to compare more than two sets of queries, data type conversion is determined by comparing two queries at a time, and following the previously mentioned rules of expression evaluation.
EXCEPT and INTERSECT cannot be used in distributed partitioned view definitions, query notifications, or together 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. Therefore, using EXCEPT and INTERSECT in distributed queries may affect performance.
Fast forward-only and static cursors are fully supported in the result set when they are used with an EXCEPT or INTERSECT operation. If a keyset-driven or dynamic cursor is used together with an EXCEPT or INTERSECT operation, the cursor of the result set of the operation is converted to a static cursor.
When an EXCEPT operation is displayed by 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.
Examples
The following examples show using the INTERSECT and EXCEPT operands. The first query returns all values from the Production.Product table for comparison to the results with INTERSECT and EXCEPT.
USE AdventureWorks;
GO
SELECT ProductID
FROM Production.Product ;
--Result: 504 Rows
The following query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.
USE AdventureWorks;
GO
SELECT ProductID
FROM Production.Product
INTERSECT
SELECT ProductID
FROM Production.WorkOrder ;
--Result: 238 Rows (products that have work orders)
The following query returns any distinct values from the query to the left of the EXCEPT operand that are not also found on the right query.
USE AdventureWorks;
GO
SELECT ProductID
FROM Production.Product
EXCEPT
SELECT ProductID
FROM Production.WorkOrder ;
--Result: 266 Rows (products without work orders)
The following query returns any distinct values from the query to the left of the EXCEPT operand that are not also found on the right query. The tables are reversed from the previous example.
USE AdventureWorks;
GO
SELECT ProductID
FROM Production.WorkOrder
EXCEPT
SELECT ProductID
FROM Production.Product ;
--Result: 0 Rows (work orders without products)
Change History
| Release | History |
|---|---|
17 July 2006 |
|
See Also
Other Resources
Performing Semi-Joins with EXCEPT and INTERSECT
Using UNION, EXCEPT and INTERSECT with Other Transact-SQL Statements