MySQL选择左连接为空的行

Question

我有这些MySQL表：

表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。

我想只选择左连接中未找到的行。所以它应该仅返回带有id 3 的行。

我想要INNER JOIN的相反操作，它将仅选择在JOIN中找到的行。如何获得相反效果，例如如果左连接存在，则忽略它并移动到下一行。希望我表达清楚。

- Michael Samuel

---

添加 where table2.user_one is null。 - Gordon Linoff

@GordonLinoff，能否请您为我解释一下添加 where table2.user_one is null 的逻辑，因为我认为它会返回 2 和 3，但用户只想要 3。 - Ankit Bajpai

table2的结构是什么？有两列user_one和user_two吗？ - Joël Salamin

Answer 1

你可以使用以下查询:

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 上的索引，您可能会发现两个联接更有效：

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;

- GarethD

Answer 2

如果您不想从table2返回任何列，最好的方法之一是使用NOT EXISTS子查询。

SELECT  table1.id 
FROM    table1 T1
WHERE
  NOT EXISTS (SELECT *
              FROM table2 T2
              WHERE T1.id = T2.user_one
                  OR T1.id = T2.user_two)

从语义上讲，这表达了您想查询的内容：选择每一行，在第二张表中没有匹配记录的情况下。

MySQL对EXISTS进行了优化：只要找到第一个匹配记录，就会立即返回。

还有一点需要注意： 当您在连接表中检查可空列时，根据您的需求，您可能需要使用NOT EXISTS（或在使用LEFT JOIN时检查其他列），因为MySQL无法区分已经存在于连接表中但是列值为NULL的记录和连接表中没有匹配记录所生成的NULL列值。

- Pred

---

“再多一点说明”是解决我遇到的类似问题的关键。我完全不知道MySQL在这种情况下无法区分两个NULL值。非常感谢。 - Jonathan Head

Answer 3

下面是一个查询，它仅返回在table2的user_one和user_two列中都没有相应值的行：

SELECT T1.*
FROM table1 T1
LEFT OUTER JOIN table2 T2A ON T2A.user_one = T1.id
LEFT OUTER JOIN table2 T2B ON T2B.user_two = T1.id
WHERE T2A.user_one IS NULL
    AND T2B.user_two IS NULL

每个列（user_one和user_two）都有一个联接，查询仅返回没有匹配联接的行。

希望这能帮到你。

- Joël Salamin

Answer 4

SELECT table1.id 
FROM table1 
LEFT JOIN table2 ON table1.id = table2.user_one
WHERE table2.user_one is NULL

- Chris

Answer 5

尝试以下查询：-

 SELECT table1.id 
 FROM table1 
 where table1.id 
 NOT IN (SELECT user_one
         FROM Table2
             UNION
         SELECT user_two
         FROM Table2)

希望这能对你有所帮助。

- Ankit Bajpai

Answer 6

尝试：

SELECT A.id FROM
(
  SELECT table1.id FROM table1 
  LEFT JOIN table2 ON table1.id = table2.user_one
  WHERE table2.user_one IS NULL
) A
JOIN (
  SELECT table1.id FROM table1 
  LEFT JOIN table2 ON table1.id = table2.user_two
  WHERE table2.user_two IS NULL
) B
ON A.id = B.id

请查看Demo

或者您可以使用两个带别名的LEFT JOIN，例如：

SELECT table1.id FROM table1 
 LEFT JOIN table2 A ON table1.id = A.user_one
 LEFT JOIN table2 B ON table1.id = B.user_two
 WHERE A.user_one IS NULL
 AND B.user_two IS NULL

请查看第二个演示：2nd Demo。

- Edper