如何在mysql中检索非匹配结果?

I'm sure this is straight-forward, but how do I write a query in mysql that joins two tables and then returns only those records from the first table that don't match. I want it to be something like:

我确信这是很直接的，但是我如何在mysql中编写一个查询来连接两个表，然后只返回第一个表中不匹配的那些记录。我希望它是这样的:

Select tid from table1 inner join table2 on table2.tid = table1.tid where table1.tid != table2.tid;

从表1内部连接表2中选择tid。tid =表1。tid在表1。tid ! = table2.tid;

but this doesn't seem to make alot of sense!

但这似乎没有什么意义!

1 个解决方案

#1

You can use a left outer join to accomplish this:

你可以使用左外连接来完成:

select
    t1.tid
from
    table1 t1
    left outer join table2 t2 on
        t1.tid = t2.tid
where
    t2.tid is null

What this does is it takes your first table (table1), joins it with your second table (table2), and fills in null for the table2 columns in any row in table1 that doesn't match a row in table2. Then, it filters that out by selecting only the table1 rows where no match could be found.

它所做的是取第一个表(表1)，将它与第二个表(表2)连接，并为表1中与表2中不匹配的任何行中的表2列填入null。然后，它通过只选择表1中找不到匹配项的行来过滤。

Alternatively, you can also use not exists:

另外，您也可以使用不存在:

select
    t1.tid
from
    table1 t1
where
    not exists (select 1 from table2 t2 where t2.tid = t1.tid)

This performs a left semi join, and will essentially do the same thing that the left outer join does. Depending on your indexes, one may be faster than the other, but both are viable options. MySQL has some good documentation on optimizing the joins, so you should check that out..

它执行左半连接，并将执行与左外部连接相同的操作。根据索引的不同，其中一个可能比另一个更快，但这两个都是可行的选项。MySQL有一些关于优化连接的很好的文档，所以您应该检查一下。

#1