CODEWITHSUNDEEP
sqloracle-database
Horman Lewis
Horman Lewis

Reputation: 366

How to use "IN" operator with several columns?

Is it possible this query:

select *
from   table t
where  (t.key1, t.key2, t.key3) IN ('a','b','c','d')

Condition with several columns and several values.

Upvotes: 0

Views: 68

Answers (2)

Patrick Hofman
Patrick Hofman

Reputation: 157136

You have to split it in multiple statements. I didn't know whether you want to OR or AND, so I took AND in this samples:

select *
from   table t
where  (t.key1) IN ('a','b','c','d')
and    (t.key2) IN ('a','b','c','d')
and    (t.key3) IN ('a','b','c','d')

Or use a join and inline view:

select *
from   table t
join   ( select 'a' col
         from   dual
         union
         select 'b'
         from   dual
         union
         select 'c'
         from   dual
         union
         select 'd'
         from   dual
      ) x
on    t.key1 = x.col
and   t.key2 = x.col
and   t.key3 = x.col

Upvotes: 1

Thomas Ruiz
Thomas Ruiz

Reputation: 3661

select *
from   table t
where  t.key1 IN ('a','b','c','d') OR
       t.key2 IN ('a','b','c','d') OR
       t.key3 IN ('a','b','c','d')

Upvotes: 1

Related Questions