One of the things I love with SQL in general and not just PostgreSQL is the ability to say SELECT * FROM tbl WHERE column IN LIST, where the list is an input. But for a while, I have wanted to do the revers SELECT * FROM LIST WHERE LIST. Element IN (SELECT column FROM tbl) and I could not figure out how to do that. However, I could not find a good way for how to do this UNTIL NOW.

We have a table with a column called fingerprint, and we want to check if for each fingerprint in a list if the element is in the table tbl. Just for fun, let us say that the table fingerprints make up [1, 3, 5, 7] and the list of fingerprints we want to check with is [1, 2, 4, 5, 8] and we want to know what fingerprints from the list is in tbl, then we can formulate the query;

SELECT * FROM (values (1), (2), (4), (5), (8)) as v(fingerprint) 
    WHERE v.fingerprint IN (SELECT fingerprint FROM tbl)

This will give us the result [1, 5] and we can ask the inverse what elements of the list is not in tbl

SELECT * FROM (values (1), (2), (4), (5), (8)) as v(fingerprint) 
    WHERE v.fingerprint NOT IN (SELECT fingerprint FROM tbl)

For me, both of these queries are super powerful, and I will add the ability to express these queries QueryC++

./Lars