I am getting output as ABC in below code. However i was expecting the output to be "Not ABC" as NULL is not equal to ABC. Can anybody help me understand what is happening here .
DECLARE
l_tmp VARCHAR2(3);
BEGIN
IF l_tmp <> 'ABC' THEN
dbms_output.put_line('Not ABC');
ELSE
dbms_output.put_line('ABC');
END IF;
END;
A variable that hasn't been assigned a value is NULL. Never compare anything with NULL - such a test (whether it's IF, where WHEN in a CASE expression, where WHERE in a SQL filter, always evaluates to false (even when inverted with !=, <>, NOT, etc.). hence the ELSE branch, which is for test failures or falseness, will be executed.
NULL isn't a value, it means we don't know the value - if we did, it may or may not satisfy the test expression, so Oracle always makes such tests fail. Instead, use IS NULL to check for nullness, or use NVL to change it some dummy value so you can perform the test with only one expression:
DECLARE
l_tmp VARCHAR2(3);
BEGIN
IF NVL(l_tmp,' ') <> 'ABC' THEN
dbms_output.put_line('Not ABC');
ELSE
dbms_output.put_line('ABC');
END IF;
END;
It should be noted that DECODE is an exception to the normal rule: you can compare against NULL as if it were a distinct value: SELECT DECODE(null,null,'is null','is not null') FROM dual will return is null. But just about every other function (such as case) and expression requires that we explicitly handle NULLs as a special case. Either NVL or COALESCE get used quite frequently to do just that.
FALSE, but in NULL. (See dbfiddle.uk/YrX8JDyU) This makes a great difference, because we can hence not solve this by applying NOT as in IF NOT l_tmp = 'ABC' THEN.
IF, or WHEN in a case statement, or even WHERE in a SQL query. Those tests will evaluate to false when an operand is null. That's why the ELSE branch is executed, if there is one.IN PL/SQL (just as in SQL) we use a three-state boolean logic. A boolenan value or an expression can be true or false or "unknown". NULL represents "unknown".
As NULL means "unknown", the expression NULL <> 'ABC' is neither "true" nor "false", but "unknown", too. This means that with
IF l_tmp <> 'ABC' THEN
we don't enter the THEN part, when l_tmp is null, as the expression is not true. We would not enter the THEN part with IF l_tmp = 'ABC' THEN either. We end in the ELSE part, as this is catching both the "false" cases and the "unknown" cases.
You can of course check for NULL explicitly:
IF l_tmp IS NULL OR l_tmp <> 'ABC' THEN
in order to enter the THEN part in case of null.
