PostgreSQL – Show Values from List Not Returned by Query

postgresqlselect

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?

Best Answer

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

Related Question