How to use an IN clause with multiple columns on the same data in postgresql?

Question

I have a query like this:

SELECT c1 from t WHERE c2 IN list1 AND c3 IN list1;

I want to combine this query to get something like this:

SELECT c1 from t WHERE c2 AND c3 IN list1;

Answer 1

You can use arrays and the operator <@(contained) , for example:

with my_table(name1, name2) as (
values ('Emily', 'Bob'), ('Ben', 'Jack'), ('Emily', 'James')
)

select *
from my_table
where array[name1, name2] <@ array['Emily', 'Jack', 'James', 'Chloe'];

 name1 | name2 
-------+-------
 Emily | James
(1 row)

See also: How to use the same list twice in a WHERE clause?

Answer 2

Suppose one Person table has Code and Email columns, so you can join this table with the list you want to iterate over.

*
P
(
('0000000264', '[e-mail]'), ('0000000262', '[e-mail]')
)
L (, )
cod = P.code AND mail = P.email