MySQL 查询 并集、交集、差集方式

MySQL查询交集、并集、差集

背景和使用的数据样本

该章节学些主要涉及到Datawhale SQL 组队学习任务；

本次使用的数据，由Datawhale 开源提供，具体代码见文章尾部：各位看官可以直接复制到MySQL Workbench 上运行生成数据库和表格。

MySQL WorkBench 如何对数据进行我们常见的集合运算呢？

Union、 INTERSECT（遗憾的是MySQL 8.0 不支持该运算用where /in 实现）、EXCEPT（遗憾的是MySQL 8.0 不支持该运算，但巧妙的用where …Not in 实现），本博客日记主要记录一些注意事项。

那么MySQL如何处理交集和差集呢？

基本语法

?

1 2 3

Select <列名1>,<列名2>,<列名3>... from <表名> union -- 或者其他 intersect、except、union all等） Select <列名1>,<列名2>,<列名3>... from <表名>

注意事项

1.首先：任何执行Union、 INTERSECT、EXCEPT的语句

都要注意，该关键字前后的Select 语句中选择的列的数量要一致，不一致会提示错误：

?

1 2 3 4 5

select product_id, product_name   -- 注意这里是2选择查询两个列 from product union select product_id, product_name  -- 这里的列的数量要与前面的列的数量一致 from product2;

正确的代码输出结果如下：

其次，select 语句中列数量不一致时，提示：

Error Code: 1222. The used SELECT statements have a different number of columns 0.000 sec

但是，对于select 中的选择的可以不一定是表格中原来就有的列表，此时能够正常查询数据表，具体如下：

?

1 2 3 4 5

select product_id, product_name from product union select product_id, '1' from product2;

结果如下：

2.Union vs Union All 的区别

Union 操作，自动去重复，即两个或多个数据表中相同的行只吃出现一次；

若想要所有表中的对应的数据都显示的话，则需要用到Union all

3.select sysdate() ; 查询返回系统当前的时间；

?

1 2 3 4 5

select product_name, sale_price, purchase_price from product union select sysdate(), sysdate() , sysdate() from product;

查询结果如下：

**4.MySQL 8.0 不支持交运算INTERSECT、except **

5. 查询差集：

?

1 2 3 4 5

-- 使用 IN 子句的实现方法 SELECT *    FROM product   WHERE product_id NOT IN ( SELECT product_id                              FROM product2)

结果：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210

/* v 9.08 SQL脚本的一些要点: 0.存储引擎使用 InnoDB, 字符集改为 utf8mb4 以更好地支持中文. 1.所有表名所使用的英文字母都改为小写(后续章节中,SQL查询中的表名也需要相应修改为小写) 2.所有列名所使用的英文字母确认为小写(后续章节中,SQL查询中的列名也需要相应修改为小写) 3.存在问题的数据, 例如 inventoryproduct 表的inventory_id列应为P开头的, 已修正为正确的数据. 4.需测试SQL脚本在命令行及各个客户端中是否能被正确执行.    * MySQL Workbench 已测试通过    * DBeaver 已测试通过(使用 "执行SQL脚本(CTR+x)" )    * HeidiSQL 已测试通过    * navicat 已测试通过    * sqlyog 已测试通过    * 命令行 测试未通过. 插入中文数据时提示 " Data too long for column 'product_name' at row 1" */ CREATE DATABASE /*!32312 IF NOT EXISTS*/`shop` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION= 'N' */;   USE `shop`;   /* Table structure for table `chars` */   DROP TABLE IF EXISTS `chars`;   CREATE TABLE `chars` (    `chr` char (3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    PRIMARY KEY (`chr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_0900_ai_ci;   /*Data for the table `chars` */   insert  into `chars`(`chr`) values ( '1' ),( '10' ),( '11' ),( '2' ),( '222' ),( '3' );   /* Table structure for table `empskills` */   DROP TABLE IF EXISTS `empskills`;   CREATE TABLE `empskills` (    `emp` varchar (32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `skill` varchar (32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    PRIMARY KEY (`emp`,`skill`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;   /*Data for the table `empskills` */   insert  into `empskills`(`emp`,`skill`) values ( '平井' , 'C++' ),( '平井' , 'Oracle' ),( '平井' , 'Perl' ),( '平井' , 'PHP' ),( '平井' , 'UNIX' ),( '渡来' , 'Oracle' ),( '相田' , 'C#' ),( '相田' , 'Java' ),( '相田' , 'Oracle' ),( '相田' , 'UNIX' ),( '神崎' , 'Java' ),( '神崎' , 'Oracle' ),( '神崎' , 'UNIX' ),( '若田部' , 'Perl' );   /* Table structure for table `inventoryproduct` */   DROP TABLE IF EXISTS `inventoryproduct`;   CREATE TABLE `inventoryproduct` (    `inventory_id` char (4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `product_id` char (4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `inventory_quantity` int NOT NULL ,    PRIMARY KEY (`inventory_id`,`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_0900_ai_ci;   /*Data for the table `inventoryproduct` */   insert  into `inventoryproduct`(`inventory_id`,`product_id`,`inventory_quantity`) values ( 'P001' , '0001' ,0),( 'P001' , '0002' ,120),( 'P001' , '0003' ,200),( 'P001' , '0004' ,3),( 'P001' , '0005' ,0),( 'P001' , '0006' ,99),( 'P001' , '0007' ,999),( 'P001' , '0008' ,200),( 'P002' , '0001' ,10),( 'P002' , '0002' ,25),( 'P002' , '0003' ,34),( 'P002' , '0004' ,19),( 'P002' , '0005' ,99),( 'P002' , '0006' ,0),( 'P002' , '0007' ,0),( 'P002' , '0008' ,18);   /* Table structure for table `product` */   DROP TABLE IF EXISTS `product`;   CREATE TABLE `product` (    `product_id` char (4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `product_name` varchar (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `product_type` varchar (32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `sale_price` int DEFAULT NULL ,    `purchase_price` int DEFAULT NULL ,    `regist_date` date DEFAULT NULL ,    PRIMARY KEY (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_0900_ai_ci;   /*Data for the table `product` */   insert  into `product`(`product_id`,`product_name`,`product_type`,`sale_price`,`purchase_price`,`regist_date`) values ( '0001' , 'T恤' , '衣服' ,1000,500, '2009-09-20' ),( '0002' , '打孔器' , '办公用品' ,500,320, '2009-09-11' ),( '0003' , '运动T恤' , '衣服' ,4000,2800, NULL ),( '0004' , '菜刀' , '厨房用具' ,3000,2800, '2009-09-20' ),( '0005' , '高压锅' , '厨房用具' ,6800,5000, '2009-01-15' ),( '0006' , '叉子' , '厨房用具' ,500, NULL , '2009-09-20' ),( '0007' , '擦菜板' , '厨房用具' ,880,790, '2008-04-28' ),( '0008' , '圆珠笔' , '办公用品' ,100, NULL , '2009-11-11' );   /* Table structure for table `product2` */   DROP TABLE IF EXISTS `product2`;   CREATE TABLE `product2` (    `product_id` char (4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `product_name` varchar (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `product_type` varchar (32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `sale_price` int DEFAULT NULL ,    `purchase_price` int DEFAULT NULL ,    `regist_date` date DEFAULT NULL ,    PRIMARY KEY (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_0900_ai_ci;   /*Data for the table `product2` */   insert  into `product2`(`product_id`,`product_name`,`product_type`,`sale_price`,`purchase_price`,`regist_date`) values ( '0001' , 'T恤' , '衣服' ,1000,500, '2009-09-20' ),( '0002' , '打孔器' , '办公用品' ,500,320, '2009-09-11' ),( '0003' , '运动T恤' , '衣服' ,4000,2800, NULL ),( '0009' , '手套' , '衣服' ,800,500, NULL ),( '0010' , '水壶' , '厨房用具' ,2000,1700, '2009-09-20' );   /* Table structure for table `productcopy` */   DROP TABLE IF EXISTS `productcopy`;   CREATE TABLE `productcopy` (    `product_id` char (4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `product_name` varchar (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `product_type` varchar (32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `sale_price` int DEFAULT NULL ,    `purchase_price` int DEFAULT NULL ,    `regist_date` date DEFAULT NULL ,    PRIMARY KEY (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;   /*Data for the table `productcopy` */   /* Table structure for table `productins` */   DROP TABLE IF EXISTS `productins`;   CREATE TABLE `productins` (    `product_id` char (4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `product_name` varchar (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `product_type` varchar (32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `sale_price` int DEFAULT '0' ,    `purchase_date` int DEFAULT NULL ,    `regist_date` date DEFAULT NULL ,    PRIMARY KEY (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;   /*Data for the table `productins` */   /* Table structure for table `producttype` */   DROP TABLE IF EXISTS `producttype`;   CREATE TABLE `producttype` (    `product_type` varchar (32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `sum_sale_price` int DEFAULT NULL ,    `sum_purchase_price` int DEFAULT NULL ,    PRIMARY KEY (`product_type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;   /*Data for the table `producttype` */   /* Table structure for table `samplelike` */   DROP TABLE IF EXISTS `samplelike`;   CREATE TABLE `samplelike` (    `strcol` varchar (6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    PRIMARY KEY (`strcol`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;   /*Data for the table `samplelike` */   insert  into `samplelike`(`strcol`) values ( 'abcdd' ),( 'abcddd' ),( 'abddc' ),( 'abdddc' ),( 'ddabc' ),( 'dddabc' );   /* Table structure for table `samplemath` */   DROP TABLE IF EXISTS `samplemath`;   CREATE TABLE `samplemath` (    `m` decimal (10,3) DEFAULT NULL ,    `n` int DEFAULT NULL ,    `p` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;   /*Data for the table `samplemath` */   insert  into `samplemath`(`m`,`n`,`p`) values ( '500.000' ,0, NULL ),( '-180.000' ,0, NULL ),( NULL , NULL , NULL ),( NULL ,7,3),( NULL ,5,2),( NULL ,4, NULL ),( '8.000' , NULL ,3),( '2.270' ,1, NULL ),( '5.555' ,2, NULL ),( NULL ,1, NULL ),( '8.760' , NULL , NULL );   /* Table structure for table `samplestr` */   DROP TABLE IF EXISTS `samplestr`;   CREATE TABLE `samplestr` (    `str1` varchar (40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL ,    `str2` varchar (40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL ,    `str3` varchar (40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;   /*Data for the table `samplestr` */   insert  into `samplestr`(`str1`,`str2`,`str3`) values ( 'opx' , 'rt' , NULL ),( 'abc' , 'def' , NULL ),( '山田' , '太郎' , '是我' ),( 'aaa' , NULL , NULL ),( NULL , 'xyz' , NULL ),( '@!#$%' , NULL , NULL ),( 'ABC' , NULL , NULL ),( 'aBC' , NULL , NULL ),( 'abc太郎' , 'abc' , 'ABC' ),( 'abcdefabc' , 'abc' , 'ABC' ),( 'micmic' , 'i' , 'I' );   /* Table structure for table `shopproduct` */   DROP TABLE IF EXISTS `shopproduct`;   CREATE TABLE `shopproduct` (    `shop_id` char (4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `shop_name` varchar (200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `product_id` char (4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    `quantity` int NOT NULL ,    PRIMARY KEY (`shop_id`,`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_0900_ai_ci;   /*Data for the table `shopproduct` */   insert  into `shopproduct`(`shop_id`,`shop_name`,`product_id`,`quantity`) values ( '000A' , '东京' , '0001' ,30),( '000A' , '东京' , '0002' ,50),( '000A' , '东京' , '0003' ,15),( '000B' , '名古屋' , '0002' ,30),( '000B' , '名古屋' , '0003' ,120),( '000B' , '名古屋' , '0004' ,20),( '000B' , '名古屋' , '0006' ,10),( '000B' , '名古屋' , '0007' ,40),( '000C' , '大阪' , '0003' ,20),( '000C' , '大阪' , '0004' ,50),( '000C' , '大阪' , '0006' ,90),( '000C' , '大阪' , '0007' ,70),( '000D' , '福冈' , '0001' ,100);   /* Table structure for table `skills` */   DROP TABLE IF EXISTS `skills`;   CREATE TABLE `skills` (    `skill` varchar (32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ,    PRIMARY KEY (`skill`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;   /*Data for the table `skills` */   insert  into `skills`(`skill`) values ( 'Java' ),( 'Oracle' ),( 'UNIX' );

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。

原文链接：https://blog.csdn.net/AI_SupplyChain/article/details/111567920