Finding records that are not there

[Alan Penny]

This is one of those "inner temple secrets" of SQL. If you can find the
records that are not there, then your training is complete:

How do you do this?

The crux of this technique is to use "left outer join". This type of join returns records that match, but also returns records in the master table that don't have a matching record.

Imagine the following 2 tables:

[panel_map]
panel_id, test_id
PX01,     T101
PX01,     T102
PX01,     T103
PX02,     Z100
PX02,     Z101

[tests]
test_id,  test_name
T101, Test 101
T102, Test 102
T103, Test 103
Z100, Z-Test 100
Z101, Z-Test 101
Z102, Z-Test 102

The test Z102 does not appear in panel_map, can we figure that out with a query?
Yes;

   SELECT t.test_id
   FROM tests AS t
        LEFT OUTER JOIN panel_map AS pm ON t.test_id = pm.test_id
   WHERE t.test_name IS NULL;

This returns all the records in tests, and any matching records in
panel_map that match on test_id. Looking for "test_name" being null
will find tests that don't appear in panel_map.

An alternative way of doing this is conceptually more straight forward:

    SELECT t.test_id
    FROM tests AS t
    WHERE (SELECT COUNT(*)
           FROM panel_map AS pm
           WHERE pm.test_id = t.test_id) = 0

In the above, if the count of occurrences in panel_map is zero, then that
test is of interest. This will yield the same result as the outer join
technique.

Which one is faster? That will depend on how your database engine optimizes
queries.