A non-technical type can learn SQL

Set Operators - EXCEPT and INTERSECT (Transact-SQL)

  • 4 minutes to read

Scope of application:SQL Server (all supported versions) Azure SQL database Azure SQL managed instance Azure Synapse Analytics Parallel data warehouse

Returns unique rows by comparing two queries.

EXCEPT returns unique lines from the left input query that are not returned by the right input query.

INTERSECT returns unique lines returned by the left and right input queries.

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

  • The number and order of the columns must be the same for all queries.

  • The data types must be compatible.

Transact-SQL syntax conventions

syntax

Arguments

<query_specification> | ( <query_expression> )
A query specification or expression that returns data to be compared to the data from another query specification or expression. The definitions of the columns that are part of an EXCEPT or INTERSECT operation do not have to be identical. But they must be comparable using implicit conversion. If the data types differ, the rules for ranking the data types determine the data type used for comparison.

The result is based on the same rules as combining expressions when the types are the same but differ in precision, decimal places, or length. For more information, see Precision, Decimal Places, and Length (Transact-SQL).

The query specification or query expression cannot have columns of the data type xml, text, ntext, image or do not return any non-binary user-defined CLR type columns because these data types are not comparable.

EXCEPT
Returns all unique values ​​from the query to the left of the EXCEPT operator. These values ​​will be returned as long as the query on the right does not also return these values.

INTERSECT
Returns any unique values ​​returned by the queries on the left and right of the INTERSECT operator.

Hints

The data types of comparable columns are returned by the queries to the left and right of the EXCEPT or INTERSECT operator. These data types can contain character data types with different collations. If so, the required comparison is made according to the rules of the order of precedence. If you cannot perform this conversion, the SQL Server database engine will return an error.

When comparing column values ​​to determine UNIQUE rows, two NULL values ​​are considered identical.

EXCEPT and INTERSECT return the column names of the result set that are identical to the column names returned by the query to the left of the operator.

Column names or aliases in ORDER BY clauses must refer to column names returned by the query on the left.

The nullability of all columns in the result set returned by EXCEPT or INTERSECT is the same as the nullability of the corresponding column returned by the operator's left query.

When EXCEPT or INTERSECT is used with other operators in an expression, it is evaluated in the following order:

  1. Expressions in brackets

  2. The INTERSECT operator

  3. EXCEPT and UNION are evaluated from left to right based on their position in the expression.

You can use EXCEPT or INTERSECT to compare more than two sets of queries. When you do this, the data type conversion is determined by comparing two queries one at a time. This is done according to the previously mentioned rules of expression evaluation.

EXCEPT and INTERSECT cannot be used in distributed partitioned view definitions and query notifications.

EXCEPT and INTERSECT can be used in distributed queries. However, they only run on the local server and are not pushed to the linked server. Therefore, using EXCEPT and INTERSECT in distributed queries can affect performance.

You can use forward cursors and static cursors in the result set when used with an EXCEPT or INTERSECT operation. You can also use a keyset or dynamic cursor in conjunction with an EXCEPT or INTERSECT operation. When you do this, the result set cursor of the operation is converted to a static cursor.

When an EXCEPT operation is displayed using the graphical showplan feature of SQL Server Management Studio, the operation is displayed as Left Anti Semi Join and an INTERSECT operation is displayed as Left Semi Join.

Examples

The following examples show how to use the and operators. The first query returns all the values ​​from the table for comparison with the results with and.

The following query returns all of the unique values ​​returned by the queries on the left and right of the operator.

The following query returns any unique values ​​from the query to the left of the operator that are not also found in the query to the right.

The following query returns any unique values ​​from the query to the left of the operator that are not also found in the query to the right. The tables are the reverse of the previous example.

Examples: Azure Synapse Analytics and Parallel Data Warehouse

The following examples show how to use the and operators. The first query returns all values ​​from the table for comparison with the results with and.

The following query returns all of the unique values ​​returned by the queries on the left and right of the operator.

The following query returns any unique values ​​from the query to the left of the operator that are not also found in the query to the right.