How to use except and intersect in SQL Server

Skilled use of various usage methods in SQL Server can bring a lot of convenience to queries. Let's introduce except and intersect today. Note This syntax is supported only for SQL Server 2005 and later version numbers.

Except refers to the data that exists in the first collection, but does not exist in the second collection.

Intersect refers to data that exists in two collections.

Tests such as the following:

CREATE TABLE t1 (ID int,mark char (2)) gocreate table T2 (ID int,mark char (2)) Goinsert into T1select 1, ' T1 ' union Allselec T 2, ' T2 ' Union allselect 3, ' T3 ' Union allselect 4, ' T4 ' Goinsert into T2select 2, ' T2 ' Union allselect 3, ' M3 ' union allselect 5, ' M5 ' union Allselect 6, ' T6 ' goselect * from T1exceptselect * from T2goselect * from T1intersectselect * from T2GO--EXCEP T result set is--1t1--3t3--4t4--intersect result set of--2t2

Except and intersect priority levels:

To test the priority between them, execute the following test code:

CREATE TABLE t3 (int id,mark char (2)) Goinsert into T3select 3, ' T3 ' Union allselect 3, ' R3 ' Union allselect 5, ' M5 ' union Allselect 5, ' R5 ' union allselect 7, ' B7 ' union allselect 8, ' B8 ' goselect * from T1exceptselect * from T2intersectselect * fr Om t3--Execution Results--1t1--2t2--3t3--4t4

Why does the result appear as above, please look at the following operation plan:

Originally T2 and T3 first intersect operation, obtained 5 M5 result set, and T1 operation except.

For reprint, please specify the original self-csdn tjvictor column of this article: Http://blog.csdn.net/tjvictor