Home » SQL & PL/SQL » SQL & PL/SQL » (non)Matching nulls
(non)Matching nulls [message #612221] Mon, 14 April 2014 10:15 Go to next message
tmcallister
Messages: 97
Registered: December 2007
Member
I have an addressing system where I need to be able to know matches and non-matches on various fields that can be null. I believe I've derived the simplest possible example here:

SELECT   *
FROM     (SELECT 'a' c1, 'b' c2 FROM DUAL
          UNION
          SELECT 'a' c1, 'a' c2 FROM DUAL
          UNION
          SELECT 'a' c1, NULL c2 FROM DUAL
          UNION
          SELECT NULL c1, NULL c2 FROM DUAL
          UNION
          SELECT NULL c1, 'b' c2 FROM DUAL)


Now to identify the matches I use
WHERE    c1 = c2 OR (c1 IS NULL AND c2 IS NULL) 


Alternatively I could use nvl, coalesce, or decode as described here: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7806711400346248708. I far prefer the syntax above however.

Now to identify mismatches I was hoping to use the above, except with a NOT clause. However this doesn't work as I hoped due to NOT UNKNOWN != KNOWN (see: http://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions004.htm#g1068986)

What would be the best way to identify this? The following works, but I don't like it...

SELECT   *
FROM     (SELECT 'a' c1, 'b' c2 FROM DUAL
          UNION
          SELECT 'a' c1, 'a' c2 FROM DUAL
          UNION
          SELECT 'a' c1, NULL c2 FROM DUAL
          UNION
          SELECT NULL c1, NULL c2 FROM DUAL
          UNION
          SELECT NULL c1, 'b' c2 FROM DUAL)
WHERE    DECODE(c1, c2, 1, 0) = 0;

[Updated on: Mon, 14 April 2014 10:23]

Report message to a moderator

Re: (non)Matching nulls [message #612224 is a reply to message #612221] Mon, 14 April 2014 10:31 Go to previous messageGo to next message
cookiemonster
Messages: 11072
Registered: September 2008
Location: Rainy Manchester
Senior Member
WHERE c1 != c2 OR (c1 IS NULL AND c2 IS NOT NULL) OR (c1 IS NOT NULL AND c2 IS NULL)
Re: (non)Matching nulls [message #612226 is a reply to message #612224] Mon, 14 April 2014 10:48 Go to previous message
tmcallister
Messages: 97
Registered: December 2007
Member
Easy enough. Can't believe I didn't come of with that.
Previous Topic: how can i insert 00-jan-2014 into a date column
Next Topic: select query
Goto Forum:
  


Current Time: Wed Oct 22 03:27:20 CDT 2014

Total time taken to generate the page: 0.09164 seconds