Developers coming from a procedural coding background are familiar with traditional IF-THEN-ELSE constructs, possibly extended to SWITCH or CASE blocks that group a series of conditions. Depending on the language, those could either evalute the conditions until the first TRUE is found or until the first result breaks out. They might also be comfortable with ternary operators such as the x ? y : z found in many laguages or the comparable (x,y)[z] operator in python.
When these developers start writing SQL they are often confronted with the quandary of how to implement these same syntax features when their usual procedural flow does not exist.
The most common type of expression evaluation would have to be those found in WHERE clauses and those of the ON clauses in joins. However, these conditions don’t really fit the same model as the IF-THEN-ELSE syntax. While the condition does do something when it’s true, but unlike an IF-THEN, there is no result description in the syntax because the condition itself is the description. That is, the SQL condition is a declarative rule of the result set, not a procedural flow directive.
So, while it’s not unreasonable to say IF condition is true THEN return row ELSE do not return row; but that’s not really in the spirit of the goal.
The closest SQL has to a real IF-THEN-ELSE syntax (without embedded PL/SQL declarations) is the CASE expression. The expression comes in two forms, the first of which most closely resembles a classic IF-ELSE IF-ELSE chain. Rather than directing procedural flow, the CASE returns a single result from the first condition found in the WHEN-list
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
…
WHEN conditionN THEN resultN
ELSE default result
END
The ELSE clause is optional in the list. If none of the WHEN clauses evaluate true, the CASE will return NULL.
The query below illustrates 2 CASE statements, one with a default and one without, thus showing the NULL result when a default ELSE is missing and no TRUE conditions are found.
SQL> SELECT a,b,c,d,
2 CASE
3 WHEN a = c THEN 'A=C'
4 WHEN b > c THEN 'B>C'
5 ELSE 'Other'
6 END case_w_else,
7 CASE
8 WHEN a = c THEN 'A=C'
9 WHEN b > c THEN 'B>C'
10 END case_wo_else
11 FROM (SELECT 1 a,2 b,1 c, NULL d FROM DUAL
12 UNION ALL
13 SELECT 2 a,3 b,1 c, NULL d FROM DUAL
14 UNION ALL
15 SELECT 3 a,4 b,5 c, 3 d FROM DUAL);
A B C D CASE_W_ELSE CASE_WO_ELSE
------- ------- ------- ------- ----------- ------------
1 2 1 A=C A=C
2 3 1 B>C B>C
3 4 5 3 Other
The other form of the CASE is similar; but the condition is always an implied equality check for a particular value.
CASE target_value
WHEN compare_value_1 THEN result1
WHEN condition_value_2 THEN result2
…
WHEN compare_value_3 THEN resultN
ELSE default result
END
As with the first format, the ELSE is optional and if none of the comparison values are equal to the target value and there is no ELSE, the CASE will return NULL.
Both of these forms are similar to a switch statement in c or Java, but they do not require or support a break statement to exist the case. Also, since the CASE returns a result, if a WHEN clause evaluates true and a result is known, the case ceases evaluating further, unlike the switch statements of other languages that could search through and execute other conditions and results after the first.
Oracle supports a function DECODE that operates similar to the second form of the CASE statement. When choosing witch to use, CASE is generally preferred because it usually easier to read and pair conditions with their corresponding results, whereas a DECODE is simply a list and the reader will have count through pairs to determine which go together unless they are formatted to make it clear. Like the CASE, DECODE uses a target value and which is then compared to sequential search values until it finds a match or runs out. If it runs out of search values without a default then the DECODE will return NULL.
DECODE(target_value ,
compare_value_1 , result1 ,
condition_value_2 , result2 ,
…
compare_value_3 , resultN ,
default result
)
Like the CASE, the evaluations use short-circuit logic, so the DECODE will cease further evaluations when it finds a match to the target.
NOTE: Unlike the CASE (and virtually any other equality comparison in SQL or PL/SQL) DECODE will evaluate a NULL as equivalent to another NULL. Thus, the query below will return the message “found null” because the NULL compare will be evaluated as equal to the NULL empty-string.
SQL> SELECT DECODE('',
2 1, 'found one',
3 NULL, 'found null',
4 2, 'found two'
5 ) result
6 FROM DUAL;
RESULT
----------
found null
The query below shows DECODE with and without a default.
SQL> SELECT a, b, c, d,
2 DECODE(a,
3 1, 'A is a one',
4 2, 'A is a two',
5 'Other'
6 ) decode_default,
7 DECODE(a,
8 1, 'A is a one',
9 2, 'A is a two'
10 ) decode_no_default
11 FROM (SELECT 1 a, 2 b, 1 c, NULL d FROM DUAL
12 UNION ALL
13 SELECT 2 a, 3 b, 1 c, NULL d FROM DUAL
14 UNION ALL
15 SELECT 3 a, 4 b, 5 c, 3 d FROM DUAL);
A B C D DECODE_DEFAULT DECODE_NO_DEFAULT
------- ------- ------- ------- -------------- -----------------
1 2 1 A is a one A is a one
2 3 1 A is a two A is a two
3 4 5 3 Other
Another thing to take care with is the return values of a CASE or DECODE. All return paths must return the same data type, or be implicitly convertible to a consistent type. If a CASE returns a number one “WHEN” but a varchar2 in another, you’ll get implicit conversion, possibly resulting in errors if a non-numeric string is converted to a number.
In addition to CASE and DECODE, Oracle’s SQL also supports a variety of special case conditional functions. These functions include NVL, NVL2, COALESCE, and NULLIF.
NVL accepts 2 parameters. If the first is not null, it is returned as is. If it is null, the second parameter is returned. If the second parameter can not be converted to the same datatype as the first parameter the function will raise an inconsistent data types exception. An important note about NVL is that it does not use short-circuit evaluation. Both expressions are evaluated before returning a result. So, if the second parameter calls a function that updates a table or sets a package variable, those actions will occur even if the first parameter is NULL.
Thus NVL(a,b) is equivalent toBEGIN
a_result := a;
b_result := b;
IF a_result IS NOT NULL THEN
RETURN a_result;
ELSE
RETURN b_result;
END IF;
END;
NVL2, as the name implies, is similar to NVL, but the first parameter is used solely for evaluation and not as a return value. If the first parameter is not null, the second parameter is returned. If the first parameter is null, the third parameter is returned. As with NVL, both expressions must return the same data type. Also like NVL, all of the expressions are evaulated before returning a result. So, if either or both of the second or third parameters calls a function that updates a table or sets a package variable, those actions will occur regardless of whether the first expression is null.
Thus NVL2(x,a,b) is equivalent to
BEGIN
a_result := a;
b_result := b;
IF x IS NOT NULL THEN
RETURN a_result;
ELSE
RETURN b_result;
END IF;
END;
COALESCE is similar to NVL and many developers will use it interchangeably with NVL; but the two do have functional differences. First, COALESCE can handle two or more parameters whereas NVL is fixed at two. Also, COALESCE uses short-circuit evaluation. That is, instead of evaluating every parameter’s expression before checking for NULL values, COALESCE will check each value in order and will return the first non-NULL value it finds and ignores the rest. Do note, while COALESCE does not evaluate the results of an expression it does check the data types. If the input parametes, NULL or not, are of incompatible types, the function will raise an exception. If all of the input parameters are null, the function will return NULL.
Thus COALESCE(a,b,c,d) is equivalent toBEGIN
RETURN CASE WHEN a IS NOT NULL THEN a
WHEN b IS NOT NULL THEN b
WHEN c IS NOT NULL THEN c
WHEN d IS NOT NULL THEN d
END;
END;
In the query below NVL, NVL2 and COALESCE are shown with a variety of null and non-null parameters to show how they evaluate in each situation. While all of these examples use simple integers, the three functions would function similarly using date, timestamp, or varchar2 values.
SELECT a,b,c,d,
NVL(d, 99) "nvl",
NVL2(d, -1, 99) "nvl2",
COALESCE(a, b, c, d) "coalesce1",
COALESCE(d, c, b, a) "coalesce2",
COALESCE(a, b, d, c) "coalesce3"
FROM (SELECT 1 a,2 b,1 c,NULL d FROM DUAL
UNION ALL
SELECT 2 a, 3 b, 1 c, NULL d FROM DUAL
UNION ALL
SELECT 3 a, 4 b, 5 c, 3 d FROM DUAL);
A B C D nvl nvl2 coalesce1 coalesce2 coalesce3
-- -- -- -- ---- ----- ---------- ---------- ----------
1 2 1 99 99 1 1 1
2 3 1 99 99 2 1 2
3 4 5 3 3 -1 3 3 3
I’ve not seen the LNNVL function used very often but it is a useful and compact means of representing conditions that may or may not include NULL values.
I don’t know of any Oracle documentation that states the meaning of the name “LNNVL”; but based on the functionality and some 3rd party sites it appears to stand for “logical not not null value.” LNNVL evaluates an Boolean expression and returns TRUE if the expression is FALSE, or if it is UNKNOWN because of NULLs. It will return FALSE if the expression is TRUE. Here are a few examples to illustrate:
LNNVL( 1=1 ) returns FALSE, because the expression is TRUE
LNNVL( 1=2 ) returns TRUE, because the expression is FALSE
LNNVL( 1=NULL) returns TRUE, because the expression is unknown
LNNVL( NULL=1) returns TRUE, because the expression is unknown
LNNVL( NULL=NULL) returns TRUE, because the expression is unknown
LNNVL (NULL is NULL) returns FALSE, because the expression is TRUE
LNNVL( 1 is NULL) returns TRUE, because the expression is FALSE
LNNVL( NULL is not NULL ) returns TRUE, because the expression is FALSE
An quirk of LNNVL is that it returns a Boolean type value intended for use in WHERE clauses, ON clauses of joins, or WHEN conditions of CASE. However, unlike most Boolean functions, it is still permissible to include it as part of the selected values of a query. There is no good reason I know of to do so as the result will always be NULL when used in this form; but it is interesting that Oracle allows it, even in this edge case. The query below shows normal usage as well as the unusual NULL usage for various inputs.
SQL> SELECT a,b,
2 CASE WHEN lnnvl(a = b) THEN 1 ELSE 0 END "lnnvl(a=b)",
3 CASE WHEN lnnvl(a != b) THEN 1 ELSE 0 END "lnnvl(a!=b)",
4 lnnvl(a = b), lnnvl(a != b)
5 FROM (SELECT TO_NUMBER(NULL) a, TO_NUMBER(NULL) b FROM DUAL
6 UNION ALL
7 SELECT TO_NUMBER(NULL) a, 1 b FROM DUAL
8 UNION ALL
9 SELECT 1 a, TO_NUMBER(NULL) b FROM DUAL
10 UNION ALL
11 SELECT 1 a, 1 b FROM DUAL
12 UNION ALL
13 SELECT 1 a, 2 b FROM DUAL);
A B lnnvl(a=b) lnnvl(a!=b) LNNVL(A=B) LNNVL(A!=B)
-- -- ---------- ----------- ---------- -----------
1 1
1 1 1
1 1 1
1 1 0 1
1 2 1 0
Oracle supports one other NULL-related function, sort of the opposite of NVL. Where NVL returns a specific value if you input a NULL, the NULLIF function will return a NULL if you input a specific value. That is, NULLIF(a,b) is equivalent toIF a=b THEN
RETURN NULL;
ELSE
RETURN a;
END IF;
One last general purpose function: VALIDATE_CONVERSION was instoduced in 12cR2. It allows for checking of an expression conversion to see if it would result in a legal value or if it would generate an error. If the conversion is valid the function returns 1. If the converions is invalid the function returns 0. In the query below 4 date conversions are tested. The first two check day/month ordering vs month/day in the formatting. The latter two check using the day name “Friday” for different NLS values.
SQL> SELECT validate_conversion('02/19/2021' AS DATE, 'mm/dd/yyyy', 'NLS_DATE_LANGUAGE = American') "ok?"
2 FROM DUAL
3 UNION ALL
4 SELECT validate_conversion('19/02/2021' AS DATE, 'mm/dd/yyyy', 'NLS_DATE_LANGUAGE = American')
5 FROM DUAL
6 UNION ALL
7 SELECT validate_conversion('Friday 2021-02-19' AS DATE, 'Day yyyy-mm-dd', 'NLS_DATE_LANGUAGE = American')
8 FROM DUAL
9 UNION ALL
10 SELECT validate_conversion('Friday 2021-02-19' AS DATE, 'Day yyyy-mm-dd', 'NLS_DATE_LANGUAGE = German')
11 FROM DUAL;
ok?
----------
1
0
1
0
This concludes the general-purpose conditional functions; but Oracle also supports functions built for specific data types and special syntax as well. As these are specialized, they are somewhat out-of-scope for this article’s topic. If you need further details, see the SQL Language Reference
- NANVL – Like NVL, but for BINARY_FLOAT and BINARY_DOUBLE, returns a value if the input is NaN (Not a Number)
- ORA_DST_AFFECTED – evaluates a timestamp to determine if new time zone rules will cause it to be invalid.
- PRESENTV, PRESENTNNV – These are used within a select satement model clause. The return output expressions based on the existence or lack of existence of a value prior to the model evaluation, with the NNV variant also checking if the prior value was NULL.
- EXISTSNODE, XMLEXISTS, XMLISVALID – as the function names imply, these only apply to XMLTYPE values, EXISTSNODE is deprecated and XMLEXISTS should be used instead.