内部联盟的对立面是什么？对于表表Person(int PersonId,varchar PersoName,int AddrId),我想知道Person中具有不良地址表中没有行的错误AddrId的行.
解决方法
What will be the opposite of inner join?
一个OUTER加入,可以有三个选项：
>左
>右
>满
This is a good visual representation of JOINs
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
SELECT p.*
FROM PERSON p
LEFT JOIN ADDRESS a ON a.addrid = p.addrid
WHERE a.addrid IS NULL
使用NOT EXISTS
SELECT p.*
FROM PERSON p
WHERE NOT EXISTS(SELECT NULL
FROM ADDRESS a
WHERE a.addrid = p.addrid)
使用NOT IN
SELECT p.*
FROM PERSON p
WHERE p.addrid NOT IN (SELECT a.addrid
FROM ADDRESS a)
