{"id":25082,"date":"2018-08-16T05:00:33","date_gmt":"2018-08-16T05:00:33","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=25082"},"modified":"2021-03-11T21:52:08","modified_gmt":"2021-03-11T16:22:08","slug":"sql-null-functions","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-null-functions\/","title":{"rendered":"SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF"},"content":{"rendered":"

In this tutorial, we will understand the need and requirements of using the Null values. Let us now try to understand more about null values and null functions in SQL with examples.<\/p>\n

What are NULL Values in SQL?<\/h3>\n

Null values are the placeholders in the database when we have the data missing, or the required data is not available.<\/p>\n

A null value is not a part of any particular data type, it is a flexible data type and can be put in the column of any data type be it string, int, blob or CLOB datatype.<\/p>\n

Null values come in handy while the cleaning of data during the exploratory analysis of data.<\/p>\n

Null values help us in removing the ambiguity arising in data. Also, null values are beneficial to maintain uniform datatype across the column.<\/p>\n

Example<\/strong>: Imagine if the user by mistake enters the date of birth in the column of mobile number then ambiguity could arise when contact needs to be established.<\/p>\n

To overcome this we run a check on data before insertion and update any data which is not of date datatype with the null value.<\/p>\n

Why do we need NULL Functions?<\/h3>\n

Null functions are required to perform operations on the null values stored in our database. We can perform functions on NULL values, which explicitly recognize if a value is null or not.<\/p>\n

Using this recognizing capacity, one can further perform operations on the null values like the aggregate functions in SQL. Some of the functions are as follows:<\/p>\n\n\n\n\n\n\n\n
Sr.No<\/b><\/td>\nFunction<\/b><\/td>\nDescription<\/b><\/td>\n<\/tr>\n
1<\/td>\nISNULL()<\/span><\/td>\nHelps us to replace NULL values with the desired value.\u00a0<\/span><\/td>\n<\/tr>\n
2<\/td>\nIFNULL()<\/span><\/td>\nAllows us to return the first value if the value is NULL, and otherwise returns the second value.\u00a0<\/span><\/td>\n<\/tr>\n
3<\/td>\nCOALESCE()<\/span><\/td>\nHelps us to return the first non-null values in the arguments.\u00a0<\/span><\/td>\n<\/tr>\n
4<\/td>\nNVL()<\/span><\/td>\nHelps to replace the NULL value with the desired value given by the user.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Demo Database<\/h4>\n

Let us now have a look at our demo database – \u2018DataFlair\u2019
\nQuery:\u00a0<\/strong><\/p>\n

SELECT * FROM DataFlair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
Examples<\/h4>\n
1. Let us see the use of the IFNULL() function.<\/strong><\/p>\n
Syntax<\/strong>:<\/p>\n
SELECT col1,col2, IFNULL(col3, value_to_be_replaced) FROM tableName;<\/pre>\n
Example: Let us view the experience of each employee in DataFlair and replace the NULL value with 0 years of experience.
\nQuery<\/strong>:<\/p>\n
SELECT emp_id,name, IFNULL(experience, 0) FROM DataFlair;<\/pre>\n
Output<\/strong>:<\/p>\n
\"SQL<\/a><\/p>\n
Here we can see all the values corresponding to NULL are automatically replaced by 0.<\/p>\n
2. Let us see the use of ISNULL() function.<\/strong><\/p>\n
Syntax<\/strong>:<\/p>\n
SELECT col1,col2, ISNULL(col3) FROM tableName;<\/pre>\n
Example: Let us now check if the experience of each employee in DataFlair is NULL or not NULL.
\nQuery<\/strong>:<\/p>\n
SELECT emp_id,name, ISNULL(experience) FROM DataFlair;\r\n<\/pre>\n
Output<\/strong>:<\/p>\n
\"SQL<\/a><\/p>\n
Here, we can see that the NULL values are converted to 0 and non NULL values are converted to 1.<\/p>\n
3. Let us see the use of COALESCE() function.<\/strong><\/p>\n
Syntax<\/strong>:<\/p>\n
SELECT col1,col2, COALESCE(col3, value_to_be_replaced) FROM tableName;<\/pre>\n
Example: Let us view the experience of each employee in DataFlair and replace the NULL value with 0 years of experience using the COALESCE() function.<\/p>\n
Query<\/strong>:<\/p>\n
SELECT emp_id,name, COALESCE(experience, 0) FROM DataFlair;<\/pre>\n
Output<\/strong>:<\/p>\n
\"SQL<\/a><\/p>\n
Here, the NULL values are converted to 0 as we have asked the function to convert NULL values to 0.<\/p>\n
4. Let us see the use of NVL() function.<\/strong><\/p>\n
Syntax<\/strong>:<\/p>\n
SELECT col1,col2, NVL(col3) FROM tableName;<\/pre>\n
Example: Let us now check if the experience of each employee in DataFlair is NULL or not NULL using the NVL function.<\/p>\n
Query<\/strong>:<\/p>\n
SELECT emp_id,name, NVL(experience) FROM DataFlair;<\/pre>\n
Output<\/strong>:<\/p>\n
\"SQL<\/a><\/p>\n
Here, we can see that the non-null values are represented as 1 and the null values are represented by 0.<\/p>\n
Summary<\/h3>\n
In this tutorial, we have seen what null values are and why do we need to use NULL values.<\/p>\n
We have seen that the use of NULL values is elementary to the database and is done to maintain the integrity of the database. After this, we have further learned about the various functions that can be run on NULL values.<\/p>\n
Some of the major functions like IFNULL(), ISNULL(), COALESCE() and NVL() which operate on NULL values are also discussed.<\/p>\n
We have understood the usage and the syntax with the help of examples for each function.<\/p>\n","protected":false},"excerpt":{"rendered":"
In this tutorial, we will understand the need and requirements of using the Null values. Let us now try to understand more about null values and null functions in SQL with examples. What are...<\/p>\n","protected":false},"author":6,"featured_media":87051,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[217,2619,7324,8987,8988,9177,9313,13465,13487,13499,13501,13504,13529,13552,13553,13554,13556],"class_list":["post-25082","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-access-sql-isnull","tag-coalesce-sql","tag-isnull-mysql","tag-mysql-isnull","tag-mysql-nvl","tag-nvl-in-sql","tag-oracle-isnull","tag-sql-ifnull","tag-sql-isnull","tag-sql-not-null","tag-sql-null-functions","tag-sql-nvl","tag-sql-select-null","tag-sql-server-is-not-null","tag-sql-server-isnull","tag-sql-server-nullif","tag-sql-server-nvl"],"yoast_head":"\nSQL Null Functions - ISNULL, IFNULL, Combine, & NULLIF - DataFlair<\/title>\n<meta name=\"description\" content=\"sql null functions,sql isnull,sql server isnull,mysql isnull,sql ifnull,isnull mysql,sql server nullif,SQL NVL,mysql nvl,Null Functions in SQL,example\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/sql-null-functions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Null Functions - ISNULL, IFNULL, Combine, & NULLIF - DataFlair\" \/>\n<meta property=\"og:description\" content=\"sql null functions,sql isnull,sql server isnull,mysql isnull,sql ifnull,isnull mysql,sql server nullif,SQL NVL,mysql nvl,Null Functions in SQL,example\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sql-null-functions\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-16T05:00:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-03-11T16:22:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Null-Function.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Null Functions - ISNULL, IFNULL, Combine, & NULLIF - DataFlair","description":"sql null functions,sql isnull,sql server isnull,mysql isnull,sql ifnull,isnull mysql,sql server nullif,SQL NVL,mysql nvl,Null Functions in SQL,example","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/sql-null-functions\/","og_locale":"en_US","og_type":"article","og_title":"SQL Null Functions - ISNULL, IFNULL, Combine, & NULLIF - DataFlair","og_description":"sql null functions,sql isnull,sql server isnull,mysql isnull,sql ifnull,isnull mysql,sql server nullif,SQL NVL,mysql nvl,Null Functions in SQL,example","og_url":"https:\/\/data-flair.training\/blogs\/sql-null-functions\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-08-16T05:00:33+00:00","article_modified_time":"2021-03-11T16:22:08+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Null-Function.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sql-null-functions\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sql-null-functions\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF","datePublished":"2018-08-16T05:00:33+00:00","dateModified":"2021-03-11T16:22:08+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-null-functions\/"},"wordCount":632,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-null-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Null-Function.jpg","keywords":["access SQL Isnull","coalesce SQL","isnull mysql","mysql isnull","mysql nvl","nvl in SQL","Oracle isnull","sql ifnull","sql isnull","sql not null","sql null functions","SQL NVL","sql select null","sql server is not null","sql server isnull","sql server nullif","sql server nvl"],"articleSection":["SQL Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sql-null-functions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sql-null-functions\/","url":"https:\/\/data-flair.training\/blogs\/sql-null-functions\/","name":"SQL Null Functions - ISNULL, IFNULL, Combine, & NULLIF - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-null-functions\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-null-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Null-Function.jpg","datePublished":"2018-08-16T05:00:33+00:00","dateModified":"2021-03-11T16:22:08+00:00","description":"sql null functions,sql isnull,sql server isnull,mysql isnull,sql ifnull,isnull mysql,sql server nullif,SQL NVL,mysql nvl,Null Functions in SQL,example","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sql-null-functions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sql-null-functions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sql-null-functions\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Null-Function.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Null-Function.jpg","width":1200,"height":628,"caption":"SQL Null Function"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sql-null-functions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Searching for Groups of Scala Regex","item":"https:\/\/data-flair.training\/blogs\/tag\/searching-for-groups-of-scala-regex\/"},{"@type":"ListItem","position":3,"name":"SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/25082","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=25082"}],"version-history":[{"count":5,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/25082\/revisions"}],"predecessor-version":[{"id":87058,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/25082\/revisions\/87058"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/87051"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=25082"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=25082"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=25082"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}