union和union all
1.语法
union:联合的意思,即把两次或多次查询结果合并起来。
要求:两次查询的列数必须一致
可以来自多张表的数据:多次sql语句取出的列名可以不一致,此时以第一个sql语句的列名为准。
如果不同的语句中取出的行,有完全相同(这里表示的是每个列的值都相同),那么union会将相同的行合并,最终只保留一行。也可以这样理解,union会去掉重复的行。
如果不想去掉重复的行,可以使用union all。
2.例子
select name FROM student union select name from teacher
如果两次查询的列数不一致,就会报错:
select name,id FROM student union select name from teacher
union all :
select name FROM student union all select name from teacher
所需的表和数据:
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`score` int(40) NULL DEFAULT NULL,
`course` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `student` VALUES ('12', 'tbb', 80, '语文');
INSERT INTO `student` VALUES ('12', 'tbb', 70, '数学');
INSERT INTO `student` VALUES ('12', 'tbb', 60, '英语');
INSERT INTO `student` VALUES ('13', 'xb', 98, '语文');
INSERT INTO `student` VALUES ('13', 'xb', 96, '英语');
INSERT INTO `student` VALUES ('13', 'xb', 97, '数学');
INSERT INTO `student` VALUES ('14', 'xz', 18, '语文');
INSERT INTO `student` VALUES ('14', 'xz', 28, '数学');
INSERT INTO `student` VALUES ('14', 'xz', 8, '英语');
INSERT INTO `student` VALUES ('15', 'xm', 78, '物理');
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(40) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `teacher` VALUES ('tbb', 25);
INSERT INTO `teacher` VALUES ('xm', 24);