MySQL MySQL选择左连接为空的行

MySQL select rows where left join is null

提问by Michael Samuel

I have these MySQL tables:

我有这些 MySQL 表：

table1:

表格1：

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

table2:

表2：

user_one | user_two
   1     | 2

And this query:

这个查询：

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

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

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

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

我只想选择在左关节中找不到的行。所以它应该只返回带有 id 的行3

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

我想要一种与 INNER JOIN 相反的方式，它只会选择在连接中找到的行。如何获得相反的结果，就像存在左连接一样，忽略它并移动到下一行。希望我很清楚

回答by GarethD

You could use the following query:

您可以使用以下查询：

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

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

虽然，根据您的索引，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;

回答by Pred

One of the best approach if you do not want to return any columns from table2is to use the NOT EXISTS

如果您不想从中返回任何列，最好的方法之一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)

Semantically this says what you want to query: Select every row where there is no matching record in the second table.

从语义上讲，这说明了您要查询的内容：选择第二个表中没有匹配记录的每一行。

MySQL is optimized for EXISTS: It returns as soon as it finds the first matching record.

MySQL 针对以下情况进行了优化EXISTS：它在找到第一条匹配记录后立即返回。

回答by Jo?l Salamin

Here is a query that returns only the rows where no correspondance has been found in both columns user_oneand user_twoof table2:

这是一个查询，它只返回在两列user_one和user_twoof中都没有找到对应关系的行table2：

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

There is one jointure for each column (user_oneand user_two) and the query only returns rows that have no matching jointure.

每列 (user_one和user_two)有一个关节，查询仅返回没有匹配关节的行。

Hope this will help you.

希望这会帮助你。

回答by Chris

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

回答by Edper

Try:

尝试：

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

See Demo

看 Demo

Or you could use two LEFT JOINSwith aliases like:

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

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

See 2nd Demo

看 2nd Demo

回答by Ankit Bajpai

Try following query:-

尝试以下查询：-

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

Hope this helps you.

希望这对你有帮助。