一、概述
union
和union all
都用于合并多个查询,用法为:
select * from a union select * from b;
select * from a union all select * from b;
两者的区别是union all
合并的结果中会有重复记录,而union
中没有。
二、示例
创建两张表chinese和math分别表示语文课和数学课的选课情况:
CREATE TABLE chinese (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);
INSERT INTO chinese (name) VALUES ('小明'), ('小花'), ('小白'), ('小刚');
CREATE TABLE math (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL
);
INSERT INTO math (name) VALUES ('小明'), ('小李'), ('老王');
2.1 union
SELECT name FROM chinese
UNION
SELECT name FROM math;
结果中没有重复记录小明
:
2.2 union all
SELECT name FROM chinese
UNION ALL
SELECT name FROM math;
结果中有重复记录小明
: