Categories
database sql

Opposite of inner join

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?

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)