Except and intersect usage in SQL Server

Skillful Use of various SQL server usage will bring a lot of convenience to the query. Let's introduce T and intersect today. Note that this syntax is only supported in SQL Server 2005 and later versions.

Exist T refers to the data that exists in the first set but does not exist in the second set.

Intersect refers to the data that exists in both sets.

The test is as follows:

Create Table T1 (ID int, Mark char (2) <br/> go <br/> Create Table T2 (ID int, Mark char (2 )) <br/> go <br/> insert into t1 <br/> select 1, 't1' Union all <br/> select 2, 't2 'Union all <br/> select 3, 't3 'Union all <br/> select 4, 't4 '<br/> go <br/> insert into t2 <br/> select 2, 't2' Union all <br/> select 3, 'm3' Union all <br/> select 5, 'm5' Union all <br/> select 6, 't6 '<br/> go <br/> select * from t1 <br/> explain T <br/> select * From t2 <br/> go <br/> select * from t1 <br/> intersect <br/> select * From t2 <br/> go </P> <p> -- distinct T result set is <br/> -- 1T1 <br/> -- 3T3 <br/> -- 4t4 </P> <p> -- the Intersect result set is <br/> -- 2T2

 

 

Priority of except T and intersect:

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

Create Table T3 (int id, Mark char (2) <br/> go <br/> insert into T3 <br/> select 3, 't3 'Union all <br/> select 3, 'r3 'Union all <br/> select 5, 'm5' Union all <br/> select 5, 'r5 'Union all <br/> select 7, 'b7 'Union all <br/> Select 8, 'b8' <br/> go <br/> select * from t1 <br/> Except T <br/> select * From t2 <br/> intersect <br/> select * from T3 </P> <p> -- running result <br/> -- 1T1 <br/> -- 2T2 <br/> -- 3T3 <br/> -- 4t4 <br/>

 

Why is the above result displayed? Please refer to the following execution plan:

 

The Intersect operations of T2 and T3 are performed first to obtain the 5 M5 result set, and then the TTS operation is performed with T1.

 

If you need to reprint, please indicate the original from csdn tjvictor column: http://blog.csdn.net/tjvictor