SQL语句UNION和union all的用法

主要说UNION ALL

功能:

将两个流式数据合并
注意:
		两个流式数据的字段必须完全一致，包括字段类型和字段顺序。

union和union all的异同点

UNION ALL允许重复值，UNION不允许重复值。

在实时计算Flink版系统中，UNION相当于UNION ALL+Distinct，运行效率低，通常不推荐使用UNION。

测试数据

1.创建示例表(SQLSERVER)

创建test_u1

CREATE TABLE test_u1(
	  --主键自增:identity(起始值,增量)
      id int identity (1,1) primary key
	  ,name nvarchar(100) null
	  ,student_id nvarchar(100) null
	  ,scholor nvarchar(100) null
	  ,salary int
)

insert into test_u1(name,student_id,scholor,salary) values('boyunv01','20211113','primary','100');
insert into test_u1(name,student_id,scholor,salary) values('boyunv02','20211114','junior','200');
--insert into test_u1(name,student_id,scholor,salary) values('boyunv03','20211115','senior','500');
--insert into test_u1(name,student_id,scholor,salary) values('boyunv04','20211116','college','9000');
--insert into test_u1(name,student_id,scholor,salary) values('boyunv05','20211117','master','30000');
--insert into test_u1(name,student_id,scholor,salary) values('boyunv06','20211118','doctor','60000');

select  * from  test_u1  

创建test_u2和insert语句

CREATE TABLE test_u2(
	  --主键自增:identity(起始值,增量)
      id int identity (1,1) primary key
	  ,name nvarchar(100) null
	  ,student_id nvarchar(100) null
	  ,scholor nvarchar(100) null
	  ,salary int
)
insert into test_u2(name,student_id,scholor,salary) values('boyunv01','20211113','primary','100');
insert into test_u2(name,student_id,scholor,salary) values('boyunv02','20211114','junior','200');
insert into test_u2(name,student_id,scholor,salary) values('boyunv03','20211115','senior','500');
insert into test_u2(name,student_id,scholor,salary) values('boyunv04','20211116','college','9000');
--insert into test_u2(name,student_id,scholor,salary) values('boyunv05','20211117','master','30000');
--insert into test_u2(name,student_id,scholor,salary) values('boyunv06','20211118','doctor','60000');

select  * from  test_u2

创建test_u3和insert语句

CREATE TABLE test_u3(
	  --主键自增:identity(起始值,增量)
      id int identity (1,1) primary key
	  ,name nvarchar(100) null
	  ,student_id nvarchar(100) null
	  ,scholor nvarchar(100) null
	  ,salary int
)

insert into test_u3(name,student_id,scholor,salary) values('boyunv01','20211113','primary','100');
insert into test_u3(name,student_id,scholor,salary) values('boyunv02','20211114','junior','200');
insert into test_u3(name,student_id,scholor,salary) values('boyunv03','20211115','senior','500');
insert into test_u3(name,student_id,scholor,salary) values('boyunv04','20211116','college','9000');
insert into test_u3(name,student_id,scholor,salary) values('boyunv05','20211117','master','30000');
insert into test_u3(name,student_id,scholor,salary) values('boyunv06','20211118','doctor','60000');

select  * from  test_u3

执行union all 语句

select  
select  
	name,id,salary, sum(salary) as salary
	from (
	  SELECT * FROM test_u1 
	  UNION ALL
	  SELECT *  FROM  test_u2
	  UNION ALL
	  SELECT * FROM test_u3
	
	)T
	GROUP BY id,name,salary

执行结果
在这里插入图片描述

后续补上这后面解释我自己不咋信服~勿喷