Checking whether an item does not exist in another table

Question

My tables are set up something like this:

table name: process
fields: name, id_string

table name: value_seach
fields: id_string, value

I want to construct a select statement that will display all of the process names (with it's respective id_string) that do not have an entry in value_search.

The id_string in the process table can be null, and still have a name, but those need to be excluded if possible. The id_string in value_search can never be null

How do I do this?

Answer 1

In general if you want rows that don't exist in another table, then LEFT JOIN the other table and WHERE ... IS NULL to a column on the second table. Also you mentioned that you don't want rows where process.id_string is NULL.

SELECT p.name, p.id_string
FROM
    process p
    LEFT JOIN value_search v
        ON v.id_string = p.id_string
WHERE
    v.id_string IS NULL
    AND p.id_string IS NOT NULL

This is known as an anti-join.