假设我们有两个表t1并t2具有以下结构和数据:
CREATE TABLE t1 (
id INT PRIMARY KEY
);
CREATE TABLE t2 (
id INT PRIMARY KEY
);
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (2),(3),(4);
首先执行SQL
SELECT
t1.id, t2.id
FROM
t1
LEFT JOIN t2 ON t1.id=t2.id
得到结果
mysql> SELECT
-> t1.id, t2.id
-> FROM
-> t1
-> LEFT JOIN t2 ON t1.id=t2.id;
+----+------+
| id | id |
+----+------+
| 1 | NULL |
| 2 | 2 |
| 3 | 3 |
在加上查询条件
SELECT
t1.id, t2.id
FROM
t1
LEFT JOIN t2 ON t1.id=t2.id
WHERE
t2.id IS NULL;
参考资料
https://www.begtut.com/mysql/mysql-minus.html