SQL ISNULL

The SQL ISNULL function is used to handle NULL values in a database. It allows users to replace NULL with a specified default value in query results, ensuring that missing or undefined data does not cause issues in calculations or display.

In this tutorial, we will explore the ISNULL function in SQL, its syntax, and how to use it effectively with real-world examples.

Syntax of SQL ISNULL Function

The basic syntax of the SQL ISNULL function is:

</>
Copy
SELECT ISNULL(column_name, replacement_value)
FROM table_name;

Explanation:

  • column_name: The column to check for NULL values.
  • replacement_value: The value that replaces NULL in the result.

Step-by-Step Examples Using SQL ISNULL

1 Handling NULL Values in a Query

Let’s create a students table to demonstrate the use of ISNULL:

</>
Copy
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    marks INT
);

Insert some sample data:

</>
Copy
INSERT INTO students (name, age, marks)
VALUES 
('Arjun', 16, 85),
('Ram', 17, NULL),
('Priya', 16, 90);

Now, let’s use ISNULL to replace NULL values in the marks column with a default value of 0:

</>
Copy
SELECT name, age, ISNULL(marks, 0) AS marks
FROM students;

Explanation:

  • The ISNULL function checks if marks is NULL.
  • If NULL, it replaces the value with 0.
  • This ensures that missing marks do not appear as NULL in the result set.

2 Using ISNULL in Calculations

Consider another scenario where we need to calculate the average marks of students, ensuring NULL values do not affect the calculation.

</>
Copy
SELECT name, 
       marks, 
       ISNULL(marks, 50) + 10 AS adjusted_marks
FROM students;

Explanation:

  • The ISNULL(marks, 50) replaces NULL with 50.
  • We then add 10 to the marks.
  • This ensures that calculations do not break due to NULL values.

Conclusion

The SQL ISNULL function is used in:

  • Replacing NULL values with default values.
  • Ensuring calculations do not fail due to NULL values.
  • Improving data readability and consistency in reports.