Opposite of inner join

Question

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.

Answer 1

What will be the opposite of inner join?

An OUTER join, which can be of three options:

• LEFT
• RIGHT
• FULL

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.

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)