【mysql】联合查询union和union all的区别

一、联合查询的说明

• 1.联合查询的多张表的列数必须保持一致
• 2.联合查询的多张表的字段类型必须保持一致
• 3.union查询把多次查询的结果合并起来，形成一个新的查询结果集
• 4.语法：select 字段列表 from 表A union select 字段列表 from 表B
• 5.union all会将全部的数据直接合并在一起
• 6.union会对合并之后的数据去重

二、创表语句

2.1 教师表

CREATE TABLE `test`.`learning_teacher`  (
  `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

2.2 学生表

CREATE TABLE `test`.`learning_student`  (
  `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '名称',
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '描述',
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '类型',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

三、联合失败情况

3.1 字段排序规则不一样

select id,name from learning_teacher union select id, name from learning_student;

3.2 字段个数不一样

select id from learning_teacher union select id, name from learning_student;

四、区别

select id,name from learning_teacher union all select id, name from learning_student;

select id,name from learning_teacher union select id, name from learning_student;