MySQL选择左连接为空的行 [英] MySQL select rows where left join is null

问题描述

我有这些MySQL表:

I have these MySQL tables:

表1:

id | writer
1  | Bob   
2  | Marley
3  | Michael

表2:

user_one | user_two
   1     | 2

此查询:

SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id = table2.user_one

此查询将返回table1的所有行1,2,3

This query will return all rows of table1 which are 1,2,3

我只想选择在左关节中找不到的行.因此，它应该仅返回ID为3

I want to select only rows which are not found in the left joint. So it should return only row with id 3

我想要与INNER JOIN相反，它将仅选择在联接中找到的行.如何获得相反的结果(例如是否存在左联接)，忽略它并移至下一行.希望我很清楚

I want sort of the opposite of INNER JOIN which will select only the rows which are found in the join. How to get the opposite like if left join exists, ignore it and move to the next row. Hope i'm clear

推荐答案

您可以使用以下查询:

SELECT  table1.id 
FROM    table1 
        LEFT JOIN table2 
            ON table1.id IN (table2.user_one, table2.user_two)
WHERE   table2.user_one IS NULL;

尽管，根据您在table2上的索引，您可能会发现两个联接的效果更好:

Although, depending on your indexes on table2 you may find that two joins performs better:

SELECT  table1.id 
FROM    table1 
        LEFT JOIN table2 AS t1
            ON table1.id = t1.user_one
        LEFT JOIN table2 AS t2
            ON table1.id = t2.user_two
WHERE   t1.user_one IS NULL
AND     t2.user_two IS NULL;

这篇关于MySQL选择左连接为空的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！