Home » SQL

NULL VALUES and NULL FUNCTIONS in SQL

In this article, we will learn about NULL VALUES and NULL FUNCTIONS in SQL.
Submitted by Bharti Parmar, on January 17, 2019

As you all know that NULL value means 0 (Zero), Empty cell, empty, missing or unknown data. If we do not fill any data in any field if it is not required or we can say that if that field is optional then by default, a table column hold NULL value.

Sometime, NULL values are treated differently from other values. It is used as a placeholder for unknown or inapplicable values and It is not possible to test for NULL values with comparison operators ( =, <, or <>).

Example:

In this table you can see as an example of NULL VALUES. Here, enroll is a primary key and S_name these two column are required field that is why these two column cannot be NULL and Address & Contact columns in a Student is optional so it can create a NULL VALUES. As shown in example below:

If, you want to find out the NULL VALUE then we can use “ IS “ operator:

Like: here, we want to find out the NULL VALUE in Address column then:

    SELECT Enroll, S_name FROM Student WHERE Address IS NULL;

Similarly, if you want to find out the NOT NULL VALUE in Address column then:

    SELECT S_name FROM Student WHERE Address IS NOT NULL;

Null functions

    SELECT Dept_name,Dept_Info*(Admission+New_Student) FROM University;

We can also use NVL(), IFNULL(), and COALESCE() functions to find IS NULL or NOT NULL VALUES.

Different database platform uses different syntax and function:

• MS Access

    SELECT S_name, Dept_info*(Admission+IIF(New_student),0,New_student)) 
    FROM University;

• SQL Server

    SELECT s_name, Dept_info*(Admission+ISNULL(New_student,0)) 
    FROM University;

• Oracle
  Oracle does not have an ISNULL() function. However, we can use the NVL() function to achieve the same result:

    SELECT S_name, Dept_info*(Admission+NVL(New_student,0)) 
    FROM University;

• MySQL
  MySQL does have an ISNULL() function. That's why we use these IFNULL function:

    SELECT S_name, Dept_info*(Admission+IFNULL(New_student,0)) 
    FROM University;

or we can use the COALESCE() function, like this:

    SELECT s_name, Dept_info*(Admission+COALESCE(New_student,0)) 
    FROM University;

Conclusion:

In this article, we have learnt NULL VALUES and its different function on different database. I hope you understand the concept behind it. If you have any doubt/query, feel free to ask in comment box.