0

Before select Status could be: X1, X2, NULL

SELECT * FROM surrtest t Where t.Status != „X2“

It’s deleting also rows where Status was null. But it shouldn’t.

So after query I’m only getting rows where status Is X1 but I also want the NULL rows.

Is it possible?

I don’t know if it’s possible. If not maybe there is a workaround where we replace the null values with empty strings? But I hope it is possible without changing the data.

Codernew
  • 15
  • 3

1 Answers1

1

This should work:

SELECT * FROM surrtest Where Status != ‘X2’ or Status is null

If that doesn’t work then there’s something weird going on with your database.

In most databases, a where clause evaluates to either true, false, or unknown. Any comparison with null will result in an unknown result. Since a where clause filters out all records that don't evaluate to true, you will not see records that evaluate to unknown, as well as false.

In your original query, the where clause was filtering out null records, because they evaluated to unknown. So, you have to add the extra part or Status is null to the where clause to include the null records.

Also, do NOT use or Status = null, since that will always evaluate to unknown, like mentioned before. You must use or Status is null.

user3163495
  • 2,425
  • 2
  • 26
  • 43
  • But why is it necessary to say OR Status is NULL? I mean why does it filtering out the null rows by default? – Codernew Feb 19 '23 at 17:39
  • It's the same reason why you have to use `Status is null` rather then `Status = null`. If you try to compare null values with `=` or `!=` or `<>` -- the result will always be null. E.g. `SELECT `X2` != null` does not yield `true` but `null`. – Kristian Ferkić Feb 19 '23 at 18:05
  • @Codernew I updated my answer with some information on why databases do this. – user3163495 Feb 19 '23 at 18:37