Show values from list that are NOT returned by query

Question

I have been given a list of values that may or may not be found in a table's column. I just need the subset of the list that is NOT found in a SELECT query.

Pretending that the list I was given is: 'something_a', 'something_b', 'something_c', 'something_d'...

My intuition is to do something like this:

SELECT item FROM ('something_a', 'something_b', 'something_c', 'something_d') 
WHERE item NOT IN (
    SELECT appropriate_column FROM some_table 
    WHERE appropriate_column IN ('something_a', 'something_b', 'something_c', 'something_d') 
);

I would expect to get back the following result if say two of the items are missing from the table:

item
---
something_a
something_c

What is a valid SQL approach to accomplishing what I'm after?

Answer 1

You can use the VALUES clause to create a "constant table":

Let's assume this is your table:

CREATE TABLE some_table
(
    appropriate_column TEXT
) ;

populated with:

INSERT INTO 
    some_table (appropriate_column)
VALUES 
     ('something_b'),
     ('something_d') ;

This is how you would make the query you're looking for:

SELECT 
    v 
FROM
    (
    VALUES
        ('something_a'),
        ('something_b'),
        ('something_c'),
        ('something_d')
    ) AS vs(v)
WHERE
    v NOT IN 
    (SELECT 
        appropriate_column 
    FROM
        some_table
    ) ;

You can check everything at dbfiddle

Answer 2

Rather stay away from NOT IN. If the column isn't defined NOT NULL, a single NULL value can kill the query. I suggest one of these:

• Select rows which are not present in other table

You can pass your list as array for convenience. See:

• Optimizing a Postgres query with a large IN

SELECT *
FROM   unnest('{something_a, something_b, something_c, something_d}'::text[]) v(appropriate_column)
LEFT   JOIN tbl t USING (appropriate_column)
WHERE  t.appropriate_column IS NULL;

db<>fiddle here