如何在mysql中检索不匹配的结果 [英] How to retrieve non-matching results in mysql

问题描述

我确定这是直接的，但是如何在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:

从table1内部连接table2选择tid table2.tid = table1.tid其中table1.tid！= table2.tid;

但这似乎没有什么意义！

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

推荐答案

您可以使用 left outer join 完成此操作：

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

这是什么，它需要你的第一个表（ table1 ），将它与您的第二个表（ table2 ）相连接，并填写 null 在 table1 中的任何一行中的 table2 列不匹配 table2 。然后，它通过只选择 table1 行中找不到匹配项来过滤出来。

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.

也可以使用 不存在 ：

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)

这会执行左半连接，并且基本上会执行左外连接。根据您的索引，一个可能比另一个更快，但两个都是可行的选项。 MySQL有一些关于优化连接的文档 ，所以你应该检查出来..

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中检索不匹配的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！