0

I have a sql statement where I check a column to make sure the word 'call' is not there like this:

 select * from TableName where PFSeat <> 'call'

But this statement will not include records where PFSeat is Null. In order to get all the records I need I have to change the statement to look like this

 select * from TableName where (PFSeat <> 'call' OR PFSeat Is Null)

Is there a reason why it is coded to where I dont get all the records I need from the first statement? What is the reason?

DannyBoy
  • 93
  • 7
  • 1
    Not all DBMS are created equal. Oracle, for example, does not return `null` with a `<>` – JohnHC Oct 13 '16 at 13:48
  • 4
    The reason is that `null` is a very special non-value that can't be compared using `=` or `<>`. This is how SQL was defined 30 years ago. But you can simplify the condition to `where PFSeat is distinct from 'call'` –  Oct 13 '16 at 13:52
  • By definition any operator other than IS NULL used with a NULL value is FALSE. And Oracle is not an exception to this rule. – Lluis Martinez Oct 13 '16 at 13:53
  • @Jonast92 That doesn't explain why `WHERE Column <> 'Value'` doesn't include `NULL` values. – Siyual Oct 13 '16 at 13:53
  • Not any dbms should evaluate `<> null` as true! – jarlh Oct 13 '16 at 13:55

2 Answers2

1

You could (and should) read the first statement as "all rows where PFSeat has a value, and that value is not 'call'".
It results from a choice made several decades ago that NULL is not a value, but the absence of a value. Most if not all DBMS adhere to that rule, otherwise many queries would start producing unexpected results.

So NULL is not a value, it is a property meaning that no value is present.
Many clauses in SQL where any part or parameter is NULL will produce NULL, by definition.

Peter B
  • 22,460
  • 5
  • 32
  • 69
0

Have a look at SQL not displaying null values on a not equals query? . When comparing a record with PFSeat set to NULL using PFSeat <> 'call' the result is not TRUE. The result is NULL and so you do not see those records. @PeterB is right this is because NULL is not a value.

Also have a look at Why NULL never compares false to anything in SQL

Community
  • 1
  • 1
Khetho Mtembo
  • 388
  • 6
  • 20