{"id":73288,"date":"2012-08-30T10:44:35","date_gmt":"2012-08-30T10:44:35","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/checking-for-null-with-oracle-sql\/"},"modified":"2021-07-14T13:07:51","modified_gmt":"2021-07-14T13:07:51","slug":"checking-for-null-with-oracle-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/checking-for-null-with-oracle-sql\/","title":{"rendered":"Checking for NULL with Oracle SQL"},"content":{"rendered":"
\n

The art of doing mathematics consists in finding that special case which contains all the germs of generality.
\n David Hilbert<\/p>\n<\/blockquote>\n

One of the most mindboggling values in the Oracle database is the NULL value. What is\u00a0NULL? NULL is nothing. NULL is not even the same as NULL. NULL is undefined. But you\u00a0need to work with NULL values (which are no actual values). Luckily Oracle provides us with a couple of functions to do the heavy lifting when it comes to checking for NULLs.<\/p>\n

IS [NOT] NULL<\/h2>\n

Syntax:<\/p>\n

expr1 IS [NOT] NULL<\/pre>\n
This predicate returns rows from the table where the column value for expr1 contains (or
\n doesn\u2019t contain) a NULL value.<\/p>\n\n\n\n\n\n\n\n\n\n
 <\/td>\nEMPNO<\/td>\nENAME<\/td>\nJOB<\/td>\nSAL<\/td>\nCOMM<\/td>\n<\/tr>\n
1<\/td>\n7876<\/td>\nADAMS<\/td>\nCLERK<\/td>\n1100.00<\/td>\n <\/td>\n<\/tr>\n
2<\/td>\n7499<\/td>\nALLEN<\/td>\nSALESMAN<\/td>\n1600.00<\/td>\n300.00<\/td>\n<\/tr>\n
3<\/td>\n7839<\/td>\nKING<\/td>\nPRESIDENT<\/td>\n5000.00<\/td>\n <\/td>\n<\/tr>\n
4<\/td>\n7654<\/td>\nMARTIN<\/td>\nSALESMAN<\/td>\n1250.00<\/td>\n1400.00<\/td>\n<\/tr>\n
5<\/td>\n7369<\/td>\nSMITH<\/td>\nCLERK<\/td>\n800.00<\/td>\n <\/td>\n<\/tr>\n
6<\/td>\n7844<\/td>\nTURNER<\/td>\nSALESMAN<\/td>\n1500.00<\/td>\n0.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
If you want to see all the employees with no commission filled in you can issue a statement like this:<\/p>\n
SELECT *\r\n  FROM emp t\r\n WHERE t.comm IS NULL;<\/pre>\n\n\n\n\n\n\n
 <\/td>\nEMPNO<\/td>\nENAME<\/td>\nJOB<\/td>\nSAL<\/td>\nCOMM<\/td>\n<\/tr>\n
1<\/td>\n7876<\/td>\nADAMS<\/td>\nCLERK<\/td>\n1100.00<\/td>\n <\/td>\n<\/tr>\n
2<\/td>\n7839<\/td>\nKING<\/td>\nPRESIDENT<\/td>\n5000.00<\/td>\n <\/td>\n<\/tr>\n
3<\/td>\n7369<\/td>\nSMITH<\/td>\nCLERK<\/td>\n800.00<\/td>\n <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Alternatively you can find the employees with a value in the commission column like this:<\/p>\n
SELECT *\r\nFROM emp t\r\nWHERE t.comm IS NOT NULL;<\/pre>\n\n\n\n\n\n\n
 <\/td>\nEMPNO<\/td>\nENAME<\/td>\nJOB<\/td>\nSAL<\/td>\nCOMM<\/td>\n<\/tr>\n
2<\/td>\n7499<\/td>\nALLEN<\/td>\nSALESMAN<\/td>\n1600.00<\/td>\n300.00<\/td>\n<\/tr>\n
4<\/td>\n7654<\/td>\nMARTIN<\/td>\nSALESMAN<\/td>\n1250.00<\/td>\n1400.00<\/td>\n<\/tr>\n
6<\/td>\n7844<\/td>\nTURNER<\/td>\nSALESMAN<\/td>\n1500.00<\/td>\n0.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Notice NULL is not the same as 0 (zero). NULL is not even the same as NULL. NULL = NULL results in FALSE where NULL IS NULL results in TRUE<\/p>\n
NVL<\/h2>\n
Syntax:<\/p>\n
NVL(expr1, expr2)<\/pre>\n
If expr1 contains a NULL value, then replace it with the value of expr2
\n The NVL function lets you substitute a value when a null value is encountered.<\/p>\n
Examples:
\n NVL(\u2018A\u2019,\u2019B\u2019) results in A
\n NVL(NULL,\u2019B\u2019) results in B
\n NVL(1,2) results in 1
\n NVL(NULL,2) results in 2
\n NVL(\u2018A\u2019,2) results in A
\n NVL(1, \u2018B\u2019) results in an error<\/p>\n
The last example results in an error because \u2018B\u2019 cannot be converted to a number. In the
\n one before it, it was possible to convert 2 to a varchar2 value.<\/p>\n
NVL2<\/h2>\n
Syntax:<\/p>\n
NVL2(expr1, expr2, expr3)<\/pre>\n
If expr1 contains a NULL value, then return expr3. If the value of expr1 contains a non-NULL
\n value, then return expr2.<\/p>\n
Examples:
\n NVL2(\u2018A\u2019,\u2019B\u2019,\u2019C\u2019) results in B
\n NVL2(NULL,\u2019B\u2019,\u2019C\u2019) results in C
\n NVL2(1,2,3) results in 2
\n NVL2(NULL,2,3) results in 3<\/p>\n
NULLIF<\/h2>\n
Syntax:<\/p>\n
NULLIF(expr1, expr2)<\/pre>\n
NULLIF returns NULL if expr1 is equal to expr2. If they are not equal expr1 is returned.
\n Expressions must be of the same data type, There is no implicit conversion performed.<\/p>\n
Examples:
\n NULLIF(\u2018A\u2019,\u2019B\u2019) results in A
\n NULLIF(\u2018A\u2019,\u2019A\u2019) results in NULL
\n NULLIF(2,3) results in 2
\n NULLIF(2,2) results in NULL
\n NULLIF(\u20182\u2019,2) results in an error
\n NULLIF(2,\u20192\u2019) results in an error<\/p>\n
COALESCE<\/h2>\n
Syntax:<\/p>\n
COALESCE(expr [, expr ]...)<\/pre>\n
The coalesce function returns the first non-NULL value of the expressions in the list. The list
\n must consist of at least 2 values. If all expressions evaluate to NULL then NULL is returned.<\/p>\n
Examples:
\n COALESCE(\u2018A\u2019,\u2019B\u2019,\u2019C\u2019) results in A
\n COALESCE(NULL,\u2019B\u2019,\u2019C\u2019) results in B
\n COALESCE(NULL,NULL,\u2019C\u2019) results in C
\n COALESCE(NULL,\u2019B\u2019,NULL) results in B
\n COALESCE(\u2018A\u2019) results in an error<\/p>\n
LNNVL<\/h2>\n
Syntax:<\/p>\n
LNNVL(condition)<\/pre>\n
The LNNVL function is used in the WHERE clause of an SQL statement when one of the
\n operands may contain a NULL value. The function returns TRUE is the result of the
\n condition is FALSE and FALSE is the result of the condition is TRUE or UNKNOWN. LNNVL
\n can be used in a condition when you would otherwise need to combine a condition with an
\n IS [NOT] NULL or an NVL predicate.<\/p>\n
The following queries have the same result:<\/p>\n
SELECT *\r\n\t  FROM emp e\r\n\t WHERE LNNVL(e.comm >= 100);\r\n\tSELECT *\r\n\t  FROM emp e\r\n\t WHERE 1 = 1\r\n\t   AND ((e.comm<\/pre>\n
Note that the condition must be inverted when using the LNNVL function.<\/p>\n
DEFAULTS<\/h2>\n
When a column in a table is defined there is the possibility to have a default value for this column whenever it is inserted without a value.<\/p>\n
\tcreate table def\r\n\t( code number\r\n\t, value varchar2(30) default 'Empty'\r\n\t)<\/pre>\n
This is done on insert only. You can still update the column to be NULL (or insert a NULL value) when by supplying an explicit NULL value.<\/p>\n
\tinsert into def (code) values (10)\r\n\tselect * from def\r\n\tinsert into def (code, value) values (15, NULL)\r\n\tupdate def set value = null where code = 10\r\n\tselect * from def<\/pre>\n
If you modify the default during the life of the table, the new default will be applied from that
\n moment on.<\/p>\n
\talter table DEF modify value default 'Really empty';\r\n\tinsert into def (code) values (20)<\/pre>\n
Now how do you make sure a default is applied to columns already NULL? You can do this
\n by using the keyword DEFAULT.<\/p>\n
\tupdate def set value = default where value is null\r\n\tselect * from def<\/pre>\n
DECODE<\/h2>\n
Syntax:<\/p>\n
DECODE(expr, search, result [, search, result ]... [, default ])<\/pre>\n
Using DECODE you can the value of a column (or any expression) to decide what to return. It can for instance be used to build a decision table. Consider the following decision table:<\/p>\n\n\n\n\n\n\n\n
Code<\/td>\nValue<\/td>\n<\/tr>\n
1<\/td>\nRed<\/td>\n<\/tr>\n
2<\/td>\nWhite<\/td>\n<\/tr>\n
3<\/td>\nBlue<\/td>\n<\/tr>\n
4<\/td>\nYellow<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The column in our table just holds the code instead of the value. If we want to represent the value instead of the code we could write a query like this:<\/p>\n
SELECT decode(color, 1, 'Red', 2, 'White', 3, 'Blue', 4, 'Yellow')\r\nFROM table;<\/pre>\n
CASE<\/h2>\n
Syntax:<\/p>\n
         CASE { simple_case_expression\r\n              | searched_case_expression\r\n              }\r\n              [ else_clause ]\r\n              END\r\nsimple_case_expression\r\n         expr\r\n              { WHEN comparison_expr THEN return_expr }...\r\nsearched_case_expression\r\n        { WHEN condition THEN return_expr }...\r\nelse_clause\r\n        ELSE else_expr<\/pre>\n
The case statement has two flavors. The simple case and the searched case. In the simple case the expression is written only once and depending on the result of this expression one of the possible cases is being used. This can be helpful when you need to choose from a set of distinct values. In the searched case every case has its own expression. This can for instance be helpful when using ranges.<\/p>\n
The same query as above can be written using a simple case expression.<\/p>\n
SELECT CASE color\r\n         WHEN 1 THEN 'Red'\r\n         WHEN 2 THEN 'White'\r\n         WHEN 3 THEN 'Blue'\r\n         WHEN 4 THEN 'Yellow'\r\n         ELSE 'Unknown'\r\n      END color\r\n  FROM table;<\/pre>\n
The query can also be written using a searched case expression:<\/p>\n
SELECT CASE\r\n         WHEN color=1 THEN 'Red'\r\n         WHEN color=2 THEN 'White'\r\n         WHEN color=3 THEN 'Blue'\r\n         WHEN color=4 THEN 'Yellow'\r\n      END color\r\nFROM table;<\/pre>\n
The else clause is optional. If none of the cases is valid for the expression, then NULL is
\n returned, as opposed to PL\/SQL where an error is raised.<\/p>\n
Conclusion<\/h2>\n
NULL values are one of the most mind-boggling values in the Oracle database. Luckily Oracle provides us with a lot of functionality to work with NULLs. The newer versions of the database also provide us with functionality to make your SQL (and PL\/SQL) code more readable, for instance the CASE keyword which can replace the DECODE keyword and has more possibilities as well.<\/p>\n
ref:
\n http:\/\/oracleworldblog.blogspot.nl\/2009\/10\/oracle-null-functions.html<\/a>
\nhttp:\/\/docs.oracle.com\/cd\/B19306_01\/server.102\/b14200\/functions105.htm<\/a>
\nhttp:\/\/docs.oracle.com\/database\/122\/SQLRF\/NVL2.htm#SQLRF00685<\/a>
\nhttp:\/\/docs.oracle.com\/database\/122\/SQLRF\/NULLIF.htm#SQLRF00681<\/a>
\nhttp:\/\/docs.oracle.com\/database\/122\/SQLRF\/COALESCE.htm#SQLRF00617<\/a>
\nhttp:\/\/docs.oracle.com\/database\/122\/SQLRF\/LNNVL.htm#SQLRF06327<\/a>
\nhttp:\/\/docs.oracle.com\/database\/122\/SQLRF\/DECODE.htm#SQLRF00631<\/a>
\nhttp:\/\/docs.oracle.com\/database\/122\/LNPLS\/plsql-language-fundamentals.htm#LNPLS278<\/p>\n","protected":false},"excerpt":{"rendered":"
The art of doing mathematics consists in finding that special case which contains all the germs of generality. David Hilbert One of the most mindboggling values in the Oracle database is the NULL value. What is\u00a0NULL? NULL is nothing. NULL is not even the same as NULL. NULL is undefined. But you\u00a0need to work with NULL values (which are no…<\/p>\n","protected":false},"author":316181,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[124952],"coauthors":[48560],"class_list":["post-73288","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-redgate-deploy"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73288","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316181"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73288"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73288\/revisions"}],"predecessor-version":[{"id":73666,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73288\/revisions\/73666"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73288"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73288"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73288"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73288"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}