MySQL选择左连接为空的行

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相反，它只选择在连接中找到的行。如果左边连接存在，如何得到反面，忽略它并移动到下一行。希望我很清楚

6 个解决方案

#1

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 table2 you 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;

#2

One of the best approach if you do not want to return any columns from table2 is 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进行了优化：它在找到第一个匹配记录后立即返回。

#3

Here is a query that returns only the rows where no correspondance has been found in both columns user_one and user_two of table2:

这是一个查询，它只返回在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

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

每列有一个关节（user_one和user_two），查询只返回没有匹配关节的行。

Hope this will help you.

希望这会帮助你。

#4

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

见演示

Or you could use two LEFT JOINS with 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

见第二次演示

#5

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.

希望这对你有所帮助。

#6

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

#1