SQL 与内连接相反

Opposite of inner join

提问by fastcodejava

What will be the opposite of inner join? For a table table Person (int PersonId, varchar PersoName, int AddrId), I want to know the rows in Person with bad AddrIdwhich don't have a row in the Addresstable.

内连接的反面是什么？对于表表 Person (int PersonId, varchar PersoName, int AddrId)，我想知道表中没有行的 Person 中AddrId的行Address。

回答by OMG Ponies

What will be the opposite of inner join?

内连接的反面是什么？

An OUTER join, which can be of three options:

一个 OUTER 连接，可以是三个选项：

• LEFT
• RIGHT
• FULL

• 剩下
• 对
• 满的

This is a good visual representation of JOINs

这是 JOIN 的一个很好的视觉表示

I want to know the rows in Person with bad AddrId which don't have a row in the Address table.

我想知道地址表中没有行的具有错误 AddrId 的 Person 中的行。

Using LEFT JOIN/IS NULL

使用 LEFT JOIN/IS NULL

   SELECT p.*
     FROM PERSON p
LEFT JOIN ADDRESS a ON a.addrid = p.addrid
    WHERE a.addrid IS NULL

Using NOT EXISTS

使用不存在

SELECT p.*
  FROM PERSON p
 WHERE NOT EXISTS(SELECT NULL
                    FROM ADDRESS a
                   WHERE a.addrid = p.addrid)

Using NOT IN

使用 NOT IN

SELECT p.*
  FROM PERSON p
 WHERE p.addrid NOT IN (SELECT a.addrid
                          FROM ADDRESS a)

回答by Thomas

An Inner join is not diametric to an Outer Join. They serve different purposes. However, a common pattern to find rows from one table that do not exist in another is to use an Outer Join:

内连接与外连接不同。它们用于不同的目的。但是，从一个表中查找另一个表中不存在的行的常见模式是使用外部联接：

Select ...
From Table1
    Left Join Table2
        On Table2.ForeignKeyCol = Table1.PrimaryKeyCol
Where Table2.PrimaryKeyCol Is Null

This returns all rows from Table1 and any matching rows from Table2 such that if a given Table1 row has no Table2 match, a null for the Table2 columns are returned. By then requiring that a non-nullable column (Table2.PrimaryKeyCol) be Null, I will get all rows from Table1 that do not exist in Table2. Using your example table names we would have something like:

这将返回 Table1 中的所有行和 Table2 中的任何匹配行，这样如果给定的 Table1 行没有 Table2 匹配项，则返回 Table2 列的空值。然后要求不可为空的列 (Table2.PrimaryKeyCol) 为 Null，我将从 Table1 中获取 Table2 中不存在的所有行。使用您的示例表名，我们将有以下内容：

Select ...
From Person
    Left Join Address
        On Address.PersonId = Person.Id
Where Address.Id Is Null

回答by Zugwalt

If you consider an inner join as the rows of two tables that meet a certain condition, then the opposite would be the rows in eithertable that don't.

如果您将内部联接视为满足特定条件的两个表的行，则相反的是任一表中不满足的行。

For example the following would select all people with addresses in the address table:

例如，以下将选择地址表中具有地址的所有人员：

SELECT p.PersonName, a.Address
FROM people p
JOIN addresses a
    ON p.addressId = a.addressId

I imagine the "opposite" of this would be to select all of the people without addresses and all addresses without people. However this doesn't seem to be what you are asking, you seem to be interested in just one component of this: all the people without an address in the addresses table.

我想与此相反的是选择所有没有地址的人和所有没有人的地址。然而，这似乎不是您要问的，您似乎只对其中的一个组成部分感兴趣：地址表中没有地址的所有人。

For this a left join would be best:

为此，最好使用左连接：

SELECT p.PersonName
FROM people p
LEFT JOIN addresses a
   ON p.addressId = a.addressId
WHERE a.addressId IS NULL

Note that often some prefer to write it differently as in their opinion it is more readable (however in my experience with large tables it performs worse than the above way):

请注意，通常有些人更喜欢以不同的方式编写它，因为他们认为它更具可读性（但是根据我对大表的经验，它的性能比上述方式更差）：

SELECT PersonName
FROM people
WHERE addressId NOT IN (SELECT addressId FROM addresses)

回答by Andre Vianna

I think the best solution would be using EXISTS. Like this:

我认为最好的解决方案是使用EXISTS. 像这样：

SELECT * FROM Person P
WHERE P.AddrId IS NOT NULL 
  AND NOT EXISTS ( SELECT 1 FROM Address A WHERE A.AddrId = P.AddrId )

The query above will return every person that the AddrId is set but does not have a corresponding record in the Address table.

上面的查询将返回设置了 AddrId 但在 Address 表中没有相应记录的每个人。

Obs.:Use the constant 1 in the EXISTSquery to avoid table access.

观察：在EXISTS查询中使用常量 1来避免表访问。