Imad Imad - 5 months ago 16
SQL Question

column = null not woking

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?

Answer Source

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.