Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    61

    Talking Unanswered: What the result if two columns are compared and both of them are nulls?

    Hi everyone,

    What the result from the below query if there is record which CONTACT_ID=10 and USER_ID column is null? Nothing is found or the record is shown?

    select * from CONTACT where CONTACT_ID=10 and USER_ID=USER_ID

    Thanks,

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    IS NULL ??

    I shall assume you mean:

    select * from CONTACT where CONTACT_ID=10 and USER_ID IS NULL

    And yes then the record is shown

    (the syntax is not =NULL, but IS NULL)

    BOW
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    Mar 2004
    Posts
    61

    Re: IS NULL ??

    No, I know that. I mean How DB2 compares two columns if they are both null.

    Thanks,

    Originally posted by Tank
    I shall assume you mean:

    select * from CONTACT where CONTACT_ID=10 and USER_ID IS NULL

    And yes then the record is shown

    (the syntax is not =NULL, but IS NULL)

    BOW

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: IS NULL ??

    AFAIK, two null values are not equal ...

    As an example ,

    $ db2 "select * from sysibm.sysdummy1 where (cast(null as integer)=cast(null as integer))"

    IBMREQD
    -------

    0 record(s) selected.

    but

    $ db2 "select * from sysibm.sysdummy1 where (cast('2' as integer)=cast('2' as integer))"

    IBMREQD
    -------
    Y

    1 record(s) selected.

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Mar 2004
    Posts
    61

    Re: IS NULL ??

    You are right. Two null columns are not equal. Even the two columns are the same one. That's very prone to error because for most of us (even Datastage server) they are equal if both sides of = are null.

    Thanks,

    Originally posted by sathyaram_s
    AFAIK, two null values are not equal ...

    As an example ,

    $ db2 "select * from sysibm.sysdummy1 where (cast(null as integer)=cast(null as integer))"

    IBMREQD
    -------

    0 record(s) selected.

    but

    $ db2 "select * from sysibm.sysdummy1 where (cast('2' as integer)=cast('2' as integer))"

    IBMREQD
    -------
    Y

    1 record(s) selected.

    HTH

    Sathyaram

Posting Permissions

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