![](https://i-blog.csdnimg.cn/blog_migrate/bede9622e5e843695f1ce85699be0355.jpeg)
数据导入
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;
结果展示:
![](https://i-blog.csdnimg.cn/blog_migrate/5198f74b0fd642b2f779ec051d600fb0.png)