SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF

What are NULL Values in SQL?

Null values are the placeholders in the database when we have the data missing, or the required data is not available.

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.

Null values come in handy while the cleaning of data during the exploratory analysis of data.

Null values help us in removing the ambiguity arising in data. Also, null values are beneficial to maintain uniform datatype across the column.

Example:

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.

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.

Why do we need NULL Functions?

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.

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:

Sr.No Function Description
1 ISNULL() Helps us to replace NULL values with the desired value.
2 IFNULL() Allows us to return the first value if the value is NULL, and otherwise returns the second value.
3 COALESCE() Helps us to return the first non-null values in the arguments.
4 NVL() Helps to replace the NULL value with the desired value given by the user.
Demo Database

Let us now have a look at our demo database – ‘IBM’

Query:

SELECT * FROM IBM;

Examples
  1. Let us see the use of the IFNULL() function.
    Syntax:

    SELECT col1,col2, IFNULL(col3, value_to_be_replaced) FROM tableName;

  2. Let us see the use of ISNULL() function.
    Syntax:

    SELECT col1,col2, ISNULL(col3) FROM tableName;

  3. Let us see the use of COALESCE() function.
    Syntax:

    SELECT col1,col2, COALESCE(col3, value_to_be_replaced) FROM tableName;

  4. Let us see the use of NVL() function.
    Syntax:

    SELECT col1,col2, NVL(col3) FROM tableName;


Copyright 1999- Ducat Creative, All rights reserved.

Anda bisa mendapatkan server slot online resmi dan terpercaya tentu saja di sini. Sebagai salah satu provider yang menyediakan banyak pilihan permainan.