Difference between IS and =

Does anyone know the difference between the operators "IS" and "=" ?
I'm new to SQL, so...
Why for querying something like "... AND filed IS NULL" I have to use IS instead of = ?
Thanks anyway.Originally posted by 435 Gavea
Does anyone know the difference between the operators "IS" and "=" ?

I'm new to SQL, so...

Why for querying something like "... AND filed IS NULL" I have to use IS instead of = ?

Thanks anyway.

Hi The main advantage of IS is that you can use it for nulls, but you can't use '=' to check for nulls.

regards,
Chalam N|||It is a feature of the "3 valued logic" used by SQL. This says that any logical condition may have 3 possible values: TRUE, FALSE or NULL. NULL means "unknown" or "not applicable" or whatever.

Also, NULL is not a value like 0, 1 or 'hello', it is the "absence" of a value. And NULL is not equal to anything, not even to NULL. It just is NULL.

The result of the expression "field = NULL" is always NULL, regardless of whether field contains a NULL or a value. That may not make much sense on the face of it, but it does if you think the way SQL thinks:

Suppose I have 2 employee records:

insert into employee (name, salary) values ('John', NULL);
insert into employee (name, salary) values ('Mary', NULL);

The NULL value for salary in both cases doesn't mean they earn the same salary, it means we don't know what it is at the moment. So the query:

select name
from employee
where name != 'John'
and salary = (select salary from employee where name='John');

... should not return 'Mary'. We don't know their salaries, so we can't claim that they earn the same salary.

Nor do we know that they don't earn the same salary! Maybe they do, for all we know. Hence this query must return no rows either:

select name
from employee
where name != 'John'
and salary != (select salary from employee where name='John');

In other words, NULL is neither equal to, nor not equal to, NULL! It just is NULL...|||Now THAT is a great reply!

Ain't this what the forums are all about! :cool:|||Thanks for the help !!!

So NULL in SQL isn't like NULL in C, for example...sql