MySQL：从一个表中选择不在另一个表中的行

Question

如何在一个表中选择所有未出现在另一个表中的行？

Table1：

+-----------+----------+------------+
| FirstName | LastName | BirthDate  |
+-----------+----------+------------+
| Tia       | Carrera  | 1975-09-18 |
| Nikki     | Taylor   | 1972-03-04 |
| Yamila    | Diaz     | 1972-03-04 |
+-----------+----------+------------+

表2：

+-----------+----------+------------+
| FirstName | LastName | BirthDate  |
+-----------+----------+------------+
| Tia       | Carrera  | 1975-09-18 |
| Nikki     | Taylor   | 1972-03-04 |
+-----------+----------+------------+

Table1中不在Table2中的行的示例输出：

+-----------+----------+------------+
| FirstName | LastName | BirthDate  |
+-----------+----------+------------+
| Yamila    | Diaz     | 1972-03-04 |
+-----------+----------+------------+

也许像这样做可以起作用：

SELECT * FROM Table1 WHERE * NOT IN (SELECT * FROM Table2)

- user1006989

Answer 1

你需要基于列名进行子查询，而不是使用通配符*。

例如，如果两个表都有一个公共的id字段，你可以这样写：

SELECT * FROM Table1 WHERE id NOT IN (SELECT id FROM Table2)

请参考MySQL子查询语法以获取更多示例。

- Stennie

---

谢谢澄清！但我真的不需要根据任何字段来选择行，因为我对行中任何字段的任何变化都感兴趣... - user1006989

如果只有几列需要比较，您可以像@Steve的示例一样进行连接。如果您实际上是要求对具有许多列的两个表中的数据进行一般比较，则可能需要寻找MySQL差异工具。 - Stennie

请注意，如果Table2中你所查看的列包含空值，那么这将始终返回一个空集合。如果您基于主键进行操作，则不会出现问题，但对于试图在其他情况下使用此查询的人来说，这一点很重要。 - Mark Amery

如果我们在谈论大数据，比如Table2包含1亿行数据，会怎样？ - frops

聪明而机智的回答。谢谢伙计。 - Anjana Silva

Answer 2

如果你有300列，如你在另一个评论中提到的，而且你想要在所有列上进行比较（假设这些列都是相同的名称），那么你可以使用 NATURAL LEFT JOIN 隐式地在两个表之间所有匹配的列名上进行联接，这样你就不必手动输入所有联接条件：

SELECT            a.*
FROM              tbl_1 a
NATURAL LEFT JOIN tbl_2 b
WHERE             b.FirstName IS NULL

- Zane Bien

---

请注意，仅当没有任何列具有NULL值时，此功能才能按预期工作。在MySQL中，NULL！= NULL，因此即使第二个表中存在重复行，每个具有NULL值的行也将被返回。 - Kyle Kochis

如果你的数据库有300列，那么你应该重新设计它。 - Iharob Al Asimi

嘿，这对我也有效，谢谢！但是如果行数> 300像你上面提到的那样，会有问题吗？ - thekucays

我仍然对查询感到困惑。例如，如果我将“where b.FirstName is null”更改为“where b.LastName is null”，会有什么区别？很抱歉问这个问题，我还是SQL的新手:D - thekucays

Answer 3

SELECT *
FROM Table1 AS a
WHERE NOT EXISTS (
  SELECT *
  FROM Table2 AS b 
  WHERE a.FirstName=b.FirstName AND a.LastName=b.Last_Name
)

EXISTS 可以帮助你...

- Ruzbeh Irani

---

好的回答，适用于大数据集，谢谢。 - ekerner

强大。针对大型数据集的最佳答案。 - Ian Chadwick

Answer 4

使用标准的LEFT JOIN可以解决这个问题，并且如果连接字段有索引的话，应该也会更快

SELECT *
FROM Table1 as t1 LEFT JOIN Table2 as t2 
ON t1.FirstName = t2.FirstName AND t1.LastName=t2.LastName
WHERE t2.BirthDate Is Null

- Steve

---

好的，我猜那就是了。顺便问一下，为什么要用 WHERE t2.Birthdate Is Null 而不是 AND t1.Birthdate = t2.Birthdate？ - user1006989

因为如果添加了它，那么每一行都将被返回，你说输出中应该只出现不在第二个表中的行。 - Steve

这是一个很棒的答案，因为它不需要返回Table2的所有行！ - dotancohen

我同意，非常好的答案。我有一个连接了4个表的多对多关系表，在内部连接中使用AND肯定会更加经济实惠。 - DR.

Answer 5

尝试这个简单的查询。它能完美地工作。

select * from Table1 where (FirstName,LastName,BirthDate) not in (select * from Table2);

- Vijesh

---

我喜欢这个的原因是一个表中的列名不必与另一个表中的列名相同，它只比较相应的值（已测试并且有效）。 - Jose Manuel Abarca Rodríguez

Answer 6

尝试：

SELECT * FROM table1
    LEFT OUTER JOIN table2
    ON table1.FirstName = table2.FirstName and table1.LastName=table2.LastName
    WHERE table2.BirthDate IS NULL

- Sachin Pundir

---

这是一个很好的答案，但请注意，“IS NULL”将在table1上触发全表扫描。如果可能的话，我们可以使用table1的索引列来添加作用域限制到where语句中。例如：AND table1.name = 'Joe'。 - ozma

Answer 7

一个选项是：

SELECT A.*
    FROM TableA as A
    LEFT JOIN TableB as B
    ON A.id = B.id
    Where B.id Is NULL

- Heitor Giacomini

Answer 8

SELECT 
table_A.column_1, 
table_A.column_2, 
table_A.email

FROM table_A

WHERE table_A.email 
NOT IN ( SELECT table_B.email FROM table_B )

从表A中选择多列的示例。

表A和表B都有一个名为“email”的列。

在查询结果中将省略来自表B的匹配电子邮件。

（此问题类似于mysql select from table 1 where no match in table 2）

- Yoda - user264474

Answer 9

在Oracle中，这对我起作用了：

SELECT a.* 
    FROM tbl1 a 
MINUS 
SELECT b.* 
    FROM tbl2 b;

- Gennady Sorochan

---

问题是关于MySQL的。 - jelder

Answer 10

SELECT a.* FROM 
FROM tbl_1 a
MINUS
SELECT b.* FROM 
FROM tbl_2 b

- Ingrid R. Forsale