How do I find unused records using an outer join?

Question

Given, the following tables:

+--------------+
| CLASSES      |
+==============+
| name | room  |
+------+-------+
| 1A   | A1.01 |
| 1B   | NULL  |
| 1C   | A1.03 |
+------+-------+

+-------+
| ROOMS |
+=======+
| id    |
+-------+
| A1.01 |
| A1.02 |
| A1.03 |
+-------+

I expect the following result:

+--------------+
| UNUSED ROOMS |
+==============+
| id           |
+--------------+
| A1.02        |
+--------------+

How would you accomplish this using an Outer Join?

Answer 1

The following query does exactly what was asked for:

SELECT rooms.*
FROM rooms
  LEFT JOIN classes ON rooms.id = classes.room
WHERE
  classes.room IS NULL;

Trying to explain what is happening here:

After Left Joining classes on rooms, you get this result:

+--------------------------------------------+
| LEFT JOINED                                |
+============================================+
| rooms.id    | classes.name | classes.room  |
+-------------+--------------+---------------+
| A1.01       | 1A           | A1.01         |
| A1.02       | NULL         | NULL          |
| A1.03       | 1C           | A1.03         |
+-------------+--------------+---------------+

Since Left Join returns all records of the left table (which is rooms), even if rooms.id doesn't match classes.room since the latter is null, the rooms columns will still be returned. Mind that classes.name is also null, since the record was not matched. I know this is a bit confusing for beginners. (Like myself, I just wanted to share my experience. Sorry for sounding a bit confused.)

So as you can see, all that is needed to be done from here is to filter out the classes where room is null.

+--------------+
| UNUSED ROOMS |
+==============+
| id           |
+--------------+
| A1.02        |
+--------------+