UNION和UNION ALL的用法与区别

前言

在MySQL中，可以使用UNION和UNION ALL查询多多条结果，并且它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同

使用

1、 建立light_devcice表

sql

代码解读

复制代码

SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for light_device -- ---------------------------- DROP TABLE IF EXISTS `light_device`; CREATE TABLE `light_device` ( `id` bigint NOT NULL AUTO_INCREMENT, `tenant_id` bigint NULL DEFAULT NULL COMMENT '租户ID', `org_id` bigint NOT NULL COMMENT '机构ID', `host_id` bigint NOT NULL COMMENT '网关设备ID', `device_id` bigint NOT NULL COMMENT '设备ID', `parent_device_id` bigint NULL DEFAULT NULL COMMENT '父设备ID', `device_name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '设备名称', `device_remark` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '设备备注', `device_sn` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '设备唯一标识', `device_type` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '设备类型', `device_category` int NULL DEFAULT NULL COMMENT '设备分类', `region_id` bigint NULL DEFAULT NULL COMMENT '区域ID', `display_flag` tinyint NULL DEFAULT 1 COMMENT '是否显示(网关):1>显示;0>不显示', `terminal_flag` tinyint NULL DEFAULT 0 COMMENT '带终端设备能力:1>是;0>否', `control_flag` tinyint NULL DEFAULT 0 COMMENT '控制能力:1>是;0>否', `delete_flag` tinyint NULL DEFAULT 0 COMMENT '是否删除:1：是；0否', `create_by` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '创建人', `update_by` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '更新人', `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `saas_device_id` bigint NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `device_id_UNIQUE`(`device_id` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1899370485329244162 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC; SET FOREIGN_KEY_CHECKS = 1;

2、建立light_device_property表

sql

代码解读

复制代码

SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for light_device_property -- ---------------------------- DROP TABLE IF EXISTS `light_device_property`; CREATE TABLE `light_device_property` ( `id` bigint NOT NULL AUTO_INCREMENT, `tenant_id` bigint NULL DEFAULT NULL COMMENT '租户ID', `org_id` bigint NOT NULL COMMENT '机构ID', `host_id` bigint NULL DEFAULT NULL COMMENT '网关ID', `device_sn` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '设备唯一标识', `device_id` bigint NOT NULL COMMENT '设备ID', `device_key` int NOT NULL COMMENT '设备属性ID', `property_name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '属性名称', `property_remark` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '设备备注', `display_flag` tinyint NULL DEFAULT 1 COMMENT '是否显示(网关):1>显示;0>不显示', `delete_flag` tinyint NULL DEFAULT 0 COMMENT '是否删除:1：是；0否', `region_id` bigint NOT NULL COMMENT '区域ID', `create_by` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '创建人', `update_by` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '更新人', `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `load_dev_type` int NULL DEFAULT NULL COMMENT '终端设备类型：开关量输入设备、继电器输出设备类型、无线终端设备类型。', `saas_device_id` bigint NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1897904309122183171 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC; SET FOREIGN_KEY_CHECKS = 1;

使用union查询

css

代码解读

复制代码

select p.id as propertyId, p.device_id as deviceId, p.property_name as deviceName, p.device_key as deviceKey, p.property_remark as deviceRemark, p.host_id as hostId, p.load_dev_type as deviceType, p.display_flag as display, p.saas_device_id as saasDeviceId from light_device_property p where p.org_id = 1853960415193595905 union select p.id as propertyId, p.device_id as deviceId, p.device_name as deviceName, null as deviceKey, p.device_remark as deviceRemark, p.host_id as hostId, p.device_type as deviceType, p.display_flag as display, p.saas_device_id as saasDeviceId from light_device p where p.org_id = 1853960415193595905

返回值为

如果使用该sql

csharp

代码解读

复制代码

select p.id as propertyId from light_device_property p where p.org_id = 1853960415193595905 union select p.id as propertyId from light_device p where p.org_id = 1853960415193595905

会发现如果两个表有重复id，不会出现重复记录

css

代码解读

复制代码

select p.id as propertyId from light_device_property p where p.org_id = 1853960415193595905 union all select p.id as propertyId from light_device p where p.org_id = 1853960415193595905

会发现如果两个表有重复id，会出现重复记录

总结

UNION 从查询结果集中自动去除了重复的行，如果要返回所有匹配行，而不进行删除，可以使用 UNION ALL。