difference between 'is null' and '= null'

[BubikolRamios]

Both works, with different result. What is the logic ?

[Celtboy]

You'll want to use IS NULL. = NULL is an arithmetic operator that can have some odd results:

http://dev.mysql.com/doc/refman/5.0/...with-null.html

[BubikolRamios]

Yes, I was convinced, until special case query(where I got a bit confused (- that '= null' does not exists at all.

But see this dilema:
tab1

id      
1
2

tab2

id      varcharfiield
1       null

I would say here in condition :

select * from tab2 where varcharfield = null'

and in this case:

select * from tab1 left join tab2 on tab1.id = tab2.id were tab1.id is null

coz, in first case null is actualy there, in table, in second it is not, it is produced by query.

Do you mean 'is null' should work in any upper case & in any case at all ?

[felgall]

In databases NULL means the value is unknown and therefore NULL == NULL is false since two unknowns are rarely going to be the same value if you ever find out what they are. So testing for = NULL will never find a match as NULL is never equal to anything.

Once you move out of the database null has a completely different meaning and two fields set to null are equal. The NULL in the database doesn't extract as a meaningful value though because outside the database there is no corresponding value meaning unknown to give the field.

[Old Pedant]

Originally Posted by felgall

Once you move out of the database null has a completely different meaning and two fields set to null are equal.

NOT TRUE AT ALL!

That may be true in PHP and JavaScript, but it is *NOT* true in some computer languages.

This is a table of the comparison operators from VBScript, just for example:
http://msdn.microsoft.com/en-us/libr...(v=vs.84).aspx

VB and VB.NET are essentially the same: They use the "IS" operator for testing object equality, and two nulls will not match using anything except the IS operator. Pascal, in most variations I've seen, is the same.

Only languages that don't seem to have a built in "isNull" or equivalent function/operator seem to treat a pair of nulls as being equal. It *IS* misleading to say that a pair of nulls are equal, for the reason Felgall gave: NULL means "unknown" and how can you know that a pair of unknowns are equal. I think this is a case where VB/VB.NET/VBScript do it better than JavaScript, C, C++, et al. If nothing else, how does it make sense to ask

    if ( a <= null )
or
    if  ( b > null )

??? Clearly, those are nonsense questions. But stupid JavaScript, in these conditions, treats null the same as zero. UGH! I think that's uglier than pig snot.

[felgall]

Originally Posted by Old Pedant

NOT TRUE AT ALL!

That may be true in PHP and JavaScript, but it is *NOT* true in some computer languages.

I should have said CAN HAVE rather than HAS. (Just about all the languages I know treat null as meaning something completely different from what it means in any other language).

Originally Posted by Old Pedant

But stupid JavaScript, in these conditions, treats null the same as zero.

That's why you should always use === and !== in comparisons in JavaScript. Null in JavaScript is a placeholder for an object that has not yet been created and so two placeholders can be considered to have the same value hence null === null in JavaScript but comparing to anything else using the correct comparison operators will return false.

In JavaScript (null === 0) will return false because it does not treat them the same unless you are sloppy and use == which first tries to convert both values to the same type and both null and 0 can be converted to false giving false == false which is true..

[Old Pedant]

YES! I think I wish that JS disallowed using null with == and !=. I certainly admit to sloppily using them way too often.

I *REALLY* dislike code that does stuff such as

if ( ! document.getElementsByClassName ) { ... }

because it implies that a method reference is a boolean value. I think you should *HAVE* to write

if ( document.getElementsByClassName === null ) { ... }

Ah, well.

[felgall]

Originally Posted by Old Pedant

I think you should *HAVE* to write

if ( document.getElementsByClassName === null ) { ... }

There is an error in your logic there. The mention of document.getElementsByClassName would presumably create it if it doesn't exist. As it now exists without having had a value allocated it now has a value of undefined and not null. When using === undefined !== null.

The correct way to write it is:

if (typeof document.getElementsByClassName === undefined ) { ... }

[Old Pedant]

SORRY! Senior moment. I knew that.