0

I have a view V_BaseData_Extract in MS SQL server with one column Comments having null values as well having other textual values too. Column type is nvarcahr.

What's happening is that SQL returning same count for both below mentioned queries.

First Query:

select count(*)
  from V_BaseData_Extract where Comments 
  not in  ( '                                                                                                                                                                                               ' , '                                                                                                                               
                                                                  ')

Second Query:

  select count(1) from V_BaseData_Extract a where a.Comments is not null

Its handling null same as the string mentioned in my first query's where condition. What could be the reason behind that ? Am I missing something ?

Is null equivalent to some number of blank spaces ?

M Usama Alvi
  • 187
  • 1
  • 15
  • Also try `... where Comments in ( ' ...` – jarlh Feb 08 '21 at 16:09
  • That's not working. Its working with NOT only. – M Usama Alvi Feb 08 '21 at 16:10
  • Almost any comparison to `NULL` -- typically including `in` and `not in` return `NULL` when a `NULL` value is involved. – Gordon Linoff Feb 08 '21 at 16:10
  • I *assume* you have no rows where the value of `Comments` is a 0 length string, or entirely made up of white spaces; so the counts are the same. – Thom A Feb 08 '21 at 16:10
  • Not working? Returns 0 I suppose. – jarlh Feb 08 '21 at 16:10
  • @Larnu, Why would the count be same ? Null value rows should be returned in `first query` since I'm not `excluding null values` in where clause. – M Usama Alvi Feb 08 '21 at 16:12
  • 2
    *"null value rows should be returned in first query since I'm not excluding null values in where clause. "* Yes you are. `NULL NOT IN ('')` = Unknown != True. If you wanted `NULL` values you would need `NOT IN ('') OR Comments IS NULL` – Thom A Feb 08 '21 at 16:17
  • You simply have no empty strings stored. You can count all values either using IS NOT NULL, or using NOT IN (' '). – jarlh Feb 08 '21 at 16:25
  • @jarlh I can count using both conditions, but I would like to know why null values were not returned in first query. – M Usama Alvi Feb 08 '21 at 16:39
  • 1
    _null_ NOT IN (' ') evaluates to null, but only true is passed to count(). – jarlh Feb 08 '21 at 16:42
  • @jarlh Technically it's called `UNKNOWN`, but yes, `WHERE` only passes predicates that are `TRUE` and not `UNKNOWN` – Charlieface Feb 08 '21 at 16:47

1 Answers1

0

This is the correct explanation. Comments above helped me look into right direction and search relevant terms.

NULL values inside NOT IN clause

M Usama Alvi
  • 187
  • 1
  • 15