Comparison Operators With NULLs In SQL: IS NULL and IS NOT NULL

The NULLs in SQL play an essential role in defining the ‘unknown values’ from the database. In SQL, almost every database contains such types of values to represent the empty spaces. The comparison operators ( =, <>, <, > ) are widely used in SQL queries to solve complex models. In this article, we are going to study the different ways to use these comparison operators with NULLs. Let’s quickly take a glance at the basics of NULLs and Comparison operators, and then we see the various methods that correctly deal with the NULL values and comparison operator.

Also Read: MySQL NULL – What Does It Mean And How To Use?

Basics of NULL and Comparison Operators

NULL values in a database represent the ‘unknown values’ or ‘missing values’. These NULL values do not represent the empty string or zero value. These NULL values can be assigned to any data type in SQL.

Comparison operators such as =”, “<“, “>”, “<=”, “>=” are used to compare the data values from the databases. These comparison operators can not be used directly with the NULL values as it will result in an unknown value. There are special operators to handle the NULL values with comparison operators that are ‘IS NULL’ and ‘IS NOT NULL‘ operators.

IS NULL: The IS NULL operator is used to check if the values are NULL.
IS NOT NULL: The IS NOT NULL operator is used to check if the values are not NULL.

IS NULL Operator

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(50),
    Department VARCHAR(50),
    Salary INT
);

INSERT INTO Employees (EmployeeID, Name, Department, Salary)
VALUES 
    (1, 'A', 'IT', 5000),
    (2, 'B', NULL, 6000),
    (3, 'C', 'HR', NULL),
    (4, 'D', 'Marketing', NULL),
    (5, 'E', NULL, NULL);

SELECT * FROM Employees WHERE Department IS NULL;
Code language: SQL (Structured Query Language) (sql)

In this SQL query, we have used IS NULL operator to search NULL values from the department column. Let’s see the results to understand the working of the IS NULL operator.

IS NOT NULL Operator

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(50),
    Department VARCHAR(50),
    Salary INT
);

INSERT INTO Employees (EmployeeID, Name, Department, Salary)
VALUES 
    (1, 'A', 'IT', 5000),
    (2, 'B', NULL, 6000),
    (3, 'C', 'HR', NULL),
    (4, 'D', 'Marketing', NULL),
    (5, 'E', NULL, NULL);

SELECT * FROM Employees WHERE Salary IS NOT NULL;
Code language: SQL (Structured Query Language) (sql)

In this SQL query, we are implementing an IS NOT NULL operator to search a NON NULL values from the database. This time we are executing this technique on the Salary column.

Comparison Operators With NULLs

In SQL we can’t directly use the NULL values with the comparison operator. The direct use of a NULL value with a comparison operator will result in an empty set of values. To deal with this query we need to use the IS NULL or IS NOT NULL with comparison operator. Let’s try the first example without IS NULL and IS NOT NULL operators.

NULL Value With Comparison Operator

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(50),
    Department VARCHAR(50),
    Salary INT
);

INSERT INTO Employees (EmployeeID, Name, Department, Salary)
VALUES 
    (1, 'A', 'IT', 5000),
    (2, 'B', NULL, 6000),
    (3, 'C', 'HR', NULL),
    (4, 'D', 'Marketing', NULL),
    (5, 'E', NULL, NULL);
SELECT * FROM Employees WHERE Salary = NULL;
Code language: SQL (Structured Query Language) (sql)

In the results, you can see the query returns no values in the output. This is because it treats every value as an unknown value.

IS NULL With Comparison Operator

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(50),
    Department VARCHAR(50),
    Salary INT
);

INSERT INTO Employees (EmployeeID, Name, Department, Salary)
VALUES 
    (1, 'A', 'IT', 5000),
    (2, 'B', NULL, 6000),
    (3, 'C', 'HR', NULL),
    (4, 'D', 'Marketing', NULL),
    (5, 'E', NULL, NULL);
SELECT * FROM Employees WHERE Salary < 5000 OR Salary IS NULL;
Code language: SQL (Structured Query Language) (sql)

In this example, we have used the IS NULL operator with comparison operator (<). This query will compare the values from the salary column. If the salary is less than 5000 or NULL then it will be printed in the output. Let’s see the result for a better understanding.

IS NOT NULL With Comparison Operator

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(50),
    Department VARCHAR(50),
    Salary INT
);

INSERT INTO Employees (EmployeeID, Name, Department, Salary)
VALUES 
    (1, 'A', 'IT', 5000),
    (2, 'B', NULL, 6000),
    (3, 'C', 'HR', NULL),
    (4, 'D', 'Marketing', NULL),
    (5, 'E', NULL, NULL);
SELECT * FROM Employees WHERE Salary >= 5000 AND Salary IS NOT NULL;
Code language: SQL (Structured Query Language) (sql)

This example involves the implementation of the IS NOT NULL operator with the comparison operator (>=). This query will compare the values from the salary column. Employees with a salary greater than 5000 will be displayed in the output.

IS NOT NULL Operator With Comparison Operator Example — IS NOT NULL Operator With Comparison Operator

In this way, we can use any comparison operator with the NULL values without error.

Summary

This article focuses on the techniques to deal with the use of comparison operators with the NULL values in SQL. The NULL values always represent unknown values from the database. To compare such values with other data values we need special operators like IS NULL and IS NOT NULL. IS NULL operator will check for NULL values and IS NOT NULL checks for the NON NULL values. The combination of logical operators like AND, and OR will help you to implement the queries. The examples based on these techniques are also implemented in detail.

Reference

https://stackoverflow.com/questions/1075142/how-to-compare-values-which-may-both-be-null-in-t-sql