0

I fired a query as 

SELECT * FROM tblEmp WHERE emp_id = 9737  AND mgr_id = NULL

and it dodn't return any value. Till yesterday it was doing so. What might be changed? We many people work on same server so there is chance that some one changed something.

To get the result I have to fire 

SELECT * FROM tblEmp WHERE emp_id = 9737  AND mgr_id IS NULL

which is giving proper output.

Since I am using this query in .Net I am afraid if it breaks when settings are reset as previous one. Will it?

Imad
  • 7,126
  • 12
  • 55
  • 112
  • 1
    AFAIK your original query should never have returned anything. The reason for this is that comparing something to `NULL` using equals is always `NULL`. And `emp_id = 9737 AND NULL` is also unknown. Lesson learned, use `IS NULL` and `IS NOT NULL` when comparing to `NULL`. – Tim Biegeleisen May 12 '17 at 05:32

2 Answers2

6

Setting the ANSI_NULLS feature of SQL Server to OFF will let you write mgr_id = NULL and evaluate it to TRUE.

However, it's bad practice to compare NULLs this way. Most RDBMSs won't allow it, most DBAs won't write queries that way, and the ANSI_NULLS setting has been deprecated for quite awhile and will be forced ON in the future.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • Nice explanation +1 ... and the only one which explains how any results could have been coming through – Tim Biegeleisen May 12 '17 at 05:33
  • Thanks for the descriptive answer. But what if ansi_nulls set to off again. will `mgr_id is null` will work as same? – Imad May 12 '17 at 05:51
  • @Imad The ANSI_NULLS setting will default to whatever the setting is on the database. You can modify the default setting using [ALTER DATABASE SET ANSI_NULLS](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options) to default it off. You can use [sp_configure user options](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option) to configure it per-user. Otherwise, you will need to issue `SET ANSI_NULLS OFF;` each session with, for example, `ExecuteNonQuery();`. – Bacon Bits May 12 '17 at 17:39
  • @Imad To be perfectly clear, I never set this setting off, so I'm not 100% certain of what you need to set to force it off for all connections. It's entirely possible for a database provider to set the value on manually. As far as I'm aware, there's no way to prohibit a connection from ever enabling it. – Bacon Bits May 12 '17 at 17:41
0

Null is not a value and so you cannot use = operator. You should use is null function for it to work.

halfer
  • 19,824
  • 17
  • 99
  • 186
Jim Macaulay
  • 4,709
  • 4
  • 28
  • 53