与内部连接相反的是

本文是小编为大家收集整理的关于与内部连接相反的是的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。

中文
English

问题描述

内部联接的对立面是什么?对于表格表人(int PersonId, varchar PersoName, int AddrId)，我想亲自知道AddrId AddrId Address表中没有行的行.

使用左JOIN/IS NULL

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

使用不存在

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

不使用

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

其他推荐答案

内部连接不与外部连接直径.他们有不同的目的.但是，从另一个表中找到行的常见模式是使用外部连接:

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

这将返回来自Table1的所有行，并从Table2返回所有匹配行，因此，如果给定的Table1行没有Table2匹配，则返回Table2列的null.到那时，要求不可用的列(table2.primarykeycol)为null，我将从表2中获取不存在的Table1的所有行.使用您的示例表名称，我们将有类似的东西:

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

其他推荐答案

如果您将内部连接视为符合特定条件的两个表的行，则相反的是表中的行.

例如，以下内容将在地址表中选择所有具有地址的人:

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

我想象的"相反"是选择所有没有人地址和所有地址的人.但是，这似乎并不是您要问的，您似乎对此仅感兴趣:所有没有地址的人在地址表中.

为此，左连接最好:

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

请注意，通常有些人喜欢以不同的方式编写它，因为他们认为它更可读性(但是，在我的大桌子经验中，它的性能比上述方式差):

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

本文地址：https://www.itbaoku.cn/post/597520.html

百度翻译此文有道翻译此文

问题描述

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 AddrId which don't have a row in the Address table.

Using 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

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

其他推荐答案

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:

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

其他推荐答案

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 either table 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)

上一篇：PostgreSQL的Ltree模块是否适合于线程评论？

下一篇：数据建模。超类型/次类型