CODEWITHSUNDEEP
sql
NBC
NBC

Reputation: 1698

Conditional statement inside where clause to account for null values

I have a table shaped like so:

ID  | Value  | Value_Comp
101 |  3     |   2
102 |  2     |   4 
103 |  7     |   NULL
104 |  1     |   4

I want to select all IDs where Value < Value_Comp, but if there's a NULL value, then select the data if value > 5

So in this scenario, IDs 102, 103, and 104 would be selected.

Edit: I have a ton of other where conditions. Is there an easy way to write this so I don't have to repeat all of them twice?

Upvotes: 0

Views: 32

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

Try this:

select *
from t
where (value_comp is null and value > 5)
    or value < value_comp;

You can put the two condition in brackets and then add other conditions in the where clause:

select *
from t
where ((value_comp is null and value > 5) or value < value_comp)
     and <condition 1> and <condition 2> . . .;

Upvotes: 2

Piotr Rogowski
Piotr Rogowski

Reputation: 3890

SELECT ID
FROM your_table
WHERE Value < Value_Comp OR (Value_Comp IS NULL AND Value > 5)

Upvotes: 1

Related Questions