| (non)Matching nulls [message #612221] |
Mon, 14 April 2014 10:15  |
tmcallister
Messages: 107
Registered: December 2007
|
Senior 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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
