MySQL - IS NULL Operator

MySQL: Working with NULL Values

Understanding NULL in MySQL

In SQL, a NULL value represents the absence of data. It is fundamentally different from a zero (0), an empty string (”), or the string literal ‘NULL’. A NULL value indicates that the data is unknown, not applicable, or has not been provided.

Because NULL represents an unknown value, you cannot use standard comparison operators like =, <>, <, or > to test for it. Comparing any value to NULL using these operators will result in NULL, which is treated as ‘false’ in a WHERE clause. Instead, MySQL provides specific operators: IS NULL and IS NOT NULL.

The IS NULL Operator

The IS NULL operator is used to filter records where a specific column’s value is NULL.

Syntax

SELECT column_list
FROM table_name
WHERE column_name IS NULL;

Example: Setup

First, let’s create and populate a CUSTOMERS table for our examples. Note that the AGE and SALARY columns can accept NULL values.

CREATE TABLE CUSTOMERS (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    AGE INT, -- Allows NULL
    ADDRESS VARCHAR(255),
    SALARY DECIMAL(10, 2) -- Allows NULL
);

INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', NULL),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', NULL, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', NULL),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', NULL, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

Our initial table looks like this:

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	NULL
2	Khilan	25	Delhi	1500.00
3	Kaushik	NULL	Kota	2000.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	8500.00
6	Komal	NULL	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Example: SELECT with IS NULL

To find all customers whose age has not been recorded, we use IS NULL:

SELECT ID, NAME, AGE FROM CUSTOMERS
WHERE AGE IS NULL;

Output

ID	NAME	AGE
3	Kaushik	NULL
6	Komal	NULL

The IS NOT NULL Operator

Conversely, IS NOT NULL is used to filter records that have a non-NULL value in a column.

Example: Counting Non-Null Salaries

Let’s count how many customers have a recorded salary:

SELECT COUNT(*) AS NumberOfEmployeesWithSalary
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;

Output

NumberOfEmployeesWithSalary
5

Note: COUNT(SALARY) would also work and gives the same result, as aggregate functions like COUNT(column) ignore NULL values.

Handling NULLs with Functions (COALESCE, IFNULL)

Sometimes you need to replace NULL with a default value for display or calculation purposes. COALESCE and IFNULL are perfect for this.

COALESCE(value1, value2, ...): Returns the first non-NULL value in the list. IFNULL(expr1, expr2): If expr1 is not NULL, it returns expr1; otherwise, it returns expr2.

Example: Using COALESCE

SELECT NAME, COALESCE(SALARY, 0.00) AS AdjustedSalary
FROM CUSTOMERS;

This query displays each customer’s salary, showing 0.00 for those with a NULL salary.

The Null-Safe Equals Operator (<=>)

MySQL provides a special operator, <=> (Null-safe equal), that works like the standard = operator but returns 1 (true) if both operands are NULL, and 0 (false) if one is NULL. This can be useful in specific scenarios, though IS NULL is more common and readable for checks.

-- This will return all rows where AGE is NULL
SELECT * FROM CUSTOMERS WHERE AGE <=> NULL;

Practical Examples (UPDATE, DELETE)

UPDATE with IS NULL

Let’s update the salary for all customers who don’t have one to a default value of 2500.00.

UPDATE CUSTOMERS
SET SALARY = 2500.00
WHERE SALARY IS NULL;

After this query, two rows will be updated. You can verify this with SELECT * FROM CUSTOMERS;.

DELETE with IS NULL

Now, let’s delete any records where the age is unknown. Warning: This is a destructive operation.

DELETE FROM CUSTOMERS
WHERE AGE IS NULL;

This will remove the records for ‘Kaushik’ and ‘Komal’.

Common Pitfalls and Best Practices

• Never use = NULL or != NULL: This is the most common mistake. It will not work as expected. Always use IS NULL or IS NOT NULL.
• Be mindful of aggregate functions: Functions like SUM, AVG, COUNT(column) ignore NULLs. COUNT(*) counts all rows regardless of NULLs.
• Use NOT NULL constraints: If a column should always have a value, define it with a NOT NULL constraint during table creation to enforce data integrity.
• Consider default values: Instead of allowing NULLs, you might define a DEFAULT value for a column in your CREATE TABLE statement (e.g., status VARCHAR(20) NOT NULL DEFAULT 'pending').

Using IS NULL in Client Applications

When interacting with a database from an application, it’s crucial to use prepared statements to prevent SQL injection. Here’s how to safely perform a query to find records with NULL values.

Node.js (using mysql2/promise)

This example uses modern async/await syntax and the popular mysql2 library.

const mysql = require('mysql2/promise');

async function findCustomersWithNullAge() {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'TUTORIALS'
    });

    const sql = 'SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE IS NULL';
    const [rows, fields] = await connection.execute(sql);

    console.log('Customers with unknown age:');
    console.log(rows);
    // Output will be an array of objects, e.g., [ { ID: 3, NAME: 'Kaushik', AGE: null }, ... ]

  } catch (error) {
    console.error('Database query failed:', error);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

findCustomersWithNullAge();

Python (using mysql-connector-python)

This example uses a with statement to ensure resources are managed correctly.

import mysql.connector
from mysql.connector import Error

def find_customers_with_null_age():
    try:
        with mysql.connector.connect(
            host='localhost',
            user='root',
            password='password',
            database='TUTORIALS'
        ) as connection:
            with connection.cursor(dictionary=True) as cursor:
                query = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE IS NULL"
                cursor.execute(query)
                results = cursor.fetchall()

                print('Customers with unknown age:')
                for row in results:
                    print(row)
                # Output: {'ID': 3, 'NAME': 'Kaushik', 'AGE': None}

    except Error as e:
        print(f"Error connecting to MySQL: {e}")

find_customers_with_null_age()

Java (using JDBC)

This example uses a try-with-resources statement, which is a best practice for modern Java to automatically close connections.

import java.sql.*;

public class FindNullExample {
    private static final String URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        String sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE IS NULL";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            System.out.println("Customers with unknown age:");
            while (rs.next()) {
                int id = rs.getInt("ID");
                String name = rs.getString("NAME");
                // Use wasNull() to check if the last read value was a SQL NULL
                int age = rs.getInt("AGE");
                String ageDisplay = rs.wasNull() ? "NULL" : String.valueOf(age);

                System.out.printf("ID: %d, Name: %s, Age: %s\n", id, name, ageDisplay);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}