Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to simulate COALESCE conditions in WHERE clause

Say I have the following:

SELECT    * 

FROM      Xyz 

WHERE     (@a IS NULL OR a = @a) AND
          (@b IS NULL OR b = @b) AND
          (@c IS NULL OR c = @c)

What I want to accomplish is to change the WHERE clause to use the conditions in a sort of COALESCE fashion where it would use the first condition that was not null or it ran out conditions.

In pseudo speak it would be something like:

WHERE    IF @a IS NOT NULL THEN
         (
             a = @a
         )
         ELSE IF @b IS NOT NULL THEN 
         (
             b = @b
         )
         ELSE IF @c IS NOT NULL THEN 
         (
             c = @c
         )

Is this possible?

like image 851
Code Maverick Avatar asked Dec 08 '22 10:12

Code Maverick


2 Answers

This will treat NULLs as wildcards:

SELECT *
FROM MyTable
WHERE COALESCE(
  NULLIF(@a,a),
  NULLIF(@b,b),
  NULLIF(@c,c)
) IS NULL

This will use the first non-null condition:

SELECT *
FROM MyTable
WHERE EXISTS (
  SELECT id,value
  FROM (VALUES (1,a),(2,b),(3,c)) t1(id,value)
  INTERSECT
  SELECT TOP 1 id,comparator
  FROM (VALUES (1,@a),(2,@b),(3,@c)) t(id,comparator)
  WHERE comparator IS NOT NULL
  ORDER BY id
)
like image 147
Anon Avatar answered Dec 14 '22 08:12

Anon


There may be a more elegant way, but this should respect the desired logic

where
(@a is not null and @a = a) or
(@a is null and @b is not null and @b = b) or
(@a is null and @b is null and @c is not null and @c = c)

which can be (a bit) simplified to:

where
(@a = a) or
(@a is null and @b = b) or
(@a is null and @b is null and @c = c)
like image 23
Raphaël Althaus Avatar answered Dec 14 '22 06:12

Raphaël Althaus