Have Geek Brain

Something about ansi_nulls

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

+2


a source to share


2 answers


C ANSI_NULLS OFF

is NULL = NULL

evaluated as TRUE

.

C ANSI_NULLS ON

(default), NULL = NULL

evaluates to NULL

.

NULL IS NULL

always evaluates as TRUE

.

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



Future versions of MS SQL Server do not support ANSI_NULLS OFF

, so I would not use it.

You should leave ANSI_NULLS ON

and use IS NULL

to evaluate that something is NULL.

If you are having trouble 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 a room with one stranger whose name is NULL. Again, the answer to your query "Are their names the same?" NULL. Please note, if you compare anything with NULL, your answer will be NULL.

+10


a source


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

Eg.

set ansi_nulls off
select 1 where null=null


Gives you:

1
+1


a source






More articles:


All Articles