Mysql:从表中选择不在另一个表中的行 [英] Mysql: Select rows from a table that are not in another

问题描述

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

How to select all rows in one table that do not appear on another?

表1:

+-----------+----------+------------+
| 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 |
+-----------+----------+------------+

表1中不在表2中的行的示例输出:

Example output for rows in Table1 that are not in Table2:

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

也许类似的东西应该起作用:

Maybe something like this should work:

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

推荐答案

如果您在另一条注释中提到有300列，并且想要在所有列上进行比较(假设这些列的名称相同)，则可以使用NATURAL LEFT JOIN隐式联接两个表之间所有匹配的列名，这样您就不必手动输入所有联接条件了:

If you have 300 columns as you mentioned in another comment, and you want to compare on all columns (assuming the columns are all the same name), you can use a NATURAL LEFT JOIN to implicitly join on all matching column names between the two tables so that you don't have to tediously type out all join conditions manually:

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

这篇关于Mysql:从表中选择不在另一个表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！