CODEWITHSUNDEEP
sqldynamic-sql
Julio
Julio

Reputation: 401

SQL - WHERE query with a mix of XOR and AND on two variables

I have two column names foo and bar from a table. I want to return any row that returns true on the conditions put on X and Y. I want to combine the conditions in the table below into the where section of my select query.

   X      |    Y     |  conditions
----------+----------+----------------
   null   |   null   | 
 not null |   null   |  foo=X
   null   | not null |  bar=Y
 not null | not null |  foo=X and bar=Y

Here X and Y are variables that get substituted into the query. Both of them are never null at the same time, so we don't care about the first condition. But if one is null the condition checks the other (XOR). If both are not null the condition checks on both (AND). This is the query I have so far, but is there anything simpler and cleaner for the where portion?

select
      ...
where
     (X is not null and Y is not null and
      foo=X and bar=Y)
  or (X is not null and
      Y is null and foo=X)
  or (X is null and
      Y is not null and bar=Y)

EDIT Note: foo and bar may have null values.

Upvotes: 3

Views: 97

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726839

You can simplify the condition by observing that the pair X, foo is independent of the pair Y, bar. Therefore, you can simplify the condition as follows:

WHERE (X is NULL OR foo=X) AND (Y is NULL OR bar=Y)

Upvotes: 3

Related Questions