SQL中的UNION、INTERSECT和EXCEPT

1.创建表结构及资料

CREATE TABLE STU_A(
	[USER_NO] [varchar](30) NOT NULL,
	[USER_NAME] [nvarchar](30) NULL,
) 


INSERT INTO STU_A VALUES ('A','张1')
INSERT INTO STU_A VALUES ('A','张1')
INSERT INTO STU_A VALUES ('B','张2')
INSERT INTO STU_A VALUES ('B','张2')
INSERT INTO STU_A VALUES ('C','张3')
INSERT INTO STU_A VALUES ('D','张4')

CREATE TABLE STU_B(
	[USER_NO] [varchar](30) NOT NULL,
	[USER_NAME] [nvarchar](30) NULL,
) 
INSERT INTO STU_B VALUES ('A','张1')
INSERT INTO STU_B VALUES ('A','张1')
INSERT INTO STU_B VALUES ('B','张2')
INSERT INTO STU_B VALUES ('C','张3')
INSERT INTO STU_B VALUES ('F','张5')

2.实践操作
1.UNION：并集
类似or
UNION和UNION ALL是差异：UNION ALL在执行后，不会把相同的结果合并。而UNION会把相同的结果只显示成一行。

(SELECT  * FROM STU_A) UNION (SELECT  * FROM STU_B)

2.INTERSECT:交集
类似And

(SELECT  * FROM STU_A) intersect (SELECT  * FROM STU_B)

3.EXCEPT:差集

(SELECT  * FROM STU_A) EXCEPT (SELECT  * FROM STU_B)