I have a Spring boot app with custom queries.
I need to find all database rows according to condition - if :myFlag
is true
, field in database must be in a list (:values
here), or null
.
Can I use something like this:
select *
from test
where id<4
and (case
when :myFlag then val in (:values)
else val in (:values) or val is null
end)
For example, if I have rows
id | val
1 | 1
2 | null
3 | 3
values = [1,3]
, myFlag = true
, then the query must return rows with id=1,3
.myFlag = false
, then I need to retrieve rows with id=1,2,3
.You don't need a case here,
select ...
from ...
where ...
and (
value in (:values)
OR (:myFlag AND value IS NULL)
)