The difference between IS and = (Access) 1

[Genotix]

Hi guy's,

I've got a small question and I hope you can help me with it.

What is the difference between an IS and = operator in a SQL query.

For instance :

SELECT .... WHERE Date = NULL

gives NO RESULTS at all

SELECT .... WHERE Date IS NULL

gives all the records that have an empty field "Date".

Linux IS userfriendly.
It's only very selective about who it's friends are.

 

[collierd]

My take on NULLS are that you should try where ever possible to use IS NULL or IS NOT NULL

Logical operators (=, > etc) are dependant upon how ANSI_NULLS are set
If ANSI_NULLS are set to ON (Default) comparisons cannot be made if either value is NULL as it interprets this as unknown
If ANSI_NULLS are set to OFF a comparison can be made and it will work as it sees NULL's as NULL

IS NULL will always see NULL as NULL and therefore is able to make the comparison


Damian.

 

[Genotix]

Thanx for your explanation

Linux IS userfriendly.
It's only very selective about who it's friends are.

 

[SQLBill]

Genotix,

Just for future reference...this forum is for Microsoft SQL Server and not for Microsoft Access. There are forums dedicated to Microsoft Access.

-SQLBill

Posting advice: FAQ481-4875

SELECT 'forum'
CASE [SQL TYPE]
WHEN 'MySQL' THEN 'forum436'
WHEN 'Access' THEN 'forum700', 'forum701',
'forum702', 'forum703', 'forum704', 'forum181'
WHEN 'ORACLE' THEN 'forum185' 'forum186', 'forum759'
ELSE 'FORUM669'
END

 

[Genotix]

SQLBill -> Sorry, my bad!

In my Threadminder
Microsoft SQL Server: Programming
is right above
Microsoft: Access Forms
I suppose U just clicked the wrong one.

Linux IS userfriendly.
It's only very selective about who it's friends are.