CODEWITHSUNDEEP
google-bigquery
user444422
user444422

Reputation: 161

Multiple conditions in where clause, is there any way to simply it in BQ

I'm just checking whether any student has got 35 marks in any subject. Is there any way to simplify it like this where (phy,che,math,cs,eng) in (35)?

with temp_table as(
select "studentA" name,36 as phy, 67 as che,75 math,65 cs,64 eng union all
select "studentB" name,44, 57, 37, 73, 57 union all
select "studentC" name,94, 87, 48, 72, 63
)
select * from temp_table where phy=35 or che=35 or math=35 or cs=35 or eng=35

Upvotes: 0

Views: 812

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

with temp_table as(
select "studentA" name,36 as phy, 67 as che,75 math,65 cs,64 eng union all
select "studentB" name,44, 57, 37, 73, 57 union all
select "studentC" name,94, 87, 48, 72, 63
)
select * from temp_table where 35 IN (phy, che, math, cs, eng)

(Be aware that if any of the columns is NULL, however, the IN comparison will return NULL)

Upvotes: 1

Related Questions