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
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
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++