How to use IN clause with multiple columns on 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;

If list1 is quite long, and you want to omit mentioning it twice within your query, you might consider using a temp table and subqueries. This might also be positive for the performance. — Uwe Allner
– Uwe Allner, Commented Mar 15, 2017 at 13:00
@LaurenzAlbe I think the first way is not good and I wanted something like second one — mohammad
– mohammad, Commented Apr 26, 2017 at 4:52

Community · Accepted Answer · 2017-05-23 10:31:23Z

8

You can use arrays and the operator <@ (is contained by), e.g.:

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 same list twice in WHERE clause?

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Mar 15, 2017 at 12:40

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related