In Oracle, NULL does not equal NULL:

EDDIE@XE> SELECT ‘hi there’ 2 FROM DUAL 3 WHERE NULL = NULL;

no rows selected

But sometimes, if not most of the times, you do want the condition NULL = NULL to return true. For example:

[…]

On my Oracle database 8i (8.1.7.4) instance, I ran the following:

SQL> SELECT 1 my_number 2 FROM DUAL 3 UNION 4 SELECT NULL my_number 5 FROM DUAL 6 / SELECT 1 my_number * ERROR at line 1: ORA-01790: expression […]

While troubleshooting a problem in the Order entry module of Oracle Applications (11.0.3), I query the column open_flag in the order header table so_headers_all for the order number in question. The column open_flag is supposed to be a Y/N flag that tells me whether the order is open or closed. Instead, for this order, the […]

In Oracle, as in other RDBMSs, nulls should be given a special treatment. In Oracle, there are three (maybe more?) functions that deal with nulls, NVL, NVL2 and COALESCE. I would like to ask you a question, but first, let me quickly refresh my memory:

Did you know that in addition to the function NVL, Oracle also has the function NVL2? which is quite different than its cousin NVL. So, what does NVL2 do?