Tuesday, November 22, 2011

Except and Intersect operators in SQL Server

Returns distinct values by comparing the results of two queries. 

EXCEPT returns the distinct data value from the left query (query on left side of the operand) which does not exist in the right query (query on the right side of the operand). INTERSECT returns data value which is common in both queries. In Oracle, the equivalent of the EXCEPT operator is MINUS and INTERSECT is same as INTERSECT. In SQL 2005, using EXCEPT and INTERSECT, one can also simulate the MERGE command in Oracle (see the blog post that is mentioned above).


CREATE TABLE #tmpName1 (name VARCHAR(100))
CREATE TABLE #tmpName2 (name VARCHAR(100))

INSERT INTO #tmpName1 VALUES('Harpreet'),('Sundeep'),('Mithun')

INSERT INTO #tmpName2 VALUES('Harpreet'),('Shashi'),('Nitin')

Now let see the result by running the query using EXCEPT and INTERSECT command.

SELECT Name FROM #tmpName1 EXCEPT SELECT Name FROM #tmpName2

Above query will return following values. Data which exists in the left query but not in the right query.

SELECT Name FROM #tmpName2 EXCEPT SELECT Name FROM #tmpName1

This query will return following values. This time we have reversed the table order.

And this query returns value returned by both the tables.

Things to remember when using these operators:
  • 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 SQL Server 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:
            a). Expressions in parentheses
            b). The INTERSECT operand
            c). 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.

No comments: