Return values from IN list that have no match in the table

I have a query like below:

select case_id  from cases where case_id in  
('dddfc33c-71ef-4d8f-ad7c-506a11372d98',
 '414bf99f-2d44-4282-ac78-d82ea8754a78',
 '8f3e0be0-6c60-40ca-8683-d95d0294b223',
 '0434c8d3-0da3-40b6-b263-860432a44e4f');

If the IN condition matches 3 rows, I would like to get the 1 row which is in the IN condition list but not in the table.

Transform your input into a set somehow. Then apply one of the standard techniques to ...

• Select rows which are not present in other table

Here is a variant with LEFT JOIN / IS NULL.

SELECT t.case_id
FROM  (
   SELECT unnest('{dddfc33c-71ef-4d8f-ad7c-506a11372d98
                  ,414bf99f-2d44-4282-ac78-d82ea8754a78
                  ,8f3e0be0-6c60-40ca-8683-d95d0294b223
                  ,0434c8d3-0da3-40b6-b263-860432a44e4f}'::uuid[])
   ) t(case_id)
LEFT   JOIN cases c USING (case_id)
WHERE  c.case_id IS NULL;

EXCEPT (better EXCEPT ALL !) or NOT EXISTS have already been mentioned and should give similar performance. The essential part is to have an index on codes(code_id) and use the matching data type.

Your sample values are valid UUIDs, so cases.case_id should have the data type uuid (!). Then pass uuid[] or cast in Postgres.

It's typically simplest to provide a single array. The benefit over a list (like in your original IN list) or a set (like in a VALUES expression): it can easily be passed as single parameter to a prepared statement or stored function. Like:

PREPARE qry1(uuid[]) AS 
SELECT t.case_id
FROM  (SELECT unnest($1)) t(case_id)
LEFT   JOIN cases c USING (case_id)
WHERE  c.case_id IS NULL;

EXECUTE qry1('{dddfc33c-71ef-4d8f-ad7c-506a11372d98
              ,414bf99f-2d44-4282-ac78-d82ea8754a78
              ,8f3e0be0-6c60-40ca-8683-d95d0294b223
              ,0434c8d3-0da3-40b6-b263-860432a44e4f}');

StackExchange Database Administrators Q#317290, answer score: 2