something about ansi_nulls

Question

Hi I have a question that we always say that null =null is false ,I want to know that when the ansi_nulls is off this statement which is "null=null" is also false? thanks

Answer 1

With ANSI_NULLS OFF, NULL = NULL evaluates to TRUE.

With ANSI_NULLS ON (the default), NULL = NULL evaluates to NULL.

NULL IS NULL always evaluates to TRUE.

However, in MySQL, you cannot turn off ANSI_NULLS. You are probably thinking of MS SQL Server.

Future versions of MS SQL Server won't support ANSI_NULLS OFF, so I wouldn't use it.

You should leave ANSI_NULLS ON and use IS NULL to evaluate if something IS NULL.

If you're having problems remembering how NULL works by default (ANSI_NULLS ON), you should think of NULL as "unknown". For example, if there are two strangers in the room, their names are NULL. If your query is, "Are their names the same?" Your answer IS NULL.

Now, let's say Bob is in the room with just one stranger, whose name IS NULL. Again the answer to your query "Are their names the same?" IS NULL. Note that if you compare anything to NULL, your answer IS NULL.

Answer 2

When ansi_nulls is off, null=null will return true.

Eg.

set ansi_nulls off
select 1 where null=null

Gives you:

1