Except and intersect usages in SQL Server

Except is the result of a set minus B set, Intersect is the intersection of a set and a B set, both return distinct values , and many properties are similar to union.

Or take student as an example

SELECT * from student;

select  * into  student1 from  student; go  insert into  student1 values  ( ' AAA ' , 20,), ( ' BBB ' , 30, ), ( ' CCC ' , 40, ' sh ' ); go  insert into  student1 values  ( ' AA ' , 10, ' Jiangsu ' ); go  select  * from  student1 order  by  1; 

Student1 in the name of AA two lines are repeated;

Student1 three more lines than student, AAA,BBB,CCC.

Select  from Student1exceptselect from student

The result is only AAA,BBB,CCC; no AA .

Select  from Student1intersectselect from student

Only 13 rows in the student table,AA that row is not duplicated

This only focuses on returning results without repeating rows, and except and intersect do not have the all option, which is different from union.

For priority validation of except, intersect, Union:

select  * into  Student2 from  student1; go  delete  from  student2 where  name= ' AAA ' ; go  select  * from  Student2 order  by  1;go 

Student2 's table is less than the Student1 table. AAA Records

Select  from Student2exceptselect from Student1intersectSelect  from Student

If you suspect that Student2 records are less than student1, except has a problem, we swap

Select  from Student1exceptselect from Student2intersectSelect  from Student

Intersect priority is higher than except

The same principle test, theintersect priority is also higher than the Union

The levels of the except and Union are the same, executed sequentially.