Mysql:从不在另一个表中的表中选择行

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:

表二:

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

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

表1中没有表2中的行示例输出:

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

Maybe something like this should work:

也许像这样的东西应该有用:

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

8 个解决方案

#1

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:

如果你有300列在另一个评论,你提到你想要比较所有列(假定列都是相同的名称),您可以使用一个自然左加入隐式连接两个表之间的所有匹配的列名称,这样您不必沉闷地类型所有手动加入条件:

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

#2

141

You need to do the subselect based on a column name, not *.

您需要基于列名而不是*进行子选择。

For example, if you had an id field common to both tables, you could do:

例如，如果两个表都有一个id字段，那么可以这样做:

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

Refer to the MySQL subquery syntax for more examples.

更多示例请参见MySQL子查询语法。

#3

A standard LEFT JOIN could resolve the problem and, if the fields on join are indexed,
should also be faster

一个标准的左连接可以解决这个问题，而且，如果连接上的字段被编入索引，也应该会更快

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

#4

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 will help you...

存在会帮助你……

#5

Try:

试一试:

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

#6

This worked for me in Oracle:

这在Oracle中对我很有效:

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

#7

Try this simple query. It works perfectly.

试试这个简单的查询。它的工作原理。

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

#8

-4

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

#1

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

#2

141