MySQL: IS NULL and IS NOT NULL operators – Explained with examples

Overview

Working with databases often involves dealing with null values. In MySQL, this is no different. Understanding how to properly handle NULL values is crucial as they often can represent the absence of data or that a certain piece of data is not applicable. This tutorial will explore the IS NULL and IS NOT NULL operators in MySQL, which are used to test for NULL or non-NULL values in your database tables.

What is a NULL Value in MySQL’s World?

Before diving into the operators themselves, let’s first understand what NULL represents in MySQL. A NULL value is an absence of a value. It is important to distinguish that NULL is not the same as zero, an empty string, or a false boolean. It signifies that the value is unknown or not applicable.

Using IS NULL Operator

The IS NULL operator in MySQL is used to test for NULL values. It returns true if the given column value is NULL.

SELECT column_names FROM table_name WHERE column_name IS NULL;

In this basic example, any rows where ‘column_name’ is NULL will be returned. Let’s consider a practical example with a table Employees.

SELECT EmployeeID, Name, EndDate FROM Employees WHERE EndDate IS NULL;

This query will return all employees that are currently employed since their ‘EndDate’ is NULL.

Using IS NOT NULL Operator

The IS NOT NULL operator, conversely, lets you find rows where a column value is not NULL.

SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

For example:

SELECT EmployeeID, Name, EndDate FROM Employees WHERE EndDate IS NOT NULL;

This will return all employees that have an ‘EndDate’ and are no longer employed by the company.

Checking for NULL with Other Conditions

IS NULL and IS NOT NULL operators can be used in conjunction with other conditions using the AND/OR logical operators.

SELECT * FROM Employees WHERE EndDate IS NULL AND Department='Sales';

This will return employees from the Sales department who are currently employed.

IS NULL with ORDER BY and GROUP BY

It’s also possible to combine IS NULL or IS NOT NULL operators with ORDER BY and GROUP BY clauses to order or group your results based on whether a column has NULL values or not.

SELECT COUNT(EmployeeID), Department FROM Employees WHERE EndDate IS NULL GROUP BY Department ORDER BY COUNT(EmployeeID) DESC;

Advanced Use: Joining Tables with NULL Values

A more advanced use of the IS NULL operator could involve JOIN operations. For example, if you want to find all customers who have not placed an order, you might do something like:

SELECT Customers.CustomerID, Customers.Name FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.OrderID IS NULL;

This will give you a list of customers without any associated orders.

NULL Values in Expressions

When you’re using expressions involving potential null values, use the COALESCE function to handle the nulls accordingly.

SELECT EmployeeID, COALESCE(EndDate, 'Still employed') AS EmploymentStatus FROM Employees;

This replaces NULL end dates with ‘Still employed’ status in the result.

Remember, comparisons with NULL using the standard operators (=, !=, <, etc.) will always give you NULL result since NULL cannot be equal to or different from anything, as it’s the absence of a value.

Traps to Avoid:

• Never use ‘column_name = NULL’ as it will never match any row. Always use ‘IS NULL’.
• Similarly, ‘column_name != NULL’ is also incorrect. Use ‘IS NOT NULL’ instead.

Testing for NULL in Stored Routines

When writing stored procedures or functions, you can also test for NULL values:

DELIMITER $
CREATE PROCEDURE GetActiveEmployeesCount()
BEGIN
    SELECT COUNT(*) FROM Employees WHERE EndDate IS NULL;
END$
DELIMITER ;

This stored procedure counts how many employees are active.

Setting Defaults to Avoid NULLs

When designing your database tables, you can set default values to columns to avoid NULLs:

CREATE TABLE Employees (
    EmployeeID int NOT NULL,
    Name varchar(255) NOT NULL,
    Department varchar(255) NOT NULL DEFAULT 'General',
    StartDate date NOT NULL,
    EndDate date DEFAULT NULL
);

In this table structure, ‘Department’ will default to ‘General’ if a value isn’t specified during a record insertion.

Conclusion

Understanding and leveraging the IS NULL and IS NOT NULL operators in MySQL allow you to elegantly handle NULL values in your database queries. Whether you are selecting, updating, inserting, or deleting data, these operators help you address the situations where the existence or absence of a value is critical to your application’s logic.