Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Brazil
    Posts
    30

    Question Unanswered: 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.

  2. #2
    Join Date
    Jul 2003
    Location
    india
    Posts
    15

    Re: Difference between IS and =

    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

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Difference between IS and =

    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...

  4. #4
    Join Date
    Oct 2003
    Posts
    706

    Cool

    Now THAT is a great reply!

    Ain't this what the forums are all about!
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  5. #5
    Join Date
    Oct 2003
    Location
    Brazil
    Posts
    30

    Talking Thanks !

    Thanks for the help !!!

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •