difference between COMM IS NULL and COMM = NULL

Question

I have a job interview they asked me what is the difference between is null and = null in sql

for example,

select * from emp where comm = null

OR

select * from emp where comm is null

in SQL Server if we set SET ANSI_NULLS ON the COMM =NULL does not show any result, can any one explain this, thanks

Answer 1

Hi @ali zubair

Here is a really nice answer to your question:

What's the difference between " = null" and " IS NULL"?

https://stackoverflow.com/questions/2749044/whats-the-difference-between-null-and-is-null

In a WHERE clause, column=null will never be true, it is not valid to use null this way, you need to say column IS NULL or column IS NOT NULL. This speaks to the special nature of NULL, it is not a value to check for equality of, it is an unknown value, so you need to use the IS or IS NOT syntax.

You may assign something the NULL value using the = equal. For example: UPDATE TableX SET Column=NULL...

(Answered by "anonymous user")

-----------------------------

If this is helpful please accept answer.

Answer 2

A comparison in SQL may return TRUE, FALSE, or UNKNOWN. With the equality operator, the result is is TRUE when values are the same, FALSE when different, and UNKNOWN if either or both values NULL.

NULL values are not returned because the WHERE expression result is UNKNOWN instead of TRUE for every row due to the NULL literal. To check for NULL in SQL, one must use the IS NULL operator instead of =:

Answer 3

Where did you set this?

SET ANSI_NULLS ON

Can you post the code (sample to show us what you got).