MySQL - IS NOT NULL Operator

MySQL: Mastering the IS NOT NULL Operator

In database management, a NULL value signifies that data is missing or unknown. It’s crucial to understand that NULL is distinct from a numerical value of zero (0) or an empty string (”). It represents the absence of any value.

To effectively handle NULL values in your queries, MySQL provides two specific operators:

• IS NULL: Checks if a value is NULL.
• IS NOT NULL: Checks if a value is not NULL.

This tutorial focuses on the `IS NOT NULL` operator, a fundamental tool for filtering records and ensuring data integrity.

The IS NOT NULL Operator

The IS NOT NULL operator is a conditional operator used in the WHERE clause of SELECT, UPDATE, and DELETE statements. Its purpose is to filter records, ensuring that a specific column contains an actual value and is not NULL.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NOT NULL;

Setup: Creating the Sample Table

Let’s set up a CUSTOMERS table to demonstrate the examples. Some records will intentionally have NULL values in the AGE and SALARY columns.

-- Create the CUSTOMERS table
CREATE TABLE CUSTOMERS(
    ID INT AUTO_INCREMENT,
    NAME VARCHAR(20) NOT NULL,
    AGE INT, -- Allows NULL values
    ADDRESS VARCHAR(255),
    SALARY DECIMAL(18, 2), -- Allows NULL values
    PRIMARY KEY(ID)
);

-- Insert sample data
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);

Example: Selecting Non-Null Records

The following query retrieves all customers whose age is known (i.e., the AGE column is not NULL).

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

Output

ID	NAME	AGE	SALARY
1	Ramesh	32	NULL
2	Khilan	25	1500.00
4	Chaitali	25	NULL
5	Hardik	27	8500.00
7	Muffy	24	10000.00

Advanced Usage with Other Statements

Using with COUNT()

You can combine IS NOT NULL with aggregate functions like COUNT(). Note that COUNT(*) counts all rows that match the WHERE clause, while COUNT(column_name) only counts the non-NULL values in that specific column.

This query counts customers with a recorded salary:

-- This counts rows where SALARY is not NULL.
SELECT COUNT(*) AS NumberOfSalariesRecorded
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;

-- A more direct way to achieve the same result:
SELECT COUNT(SALARY) AS NumberOfSalariesRecorded
FROM CUSTOMERS;

Using with UPDATE

You can use IS NOT NULL to conditionally update rows. For instance, let’s give a bonus to all customers who have a salary on record.

UPDATE CUSTOMERS
SET SALARY = SALARY * 1.10 -- 10% bonus
WHERE SALARY IS NOT NULL;

Using with DELETE

Similarly, you can use it to delete records. For example, to remove all customers with a recorded age:

-- Be careful with DELETE statements!
DELETE FROM CUSTOMERS
WHERE AGE IS NOT NULL;

Best Practices and Common Pitfalls

Pitfall: col != value vs. IS NOT NULL

A common mistake is trying to filter NULL values using comparison operators like <> or !=. NULL represents an unknown state, so it cannot be compared to any other value. Any comparison with NULL (e.g., AGE != 30 or AGE = NULL) results in UNKNOWN, and the row is excluded from the result set.

-- INCORRECT: This will NOT return customers with NULL age.
SELECT * FROM CUSTOMERS WHERE AGE != 32;

-- CORRECT: To check for non-null values, always use IS NOT NULL.
SELECT * FROM CUSTOMERS WHERE AGE IS NOT NULL;

Best Practice: Schema Design

Whenever possible, define columns as NOT NULL and provide a DEFAULT value during table creation. This enforces data integrity at the database level and reduces the need for NULL checks in your application logic.

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Using IS NOT NULL in Application Code

Here are modern, secure examples of how to execute an IS NOT NULL query from various programming languages. The key best practice is to use prepared statements (or parameterized queries) to prevent SQL injection.

Node.js (with mysql2)
Python (with mysql-connector-python)
Java (with JDBC)
PHP (with mysqli)



### Setup
Install the `mysql2` package: `npm install mysql2`

### Code
This example uses `async/await` for cleaner asynchronous code and a connection pool for better performance.

```javascript
// Filename: queryNotNull.js
const mysql = require('mysql2/promise');

async function getCustomersWithAge() {
  let connection;
  try {
    // Use a connection pool for efficient management of connections
    const pool = mysql.createPool({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'TUTORIALS',
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0
    });

    console.log('Connected to database!');

    const sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE IS NOT NULL";

    // The pool handles connection acquisition and release
    const [rows, fields] = await pool.query(sql);

    console.log('Customers with a recorded age:');
    if (rows.length === 0) {
      console.log('No records found.');
    } else {
      rows.forEach(row => {
        console.log(`ID: ${row.ID}, Name: ${row.NAME}, Age: ${row.AGE}`);
      });
    }

    // The pool should be closed when the application is shutting down
    await pool.end();

  } catch (error) {
    console.error('Database query failed:', error);
  }
}

getCustomersWithAge();

Output

Connected to database!
Customers with a recorded age:
ID: 1, Name: Ramesh, Age: 32
ID: 2, Name: Khilan, Age: 25
ID: 4, Name: Chaitali, Age: 25
ID: 5, Name: Hardik, Age: 27
ID: 7, Name: Muffy, Age: 24

Setup

Install the official MySQL connector: pip install mysql-connector-python

Code

This example uses a with statement to ensure the connection and cursor are closed automatically.

query_not_null.py

import mysql.connector
from mysql.connector import Error

def get_customers_with_age():
    """ Connects to MySQL and fetches customers with a non-null age. """
    try:
        # Use a context manager for the connection
        with mysql.connector.connect(
            host='localhost',
            user='root',
            password='password',
            database='TUTORIALS'
        ) as connection:
            print('Connected to database!')
            sql_query = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE IS NOT NULL"

            # Use a context manager for the cursor
            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(sql_query)
                results = cursor.fetchall()

                print('Customers with a recorded age:')
                if not results:
                    print('No records found.')
                else:
                    for row in results:
                        print(f"ID: {row['ID']}, Name: {row['NAME']}, Age: {row['AGE']}")

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

if __name__ == "__main__":
    get_customers_with_age()

Output

Connected to database!
Customers with a recorded age:
ID: 1, Name: Ramesh, Age: 32
ID: 2, Name: Khilan, Age: 25
ID: 4, Name: Chaitali, Age: 25
ID: 5, Name: Hardik, Age: 27
ID: 7, Name: Muffy, Age: 24

Setup

Ensure you have the MySQL JDBC driver (e.g., mysql-connector-java-8.x.x.jar) in your project’s classpath.

Code

This example uses try-with-resources to automatically manage resources like Connection, Statement, and ResultSet, preventing resource leaks.

QueryNotNull.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class QueryNotNull {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASS = "password";

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

        // Use try-with-resources for automatic resource management
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            System.out.println("Connected to database!");
            System.out.println("Customers with a recorded age:");

            boolean found = false;
            while (rs.next()) {
                found = true;
                int id = rs.getInt("ID");
                String name = rs.getString("NAME");
                int age = rs.getInt("AGE");
                System.out.printf("ID: %d, Name: %s, Age: %d%n", id, name, age);
            }
            if (!found) {
                System.out.println("No records found.");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Output

Connected to database!
Customers with a recorded age:
ID: 1, Name: Ramesh, Age: 32
ID: 2, Name: Khilan, Age: 25
ID: 4, Name: Chaitali, Age: 25
ID: 5, Name: Hardik, Age: 27
ID: 7, Name: Muffy, Age: 24

Setup

Ensure the mysqli extension is enabled in your php.ini file.

Code

This example uses modern, object-oriented mysqli with error checking.

query_not_null.php

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';

// Establish a connection using object-oriented style
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

// Check for connection errors
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

printf("Connected to database!\n");

$sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE IS NOT NULL";

// Execute the query
if ($result = $mysqli->query($sql)) {
    echo "Customers with a recorded age:\n";
    if ($result->num_rows > 0) {
        // Fetch results as an associative array
        while($row = $result->fetch_assoc()) {
            printf("ID: %d, Name: %s, Age: %d\n",
                $row["ID"],
                $row["NAME"],
                $row["AGE"]
            );
        }
    } else {
        echo "No records found.\n";
    }
    // Free result set
    $result->free();
} else {
    printf("Query failed: %s\n", $mysqli->error);
}

// Close the connection
$mysqli->close();

?>

Output

Connected to database!
Customers with a recorded age:
ID: 1, Name: Ramesh, Age: 32
ID: 2, Name: Khilan, Age: 25
ID: 4, Name: Chaitali, Age: 25
ID: 5, Name: Hardik, Age: 27
ID: 7, Name: Muffy, Age: 24