{"id":20384,"date":"2021-09-19T08:26:32","date_gmt":"2021-09-18T22:26:32","guid":{"rendered":"https:\/\/database.guide\/?p=20384"},"modified":"2021-09-19T08:26:32","modified_gmt":"2021-09-18T22:26:32","slug":"nullif-function-in-oracle","status":"publish","type":"post","link":"https:\/\/database.guide\/nullif-function-in-oracle\/","title":{"rendered":"NULLIF() Function in Oracle"},"content":{"rendered":"\n

In Oracle Database, the NULLIF()<\/code> function compares two expressions, and returns null<\/code> if both expressions are equal. If they are not equal, then the function returns the first expression.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

NULLIF(expr1, expr2)<\/code><\/pre>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s an example to demonstrate:<\/p>\n\n\n\n
SELECT NULLIF(5, 7)\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
5<\/pre>\n\n\n\n
In this case, the arguments were not equal, and so the function returned the first argument.<\/p>\n\n\n\n
Here’s what happens when both arguments are equal:<\/p>\n\n\n\n
SET NULL 'null';\nSELECT NULLIF(7, 7)\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
null<\/pre>\n\n\n\n
We can see that NULLIF()<\/code> returns null<\/code> when both arguments are equal<\/p>\n\n\n\n
Regarding the first line SET NULL 'null';<\/code>, I added that so that my SQLcl session returns null<\/code> whenever the result is null. <\/p>\n\n\n\n
By default, SQLcl and SQL*Plus return a blank space whenever null<\/code> occurs as a result of a SQL SELECT<\/a><\/code> statement. <\/p>\n\n\n\n
However, you can use SET NULL<\/a><\/code> to specify a different string to be returned. Here I specified that the string null<\/code> should be returned.<\/p>\n\n\n\n
Compared to CASE<\/code><\/h2>\n\n\n\n
The NULLIF()<\/code> function is the equivalent of the following CASE<\/code> expression:<\/p>\n\n\n\n
CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END<\/code><\/pre>\n\n\n\n
Invalid Argument Count<\/h2>\n\n\n\n
Calling the function without passing any arguments results in an error:<\/p>\n\n\n\n
SELECT NULLIF()\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
SQL Error: ORA-00909: invalid number of arguments\n00909. 00000 -  \"invalid number of arguments\"<\/pre>\n\n\n\n
And passing too many arguments also causes an error:<\/p>\n\n\n\n
SELECT NULLIF(1, 2, 3)\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
SQL Error: ORA-00909: invalid number of arguments\n00909. 00000 -  \"invalid number of arguments\"<\/pre>\n","protected":false},"excerpt":{"rendered":"
In Oracle Database, the NULLIF() function compares two expressions, and returns null if both expressions are equal. If they are not equal, then the function returns the first expression.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41],"tags":[83,93,20],"class_list":["post-20384","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-comparison-functions","tag-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/20384","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=20384"}],"version-history":[{"count":4,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/20384\/revisions"}],"predecessor-version":[{"id":20389,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/20384\/revisions\/20389"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=20384"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=20384"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=20384"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}