Mysql：从表中选择不在另一个表中的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11767565/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql: Select rows from a table that are not in another
提问by Stennie
How to select all rows in one table that do not appear on another?
如何选择一个表中没有出现在另一个表中的所有行？
Table1:
表格1：
+-----------+----------+------------+
| FirstName | LastName | BirthDate |
+-----------+----------+------------+
| Tia | Carrera | 1975-09-18 |
| Nikki | Taylor | 1972-03-04 |
| Yamila | Diaz | 1972-03-04 |
+-----------+----------+------------+
Table2:
表2：
+-----------+----------+------------+
| FirstName | LastName | BirthDate |
+-----------+----------+------------+
| Tia | Carrera | 1975-09-18 |
| Nikki | Taylor | 1972-03-04 |
+-----------+----------+------------+
Example output for rows in Table1 that are not in Table2:
Table1 中不在 Table2 中的行的示例输出：
+-----------+----------+------------+
| FirstName | LastName | BirthDate |
+-----------+----------+------------+
| Yamila | Diaz | 1972-03-04 |
+-----------+----------+------------+
Maybe something like this should work:
也许这样的事情应该有效：
SELECT * FROM Table1 WHERE * NOT IN (SELECT * FROM Table2)
采纳答案by Zane Bien
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 JOINto 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:
如果您在另一条评论中提到有 300 列，并且您想比较所有列（假设列的名称都相同），则可以使用 aNATURAL LEFT JOIN隐式连接两个表之间的所有匹配列名，以便您不必繁琐地手动输入所有连接条件：
SELECT a.*
FROM tbl_1 a
NATURAL LEFT JOIN tbl_2 b
WHERE b.FirstName IS NULL
回答by Stennie
You need to do the subselect based on a column name, not *.
您需要根据列名进行子选择，而不是*.
For example, if you had an idfield common to both tables, you could do:
例如，如果您有id两个表共有的字段，您可以执行以下操作：
SELECT * FROM Table1 WHERE id NOT IN (SELECT id FROM Table2)
Refer to the MySQL subquery syntaxfor more examples.
有关更多示例，请参阅MySQL 子查询语法。
回答by Ruzbeh Irani
SELECT *
FROM Table1 AS a
WHERE NOT EXISTS (
SELECT *
FROM Table2 AS b
WHERE a.FirstName=b.FirstName AND a.LastName=b.Last_Name
)
EXISTSwill help you...
EXISTS会帮助你...
回答by Steve
A standard LEFT JOIN could resolve the problem and, if the fields on join are indexed,
should also be faster
一个标准的 LEFT JOIN 可以解决这个问题，如果 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
回答by Sachin Pundir
Try:
尝试：
SELECT * FROM table1
LEFT OUTER JOIN table2
ON table1.FirstName = table2.FirstName and table1.LastName=table2.LastName
WHERE table2.BirthDate IS NULL
回答by Vijesh
Try this simple query. It works perfectly.
试试这个简单的查询。它完美地工作。
select * from Table1 where (FirstName,LastName,BirthDate) not in (select * from Table2);
回答by Gennady Sorochan
This worked for me in Oracle:
这在 Oracle 中对我有用：
SELECT a.*
FROM tbl1 a
MINUS
SELECT b.*
FROM tbl2 b;
回答by Ingrid R. Forsale
SELECT a.* FROM
FROM tbl_1 a
MINUS
SELECT b.* FROM
FROM tbl_2 b
