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,

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

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

Thanks,

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

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,