内连接的对面 [英] Opposite of inner join

问题描述

内连接的反面是什么？对于表表Person （int PersonId，varchar PersoName，int AddrId），我想知道Person中的行有坏 AddrId 在地址表中没有一行。

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.

推荐答案

内连接的相反点是什么？

What will be the opposite of inner join?

一个OUTER联接，可以有三个选项：

An OUTER join, which can be of three options:

• LEFT


• RIGHT


• FULL

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

我想知道具有坏AddrId的Person中的行，在Address中没有一行表。

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

使用LEFT JOIN / IS NULL

Using LEFT JOIN/IS NULL

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

使用NOT EXISTS

Using NOT EXISTS

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

使用NOT IN

Using NOT IN

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

这篇关于内连接的对面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！