Except and Intersect operators in SQL Server 2005

In SQL Server 2005, MSFT introduced two new operators in their T-SQL language: Except and Intersect. We had briefly touched upon these while discussing the MERGE command in Oracle – you can read more on that post here. 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).

Example:

CREATE TABLE TEST1 (COL1 VARCHAR(10))
GO
CREATE TABLE TEST2 (COL1 VARCHAR(10))
GO

INSERT INTO TEST1 VALUES(‘GEORGIA’)
INSERT INTO TEST1 VALUES(‘ALABAMA’)
INSERT INTO TEST1 VALUES(‘FLORIDA’)
INSERT INTO TEST1 VALUES(‘NEWYORK’)
GO

INSERT INTO TEST2 VALUES(‘GEORGIA’)
INSERT INTO TEST2 VALUES(‘TEXAS’)
INSERT INTO TEST2 VALUES(‘NEVADA’)
INSERT INTO TEST2 VALUES(‘NEWYORK’)
GO

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

SELECT COL1 FROM TEST1 EXCEPT SELECT COL1 FROM TEST2
GO

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

ALABAMA
FLORIDA

SELECT COL1 FROM TEST2 EXCEPT SELECT COL1 FROM TEST1
GO

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

NEVADA
TEXAS

SELECT COL1 FROM TEST2 INTERSECT SELECT COL1 FROM TEST1
GO

And this query returns value returned by both the tables.

GEORGIA
NEWYORK

Things to remember when using these operators:

• Number of columns should be the same and columns should be in exact same order in all the queries.
• Data types should be compatible for columns in the queries otherwise data-type conversions will take place.
• xml, text, ntext and image data types are not comparable.
• Column names in ORDER BY clause must refer to column names mentioned in left side query.
• They cannot be used in distributed partitioned view definitions or query notifications.
• If you are looking at the execution plans, an EXCEPT will be denoted by a left anti semi join and an INTERSECT will be denoted by a left semi join.