What are SQL NULL functions?

Overview

Null values act as placeholders when we encounter missing information or unavailable data. These null values are not part of any DataType. Therefore, it's a flexible data type.

When we work with databases, we may encounter situations, where we have to leave an empty column value. Then, SQLStructured Query Language provides us with multiple NULL functions to perform different operations.

Let’s execute all these functions using different coding examples.

ISNULL()function

The ISNULL()function is used to check whether the specified expression has null values. It returns 1 when the expression is null. Otherwise, it returns 0.

Syntax

SELECT _column(s), ISNULL(expression) FROM table_name;

Parameter

  • expression: It can be either a single value or a table column.

Return value

The ISNULL() function returns either 1 or 0. If the expression contains a null value it returns 1, otherwise 0.

Code

Let's understand this concept with the help of a coding example. Here, we have a School database and a students table to keep their record like ID or name.

-- Creating a school database

CREATE DATABASE School;

-- using school databases

USE School;

-- creating a student table in school database

CREATE TABLE students (

  id INTEGER PRIMARY KEY,

  _name TEXT NOT NULL,

  gender TEXT NOT NULL,

  age INTEGER NULL,

  marks INTEGER NULL);

-- inserting values in above created student table

INSERT INTO students VALUES (11, 'John', 'M', 23, 80);

INSERT INTO students VALUES (22, 'Julia', 'F', 18, 50);

INSERT INTO students VALUES (23, 'Hema', 'F',NULL, 90);

INSERT INTO students VALUES (24, 'Jonathan','M', 20,NULL);

-- query

SELECT _name, ISNULL(age) as age FROM students;

Explanation

  • Line 2: We create a School database.
  • Line 4: We use the School database.
  • Lines 6 to 16: We create a students table with multiple fields like ID, name, age, and marks. And insert data in it.
  • Line 18: We use the ISNULL(age) function to filter out such students record(s) whose age attribute is NULL.

IFNULL()function

The IFNULL function is used to replace the current NULL value with the new value.

Parameter

As the function takes two parameters:

  • column_name: The name of the column.
  • mvalue_to_replace: The value that is to be replaced.

Return value

If the column value is not null then it returns that value. Otherwise, it replaces that NULL value with the given value.

Syntax

SELECT _column(s), IFNULL(column_name, value_to_replace) FROM table_name;

Code

Let’s understand this concept with the help of a coding example. Here, we have a School database and a students table to hold its record like ID or name.

-- Creating a school database
CREATE DATABASE School;
-- using school databases
USE School;
-- creating a student table in school database
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  _name TEXT NOT NULL,
  gender TEXT NOT NULL,
  age INTEGER NULL,
  marks INTEGER NULL
);
-- inserting values in above created student table
INSERT INTO students VALUES (11, 'John', 'M', 23, 80);
INSERT INTO students VALUES (22, 'Julia', 'F', 18, 50);
INSERT INTO students VALUES (23, 'Hema', 'F',NULL, 90);
INSERT INTO students VALUES (24, 'Jonathan','M', 20,NULL);
SELECT id, _name, IFNULL(age, 0) as age FROM students;

Explanation

  • Line 2: We create a School database.
  • Line 4: We use the School database.
  • Lines 6 to 17: We create a students table with multiple fields like ID, name, age, and marks. And insert data in it.
  • Line 19: We use the IFNULL(age, 0) to return the value(s) of age attribute and replace NULL values with 0.

COALESCE()function

The COALESCE() function takes a list of arguments or parameters. It returns the first non-null value from the given column names.

Syntax

SELECT _column(s), CAOLESCE(first_expression,....,nth_expression) FROM table_name;

Code

Let’s understand this concept with the help of a coding example. Here, we have a School database and a students table to hold its record like ID or name.

-- Creating a school database
CREATE DATABASE School;
-- using school databases
USE School;
-- creating a student table in school database
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  _name TEXT NOT NULL,
  gender TEXT NOT NULL,
  age INTEGER NULL,
  marks INTEGER NULL
);
-- inserting values in above created student table
INSERT INTO students VALUES (11, 'John', 'M', 23, 80);
INSERT INTO students VALUES (22, 'Julia', 'F', NULL, NULL);
INSERT INTO students VALUES (23, 'Hema', 'F',NULL, 90);
INSERT INTO students VALUES (24, 'Jonathan','M', 20,NULL);
SELECT _name, COALESCE(age, marks) as NonNULL FROM students;

Explanation

  • Line 2: We create a School database.
  • Line 4: We use the School database.
  • Line 6 to 17: We create a students table with multiple fields like ID, name, gender, age, and marks. And insert data in it.
  • Line 19: We use COALESCE(age, marks) to get such record(s) that have non-null values from the given age and marks columns.

NULLIF()function

The NULLIF() function takes two parameters and compares their values. If both the values are equal, then it will return NULL. Otherwise, it will display the first value against that column.

Syntax

SELECT _column(s), NULLIF(first_expression, second_expression) FROM table_name;

Code

Let’s understand this concept with the help of a coding example. Here, we have a School database and a students table to hold its record like ID, name, gender, etc.

-- Creating a school database
CREATE DATABASE School;
-- using school databases
USE School;
-- creating a student table in school database
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  _name TEXT NOT NULL,
  gender TEXT NOT NULL,
  age INTEGER NULL,
  marks INTEGER NULL
);
-- inserting values in above created student table
INSERT INTO students VALUES (11, 'John', 'M', 23, 80);
INSERT INTO students VALUES (22, 'Julia', 'F', NULL, NULL);
INSERT INTO students VALUES (23, 'Hema', 'F',NULL, 90);
INSERT INTO students VALUES (24, 'Salopek','M', 20, NULL);
SELECT _name, NULLIF(age, marks) age FROM students;

Explanation

  • Line 2: We create a School database.
  • Line 4: We use the School database.
  • Lines 6 to 17: We create a students table with multiple fields like ID, name, gender, age, and marks. And insert data in it.
  • Line 19: We use the NULLIF() function to get NULL if both age and marks are equal. Otherwise the first argument value is returned, that is, age.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)