I fired a query as
SELECT * FROM tblEmp WHERE emp_id = 9737 AND mgr_id = NULL
SELECT * FROM tblEmp WHERE emp_id = 9737 AND mgr_id IS NULL
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.