Is there a way to write a prepared statement where a value is compared to another value in a condition and I don't know, whether this value is NULL
or not.
SELECT `foo` FROM `bar` WHERE `a1` = :a1 AND `a2` = :a2
If I would use this prepared statement with a1 => null
and a2 => 42
, then the resulting query would be:
SELECT `foo` FROM `bar` WHERE `a1` = NULL AND `a2` = '42'
This is, of course, not what I want. I would need this in that case:
SELECT `foo` FROM `bar` WHERE `a1` IS NULL AND `a2` = '42'
^^
Both a1
and a2
are nullable. I don't want to define 4 prepare statements:
-- I would use this, if both values are not null
SELECT `foo` FROM `bar` WHERE `a1` = :a1 AND `a2` = :a2
-- and this, if the expected value of a1 is null
SELECT `foo` FROM `bar` WHERE `a1` IS NULL AND `a2` = :a2
-- and this, if the expected value of a2 is null
SELECT `foo` FROM `bar` WHERE `a1` = :a1 AND `a2` IS NULL
-- and this, if I would expect both values to be null
SELECT `foo` FROM `bar` WHERE `a1` IS NULL AND `a2` IS NULL
See Question&Answers more detail:os