I’ve been using Oracle on and off for about twelve years so it seems like I should have known this trick already but today I learned so I’m sharing.
Consider this scenario: You have a table that stores records about a course. It is valid to allow a user to store a duplicate course record for everything but the ID. However, you want to tell the user that the record is a duplicate and point them to the carbon copy. Furthermore, many of the columns you check against for equivalence are NULLABLE and are of different data types (DATE, VARCHAR, NUMBER).
The first thing you have to remember is NULL != NULL. Thus you can’t just check a column thusly
source_col = test_value It will work when both source_col
and test_value are NOT NULL but if they are both NULL you won’t identify the two records as being copies of each other.
I’ve typically handled this by having this clunky bit of logic:
WHERE (source_col = test_value OR (source_col IS NULL and test_value IS NULL)) AND ...
If you are testing against five or six records your query quickly becomes kind of ugly and difficult to visually parse so I’ve not been really happy with this technique
even though it does work. NOTE:
NVL(source_col, 'fakevalue') = NVL(test_value, 'fakevalue') is both slower and fraught with the possibility that fakevalue might, possibly
find a way to validly exist in the source_col which could lead to a false positive.
WHERE DECODE(source_col, test_value, 1) = 1 AND ...
This is super simple and brilliat but does depend on future maintainers of your code to understand how
DECODE works. Here is my explanation.
DECODE is defined like so
DECODE(expression, search_value, result).
DECODE is actually a little more complicated than what I’m describing so if please read the documentation. Anyway, for my usage
DECODE is just dealing
with the first three possible arguments.
expression represents the source column that I am search against.
search_value is the test value I’m searching for.
result is what
DECODE will return if the
expression match. The key here is that
DECODE considers it a match if both
search_value are both NULL!
Thus if your source_col value IS NULL and test_value is NULL or your souce_col value is a non-empty string and your test_value is the same non-empty string the records will match.
WARNING! I can’t really stress this enough. Oracle considers an empty string to be null. Thus
DECODE(null, '', 1) will return the
1 result. Consider this dummy example
SELECT 'foo' as bar from dual WHERE '' IS NULL;
You will get a row back with bar == ‘foo’ which may not be what you expected. Thus, you will get a row back as well if you do this:
SELECT 'foo' as bar FROM dual WHERE DECODE('',NULL,1) = 1
Even if you switch the NULL and empty string around the
DECODE = 1 will evaluate to
TRUE so you will get this match every time. This is just a part of how oracle works and I’m not sure you can do anything to work around it.