How to do an "inverse join"

Databases
1

So, on a normal join, you join together rows from two different tables that have a common id (or whatever you’re relating with).

I’d like to do something a little different: I want to get all the rows in a certain table (table A) that do NOT share an ID with any of the rows in another table (table B).

In case that’s unclear, I’ll be a bit more specific: I have a table (A) of ALL POSSIBLE time periods, and another table (B) of time periods that have already been reserved. I would like to show all AVAILABLE time periods (meaning, the time periods that are in A and NOT in B).

I’m sure this is super easy and I just missed something in my books. Thanks in advance!

2 
SELECT ...
  FROM A
LEFT OUTER
  JOIN B
    ON B.timeperiod = A.timeperiod
 WHERE B.timeperiod IS NULL
3

Perfect, thanks so much. As far as understanding this goes, I’m a little iffy.

I understand what a LEFT OUTER JOIN is; it differs from a normal INNER join in that, even if there are no matches in table B, you still get the result back from table A.

However, the last line seems to be the key, and I don’t quite understand what’s going on there. If someone explained it a bit, I’d be very grateful, but no rush either way.

4

a LEFT OUTER JOIN basically returns all rows from table A, with or without matching rows from table B

when there is no matching row from B, then the columns from B are all set to NULL in the result row

those are the rows we want to keep, and that’s what the WHERE clause does

:slight_smile:

5

Awesome, thanks :slight_smile: