CODEWITHSUNDEEP
sqlsql-servert-sql
Seemorecode
Seemorecode

Reputation: 13

SQL putting an AND within an OR

I'm trying to add a condition based on the following:

where new.Number <> old.Number
and new.Number is not null

this selects rows that have a changed 'number' so long as the 'new number' is not blank

I want to add another condition that can be used as the other side of an 'OR' but am confused as how to do this, my brain says:

where (((new.Number <> old.Number) and new.Number is not null) or (new condition))

So we would select rows that pass the previous conditions OR pass the new condition but this doesn't seem to work.

Upvotes: 0

Views: 106

Answers (4)

Salman Arshad
Salman Arshad

Reputation: 272396

Revised Answer

Assuming that you DO want to include results that involve NULL you have two options:

Option 1

Use the OR clause intelligently:

WHERE old.Number IS NULL OR old.Number <> new.Number

This matches if:

  1. Old number is NULL
  2. Or both numbers are not NULL and different

This does not match in all other cases including:

  1. Old number is not NULL
  2. And new number is NULL or both numbers are same

Option 2

Use ISNULL function to compare NULL values like this:

WHERE ISNULL(new.Number, -1) <> ISNULL(old.number, -1)

This matches if:

  1. Both numbers are different (e.g. 1 <> 2)
  2. Or one of the numbers is NULL (e.g. 1 <> NULL and NULL <> 2)

This does not match if:

  1. Both numbers are same (e.g. 1 <> 1)
  2. Or both numbers are NULL (e.g. NULL <> NULL)

In any case, ( and ) could be used when you mix AND and OR clauses.

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

As was said, you can consolidate the original condition to remove the is not null. However, I still prefer to be explicit about the intent of this, and therefore I prefer this pattern:

coalesce(new.Number, old.Number) <> old.Number

This makes it clearer for future maintainers that you had explicitly considered what you wanted to do with NULL values, and it still condenses you down to one expression that you can easily combine with ar OR.

But in the future, if you have more complex conditions that won't consolidate, just remember your parentheses:

WHERE ( new.Number <> old.Number and new.Number is not null )
     OR ( other condition )

Upvotes: 0

Anu
Anu

Reputation: 16

You can try this... WHERE (new.Number <> old.Number AND new.Number IS NOT NULL) OR (new condition)

Upvotes: 0

Pieter21
Pieter21

Reputation: 1845

Maybe you can rewrite the first condition into something like WHERE new.Number NOT IN (null, old.Number) to have it as a single expression, which could make the 'OR' easier.

Also, are you absolutely sure that the function is correct without the new condition? For us there is no way of checking, because there is no context about the tables you select.

Upvotes: 0

Related Questions