为何Union All把相同的记录去除了？

[中关村网名]

Use tempdb;
Go
Create table [dbo].[a] (name varchar null);
Create table [dbo].[b] (name varchar null);
Create table [dbo].[c] (name varchar null);
Insert Into [dbo].[a] values ('a'),('b'),('c');
Insert Into [dbo].[b] values ('a'),('d'),('c');
Insert Into [dbo].[c] values ('a'),('e'),('f');
GO
Select name From dbo.a
except (
	Select name From dbo.a 
	Intersect 
	Select name From dbo.b
	Intersect 
	Select name From dbo.c
)
Union All
Select name From dbo.b
except (
	Select name From dbo.a 
	Intersect 
	Select name From dbo.b
	Intersect 
	Select name From dbo.c
)
Union All
Select name From dbo.c
except (
	Select name From dbo.a 
	Intersect 
	Select name From dbo.b
	Intersect 
	Select name From dbo.c
)

明明应该有6条记录,以上代码返回5条记录，把相同name的c记录去掉了，为何呢？？

[MrYangkang]

不知道为什么，但是用这种可以 with t as ( Select name From dbo.a except ( Select name From dbo.a Intersect Select name From dbo.b Intersect Select name From dbo.c ) ),t1 as ( Select name From dbo.b except ( Select name From dbo.a Intersect Select name From dbo.b Intersect Select name From dbo.c ) ),t2 as ( Select name From dbo.c except ( Select name From dbo.a Intersect Select name From dbo.b Intersect Select name From dbo.c ) ) select * from t union all select * from t1 union all select * from t2

[专注or全面]

那你用union好了

[MrYangkang]

引用 2 楼 x_wy46 的回复:

那你用union好了

不行，我试啦

[daiyueqiang2045]

LZ 应该是把都有的记录A去掉了 1：Select name From dbo.a Intersect Select name From dbo.b Intersect Select name From dbo.c 这个的结果是A记录 2：全部都是except所以都不存在a记录了

[seusoftware]

union会移除重复记录， union all保留重复记录，不会删除重复。

[tcmakebest]

原因很简单，union 和 except、 intersect 的优先级是相同的，所以请使用括号：

Select name From dbo.a
except (
	Select name From dbo.a 
	Intersect 
	Select name From dbo.b
	Intersect 
	Select name From dbo.c
)
Union All (
Select name From dbo.b
except (
	Select name From dbo.a 
	Intersect 
	Select name From dbo.b
	Intersect 
	Select name From dbo.c
) )
Union All (
Select name From dbo.c
except (
	Select name From dbo.a 
	Intersect 
	Select name From dbo.b
	Intersect 
	Select name From dbo.c
))

[發糞塗牆]

从执行计划的层次也就是执行的顺序来说，并不是先except/Intersect再union all的，之所以数据并不是你想要的是因为顺序本来就是你要的，所以运算时得不到你的结果，如果你要这样写，加上括号来保证优先级是对的。

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([Union1037]))
       |--Sort(DISTINCT ORDER BY:([Union1037] ASC))
       |    |--Concatenation
       |         |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([Union1020]))
       |         |    |--Sort(DISTINCT ORDER BY:([Union1020] ASC))
       |         |    |    |--Concatenation
       |         |    |         |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([tempdb].[dbo].[a].[name]))
       |         |    |         |    |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[a].[name] ASC))
       |         |    |         |    |    |--Table Scan(OBJECT:([tempdb].[dbo].[a]))
       |         |    |         |    |--Nested Loops(Left Semi Join, WHERE:([tempdb].[dbo].[a].[name] = [tempdb].[dbo].[c].[name]))
       |         |    |         |         |--Nested Loops(Left Semi Join, WHERE:([tempdb].[dbo].[a].[name] = [tempdb].[dbo].[b].[name]))
       |         |    |         |         |    |--Table Scan(OBJECT:([tempdb].[dbo].[a]), WHERE:([tempdb].[dbo].[a].[name] = [tempdb].[dbo].[a].[name]))
       |         |    |         |         |    |--Table Scan(OBJECT:([tempdb].[dbo].[b]))
       |         |    |         |         |--Table Scan(OBJECT:([tempdb].[dbo].[c]))
       |         |    |         |--Table Scan(OBJECT:([tempdb].[dbo].[b]))
       |         |    |--Nested Loops(Left Semi Join, WHERE:([tempdb].[dbo].[a].[name] = [tempdb].[dbo].[c].[name]))
       |         |         |--Nested Loops(Left Semi Join, WHERE:([tempdb].[dbo].[a].[name] = [tempdb].[dbo].[b].[name]))
       |         |         |    |--Table Scan(OBJECT:([tempdb].[dbo].[a]), WHERE:([Union1020] = [tempdb].[dbo].[a].[name]))
       |         |         |    |--Table Scan(OBJECT:([tempdb].[dbo].[b]))
       |         |         |--Table Scan(OBJECT:([tempdb].[dbo].[c]))
       |         |--Table Scan(OBJECT:([tempdb].[dbo].[c]))
       |--Nested Loops(Left Semi Join, WHERE:([tempdb].[dbo].[a].[name] = [tempdb].[dbo].[c].[name]))
            |--Nested Loops(Left Semi Join, WHERE:([tempdb].[dbo].[a].[name] = [tempdb].[dbo].[b].[name]))
            |    |--Table Scan(OBJECT:([tempdb].[dbo].[a]), WHERE:([Union1037] = [tempdb].[dbo].[a].[name]))
            |    |--Table Scan(OBJECT:([tempdb].[dbo].[b]))
            |--Table Scan(OBJECT:([tempdb].[dbo].[c]))