compare null with null

jinsezh · #1 (**permalink**) 10-10-06, 19:08

Hi there,

I want to compare source.enddate with target.enddate, they should be both NULL or the same value.

If I write " select * from source, target where source.enddate = target.enddate", it will not return the record which the two columns are both NULL.

So I wonder which is the easiest way to compare them.

Thanks,
jinse

sathyaram_s · #2 (**permalink**) 10-10-06, 19:36

COALESCE(source.enddate,'1600-01-01')=COALESCE(target.enddate,'1600-01-01')

umayer · #3 (**permalink**) 10-11-06, 05:09

try:

select * from source, target where source.enddate IS NOT DISTINCT FROM target.enddate

... IS [NOT] DISTINCT FROM ... is available for DB2 for z/OS V8 ( maybe it's also available for other platforms )

Peter.Vanroose · #4 (**permalink**) 10-11-06, 12:20

Code:

SELECT *
FROM   source, target
WHERE  source.enddate = target.enddate
OR     (source.enddate IS NULL
        AND
        target.enddate IS NULL
       )

Peter.Vanroose · #5 (**permalink**) 10-11-06, 12:24

Yet another one (might or might not be more performant than the others):

Code:

SELECT *
FROM   source INNER JOIN target
       ON source.enddate = target.enddate
UNION ALL
SELECT *
FROM   (SELECT * FROM source WHERE enddate IS NULL),
       (SELECT * FROM target WHERE enddate IS NULL)

Note that the latter join is actually a cross join!

Internet Services

Web Development

Digital Marketing

Consumer Tech