SQL: Find Items in Table A Not in Table B

Goal: Find records from Table A (Students) that do not exist in Table B (Rooms)

Prerequisites: 2 Tables with relational data, Ability to run SQL Queries

There is often times you wish to find items in one table or query that are not in another table or query. This is commonly referred to amongst the sql community as a problem referred to as "A not in B". In this example I want to find any Student who does not have a room assigned to them. Some other examples that you might think of may be, finding invoices that don't have any payments, or perhaps find customers who don't have any orders. Regardless of the specifics, it is generally very simple to find records in one table who do not have any associated record in another table.

Here are my 2 tables:

Students

uid firstName lastName
--- --------- --------
1   Joe      Blow
2   Nancy    Drew
3   Johnny   Appleseed

Rooms

roomid uid building
------ --- --------
1      1   Monroe
2      3   Washington

Now in order to find student names who do not have a room on campus we can use the query below:

SELECT firstName, lastName
FROM students LEFT JOIN rooms ON students.uid=rooms.uid
WHERE rooms.uid IS NULL

which would return:

firstName lastName
--------- ---------
Nancy     Drew

This is a great query to have handy as these types of situations are extremely common and has a high use case for application developers and reports.