1

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?

2 Answers 2

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

2
  • This works perfectly but seems unintuitive to me, particularly because the FROM clause becomes a list of single-item tuples, and also because I'm not sure where the variable vs comes from.
    – muad-dweeb
    May 18, 2020 at 21:37
  • But consider this fiddle with a NULL value in the table. May 19, 2020 at 0:42
1

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:

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.