How to use the same list twice in the WHERE clause?

Question

My SQL query contains a WHERE similar to this:

 WHERE name1 in ('Emily', 'Jack', 'James', 'Chloe') OR name2 in ('Emily', 'Jack', 'James', 'Chloe') 

Note that the same list appears twice, which is pretty unsatisfactory (and my real list is actually longer).

What would be the best way to write this query?

Answer 1

You can use arrays and the overlap operator && , for example:

 with my_table(name1, name2) as ( values ('Emily', 'Bob'), ('Ben', 'Jack'), ('Bob', 'Ben') ) select * from my_table where array[name1, name2] && array['Emily', 'Jack', 'James', 'Chloe']; name1 | name2 -------+------- Emily | Bob Ben | Jack (2 rows) 

Answer 2

Use a generic table expression:

 with to_check (name) as ( values ('Emily'), ('Jack'), ('James'), ('Chloe') ) select ... from ... WHERE name1 in (select name from to_check) OR name2 in (select name from to_check); 

Answer 3

One method is regular expressions:

 WHERE (name1 || ' ' || name2) ~ 'Emily|Jack|James|Chloe' 

This is not exactly the same logic (the name "Emily Doe" will match that logic, but not the first one), but it can do what you want.