[Mysql] 集合操作

数据导入

DROP TABLE IF EXISTS `table_aid`;
CREATE TABLE `table_aid` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `table_aid` VALUES ('1');
INSERT INTO `table_aid` VALUES ('2');
INSERT INTO `table_aid` VALUES ('3');
INSERT INTO `table_aid` VALUES ('4');

DROP TABLE IF EXISTS `table_bid`;
CREATE TABLE `table_bid` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `table_bid` VALUES ('2');
INSERT INTO `table_bid` VALUES ('3');
INSERT INTO `table_bid` VALUES ('5');
INSERT INTO `table_bid` VALUES ('6');

table_aid表

table_bid表

上述表a和表b都只有一个字段，即相同字段名id

问题1:求表a与表b的交集

SELECT a.id
FROM table_aid AS a
INNER JOIN table_bid AS b
ON a.id = b.id;

结果展示:

问题2:求表a与表b的并集

SELECT *
FROM table_aid
UNION 
SELECT *
FROM table_bid;

结果展示:

问题3:存在表a但不存在表b的元素

SELECT a.id
FROM table_aid AS a
LEFT JOIN table_bid AS b
ON a.id = b.id
WHERE b.id IS NULL;

结果展示:

问题3:存在表b但不存在表a的元素

SELECT b.id
FROM table_aid AS a
RIGHT JOIN table_bid AS b
ON a.id = b.id
WHERE a.id IS NULL;

结果展示:

问题4:存在表a但不存在表b的元素，同时存在表b但不同时存在表a的元素

-- 解法1
SELECT a.id
FROM table_aid AS a
LEFT JOIN table_bid AS b
ON a.id = b.id
WHERE b.id IS NULL
UNION
SELECT b.id
FROM table_aid AS a
RIGHT JOIN table_bid AS b
ON a.id = b.id
WHERE a.id IS NULL;

-- 解法2
SELECT *
FROM table_aid
WHERE id NOT IN(SELECT id FROM table_bid)
UNION
SELECT *
FROM table_bid
WHERE id NOT IN(SELECT id FROM table_aid);

结果展示:

知识扩展:查找不在表里的数据

遇到要查找“不在表里的数据，也就是在表A里的数据，但是不在表B里的数据”，可以使用如下SQL语句:

SELECT...

FROM 表1 AS a

LEFT JOIN 表2 AS b

ON a.列名 = b.列名

WHERE b.列名 IS NULL;

SELECT...

FROM 表1 AS a

RIGHT JOIN 表2 AS b

ON a.列名 = b.列名

WHERE a.列名 IS NULL;

案例练习

数据导入

DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `customers` VALUES (1, 'Joe');
INSERT INTO `customers` VALUES (2, 'Henry');
INSERT INTO `customers` VALUES (3, 'Sam');
INSERT INTO `customers` VALUES (4, 'Max');

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `id` int(11) NOT NULL,
  `customerid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `orders` VALUES (1, 3);
INSERT INTO `orders` VALUES (2, 1);

某网站包含两个表，顾客姓名表(表名Customers)和购买记录表(表名Orders)

“顾客姓名表”中的ID与“购买记录”表中的学生学号CustomerId一一对应

customers表(顾客姓名表)

orders表(购买记录表)

问题:找出所有从不订购任何东西的客户

SELECT a.name AS Customers
FROM customers AS a
LEFT JOIN orders AS b
ON a.id = b.customerid
WHERE b.customerid IS null;

结果展示: