MySQL - NULL Values

MySQL: Working with NULL Values

Understanding NULL

In MySQL, NULL is a special marker used to indicate that a data value does not exist in the database. It is fundamentally different from a zero (0) for numeric types or an empty string (”) for string types. A NULL value represents missing, unknown, or inapplicable data.

A key concept to remember is that NULL is not equal to anything, not even itself. Standard comparison operators like =, !=, <, or > cannot be used to test for NULL. You must use the IS NULL or IS NOT NULL operators.

Defining Columns with NOT NULL

To enforce that a column must always have a value, you use the NOT NULL constraint during table creation. Attempting to insert a NULL value (or omitting the column from an INSERT statement without a DEFAULT value) into a NOT NULL column will result in an error.

Syntax

CREATE TABLE table_name (
    column1_name datatype NOT NULL,
    column2_name datatype, -- This column can be NULL
    ...
);

Example

Let’s create a products table where name and price are mandatory, but description is optional.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    description TEXT, -- Can be NULL
    launch_date DATE -- Can be NULL
);

Now, let’s insert some records. Notice that we can omit description and launch_date, which will result in them being NULL.

INSERT INTO products (name, price, description, launch_date) VALUES
    ('Laptop Pro', 1200.00, 'A powerful laptop for professionals.', '2023-01-15'),
    ('Wireless Mouse', 25.50, NULL, '2023-02-20'),
    ('Mechanical Keyboard', 150.00, 'RGB-lit mechanical keyboard.', NULL),
    ('USB-C Hub', 45.00, NULL, NULL);

Querying for NULL Values

To find rows where a specific column contains NULL, use the IS NULL operator.

-- Find products with a missing description
SELECT id, name, price FROM products WHERE description IS NULL;

This query returns the ‘Wireless Mouse’ and ‘USB-C Hub’.

To find rows where a value is present, use IS NOT NULL.

-- Find products that have a launch date
SELECT name, launch_date FROM products WHERE launch_date IS NOT NULL;

This query returns the ‘Laptop Pro’ and ‘Wireless Mouse’.

Handling NULL in Queries and Functions

Often, you don’t want to display NULL in your application’s output. MySQL provides functions to substitute NULL values.

Using IFNULL() and COALESCE()

IFNULL(expr1, expr2) returns expr1 if it is not NULL, otherwise it returns expr2. COALESCE(value1, value2, ...) returns the first non-NULL value in its argument list.

-- Display 'No description' for products with a NULL description
SELECT
    name,
    IFNULL(description, 'No description available') AS product_description
FROM products;

COALESCE is more flexible when you have multiple potential fallbacks:

-- Use a short description if available, otherwise a generic note, otherwise 'N/A'
SELECT name, COALESCE(short_description, generic_note, 'N/A') AS display_note FROM products;

Aggregate Functions and NULL

Most aggregate functions like SUM(), AVG(), MIN(), and MAX() simply ignore NULL values. COUNT(column_name) counts non-NULL values in that column, while COUNT(*) counts all rows regardless of NULL values.

SELECT
    COUNT(*) AS total_products,
    COUNT(launch_date) AS products_with_launch_date,
    AVG(price) AS average_price -- Averages the 4 prices
FROM products;

Modifying and Deleting Rows with NULL Values

You can use the IS NULL condition in UPDATE and DELETE statements to target specific rows.

Updating NULL Values

-- Set a launch date for all products that don't have one
UPDATE products
SET launch_date = '2023-11-01'
WHERE launch_date IS NULL;

Deleting Rows with NULL Values

-- Delete products that have no description
DELETE FROM products
WHERE description IS NULL;

Common Pitfall: column = NULL

A frequent mistake for beginners is trying to use the equality operator with NULL. The expression column = NULL will always evaluate to NULL (which is treated as false in a WHERE clause), not true or false. It will never find any rows.

-- THIS IS INCORRECT AND WILL NOT WORK AS EXPECTED
SELECT * FROM products WHERE description = NULL; -- Returns an empty set

-- THIS IS THE CORRECT WAY
SELECT * FROM products WHERE description IS NULL;

Practical Application: Handling NULL in Code

When retrieving data, your application code needs to be prepared to handle NULL values, which often map to special types like None in Python, null in JavaScript/PHP, or null in Java.

Node.js (with mysql2)

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

async function getProducts() {
  let connection;
  try {
    connection = await mysql.createConnection({ /* connection details */ });
    const [rows] = await connection.execute('SELECT name, description FROM products');

    rows.forEach(product => {
      // The 'description' field will be `null` if it's NULL in the database
      const description = product.description || 'Not available';
      console.log(`${product.name}: ${description}`);
    });

  } catch (error) {
    console.error('Failed to fetch products:', error);
  } finally {
    if (connection) await connection.end();
  }
}

getProducts();

Python (with mysql-connector-python)

import mysql.connector

def get_products():
    try:
        with mysql.connector.connect(/* connection details */) as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute('SELECT name, description FROM products')
                for product in cursor.fetchall():
                    # The 'description' key will have a value of `None` if it's NULL
                    description = product.get('description') or 'Not available'
                    print(f"{product['name']}: {description}")
    except mysql.connector.Error as err:
        print(f'Error: {err}')

get_products()

Java (with JDBC and try-with-resources)

import java.sql.*;

public class NullValueDemo {
    public void getProducts() {
        String url = "jdbc:mysql://localhost:3306/your_database_name";
        String user = "root";
        String password = "password";

        String sql = "SELECT name, description FROM products";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                String name = rs.getString("name");
                String description = rs.getString("description");

                // rs.getString() returns null if the DB value is NULL
                if (rs.wasNull()) {
                    description = "Not available";
                }
                System.out.println(name + ": " + description);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}