Home > Blockchain >  How to condense 'where' clause in SQL when searching for same values across multiple colum
How to condense 'where' clause in SQL when searching for same values across multiple colum

Time:11-19

I currently have this code which is looking for the same values in multiple columns, is there a way to condense this code so it is not so lengthy?

SELECT DISTINCT
    client_ID
FROM 
    df1
WHERE 
    code_1 IN ( 'A', 'B', 'C', 'D', 'E')
    OR code_2 IN ( 'A', 'B', 'C', 'D', 'E')
    OR code_3 IN ( 'A', 'B', 'C', 'D', 'E')
    OR code_4 IN ( 'A', 'B', 'C', 'D', 'E')
    OR code_5 IN ( 'A', 'B', 'C', 'D', 'E')

My attempt which doesn't seem to be working:

SELECT DISTINCT
    client_ID
FROM 
    df1
WHERE 
    (code_1 OR code_2 OR code_3 OR code_4 OR code_5 IN ( 'A', 'B', 'C', 'D', 'E'))

CodePudding user response:

In SQL Server, where tuple equality is not supported, an alternative to the long is of OR is to unpivot the columns to rows in an exists subquery:

select client_id
from df1
where exists (
    select 1 from ( values (code_1), (code_2), (code_3), (code_4), (code_5) ) x(code)
    where x.code in ( 'A', 'B', 'C', 'D', 'E')
)
  • Related