Kenhuang Academy
  • English

      • MySQL NULL Values

      Overview

      NULL represents missing or unknown data in MySQL. Understanding how to work with NULL values is essential for writing correct queries and maintaining data integrity.

      Understanding NULL

      • NULL: No value or unknown value
      • Not Zero: NULL is different from 0
      • Not Empty String: NULL is different from ''
      • Comparison: NULL comparisons require special operators

      NULL vs Empty Values

      Comparison Table

      | Value |-------------|-------------------|-----------------------|------------|------------| | NULL | TRUE | FALSE | NULL | NULL | | '' (empty string) | FALSE | TRUE | TRUE | FALSE | | 0 | FALSE | TRUE | FALSE | TRUE |

      Examples

      CREATE TABLE null_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(255),
    phone VARCHAR(20),
    age INT
);

INSERT INTO null_demo (name, email, phone, age) VALUES
    ('John', 'john@example.com', '555-1234', 30),
    ('Jane', NULL, '555-5678', NULL),
    ('Bob', 'bob@example.com', NULL, 25),
    ('Alice', NULL, NULL, NULL);

-- Check NULL values
SELECT 
    name,
    email,
    phone,
    age,
    IF(email IS NULL, 'No email', email) AS email_display,
    IFNULL(phone, 'Not provided') AS phone_display
FROM null_demo;

      Testing for NULL

      IS NULL and IS NOT NULL

      -- Find rows with NULL values
SELECT * FROM null_demo WHERE email IS NULL;

-- Find rows without NULL values
SELECT * FROM null_demo WHERE email IS NOT NULL;

-- Multiple NULL checks
SELECT * FROM null_demo 
WHERE email IS NULL 
    AND phone IS NULL;

      COALESCE Function

      -- Return first non-NULL value
SELECT 
    name,
    COALESCE(phone, mobile_phone, 'No phone') AS contact_phone,
    COALESCE(email, work_email, 'No email') AS contact_email
FROM users;

-- Nested COALESCE
SELECT 
    COALESCE(
        primary_contact,
        secondary_contact,
        emergency_contact,
        'No contact available'
    ) AS contact_person
FROM employees;

      IFNULL Function

      -- Return second value if first is NULL
SELECT 
    name,
    IFNULL(discount, 0) AS discount,
    IFNULL(shipping, 5.99) AS shipping_cost
FROM orders;

-- In calculations
SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS total,
    price * quantity * COALESCE(discount_percent, 100) / 100 AS final_total
FROM order_items;

      NULL in Comparisons

      NULL Comparison Behavior

      -- These comparisons return NULL, not TRUE or FALSE
SELECT NULL = NULL;         -- Returns NULL
SELECT NULL != NULL;        -- Returns NULL
SELECT NULL < 10;           -- Returns NULL
SELECT NULL > 10;           -- Returns NULL
SELECT NULL + 10;          -- Returns NULL
SELECT NULL * 10;          -- Returns NULL

-- Use IS NULL instead
SELECT NULL IS NULL;         -- Returns TRUE
SELECT NULL IS NOT NULL;     -- Returns FALSE

      NULL in WHERE Clauses

      -- Correct: Use IS NULL
SELECT * FROM users WHERE phone IS NULL;

-- Incorrect: Will not work as expected
SELECT * FROM users WHERE phone = NULL;

-- Multiple NULL conditions
SELECT * FROM users 
WHERE phone IS NULL 
    AND email IS NOT NULL;

-- Using OR with NULL
SELECT * FROM users 
WHERE email IS NULL 
    OR email = '';

      NULL in Expressions

      Arithmetic Operations

      -- Any operation with NULL returns NULL
SELECT 
    price,
    quantity,
    discount,
    price * quantity * discount AS total
FROM orders;
-- If discount is NULL, total is NULL

-- Use IFNULL or COALESCE to handle
SELECT 
    price,
    quantity,
    COALESCE(discount, 1) AS discount_factor,
    price * quantity * COALESCE(discount, 1) AS total
FROM orders;

      String Operations

      -- Concatenation with NULL
SELECT 
    first_name,
    last_name,
    CONCAT(first_name, ' ', last_name) AS full_name,
    CONCAT(first_name, ' ', middle_name, ' ', last_name) AS full_with_middle
FROM names;
-- If middle_name is NULL, full_with_middle is NULL

-- Use CONCAT_WS (skip NULL)
SELECT 
    CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name_ws
FROM names;
-- CONCAT_WS skips NULL values

      NULLIF Function

      -- Return NULL if two expressions are equal
SELECT 
    price,
    discount_price,
    NULLIF(price, discount_price) AS price_difference
FROM products;
-- Returns NULL if price equals discount_price

-- Use in calculations
SELECT 
    price,
    discount,
    COALESCE(NULLIF(discount, 0), 0.1) AS effective_discount
FROM products;

      NULL in Aggregations

      COUNT and NULL

      -- COUNT(*) includes NULL
-- COUNT(column) excludes NULL
SELECT 
    COUNT(*) AS total_rows,
    COUNT(email) AS emails_provided,
    COUNT(phone) AS phones_provided,
    COUNT(*) - COUNT(email) AS missing_emails,
    COUNT(*) - COUNT(phone) AS missing_phones
FROM users;

      SUM, AVG, and NULL

      -- Aggregate functions ignore NULL
SELECT 
    COUNT(*) AS total_orders,
    COUNT(amount) AS orders_with_amount,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount
FROM orders;
-- SUM and AVG only include non-NULL values

-- With NULL handling
SELECT 
    COUNT(*) AS total_orders,
    SUM(COALESCE(amount, 0)) AS total_amount_all,
    AVG(COALESCE(amount, 0)) AS avg_amount_all
FROM orders;

      GROUP BY and NULL

      -- NULL values are grouped together
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
-- All NULL departments are in one group

-- Handle NULL in grouping
SELECT 
    COALESCE(department, 'Unassigned') AS department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY COALESCE(department, 'Unassigned');

      NULL in ORDER BY

      -- NULL values in sorting
SELECT * FROM products 
ORDER BY price;  -- NULLs appear first (ascending) or last (descending)

-- Control NULL placement (MySQL 8.0+)
SELECT * FROM products 
ORDER BY price ASC NULLS FIRST;

SELECT * FROM products 
ORDER BY price DESC NULLS LAST;

-- Custom NULL ordering (older versions)
SELECT * FROM products 
ORDER BY IF(price IS NULL, 1, 0), price;

      NULL in UNIQUE Constraints

      UNIQUE and NULL Behavior

      -- Multiple NULL values allowed in UNIQUE column
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE,
    phone VARCHAR(20) UNIQUE
);

-- These are allowed
INSERT INTO users (email, phone) VALUES (NULL, '555-1234');
INSERT INTO users (email, phone) VALUES (NULL, '555-5678');
INSERT INTO users (email, phone) VALUES ('user1@example.com', NULL);
INSERT INTO users (email, phone) VALUES ('user2@example.com', NULL);
-- Multiple NULL emails are allowed

-- But duplicate non-NULL values are not
INSERT INTO users (email, phone) VALUES ('user1@example.com', '555-9999');
-- Error: Duplicate entry

      NULL in FOREIGN KEY Constraints

      -- NULL allowed in foreign key
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Orders without customer (NULL customer_id allowed)
INSERT INTO orders (order_date, customer_id) VALUES 
    ('2024-01-15', NULL),
    ('2024-01-16', NULL);

-- Find orders without customer
SELECT * FROM orders WHERE customer_id IS NULL;

-- Find orders with customer
SELECT * FROM orders WHERE customer_id IS NOT NULL;

      NULL Handling Functions

      ISNULL Function

      -- Test if expression is NULL
SELECT 
    name,
    phone,
    ISNULL(phone) AS phone_is_null,
    IFNULL(phone, 'Not provided') AS phone_display
FROM users;

      NVL Function (Oracle compatibility)

      -- NVL equivalent to IFNULL
SELECT 
    price,
    discount,
    NVL(discount, 0) AS effective_discount
FROM products;

      LEAST and GREATEST with NULL

      -- LEAST and GREATEST return NULL if any argument is NULL
SELECT 
    price1,
    price2,
    price3,
    LEAST(price1, price2, price3) AS lowest_price,
    GREATEST(price1, price2, price3) AS highest_price
FROM price_comparisons;

-- Use COALESCE to handle NULL
SELECT 
    GREATEST(
        COALESCE(price1, 0),
        COALESCE(price2, 0),
        COALESCE(price3, 0)
    ) AS highest_price
FROM price_comparisons;

      NULL in Views

      -- Views can contain NULL values
CREATE VIEW employee_summary AS
SELECT 
    e.name,
    e.department,
    e.salary,
    COALESCE(d.manager_name, 'No manager') AS manager
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- Query view
SELECT * FROM employee_summary;

      NULL in Stored Procedures

      DELIMITER //
CREATE PROCEDURE get_user_details(
    IN user_id INT,
    OUT user_name VARCHAR(100),
    OUT user_email VARCHAR(255),
    OUT user_phone VARCHAR(20)
)
BEGIN
    SELECT 
        name,
        email,
        phone
    INTO user_name, user_email, user_phone
    FROM users
    WHERE id = user_id;
    
    -- Set default if NULL
    IF user_email IS NULL THEN
        SET user_email = 'Not provided';
    END IF;
    
    IF user_phone IS NULL THEN
        SET user_phone = 'Not provided';
    END IF;
END //
DELIMITER ;

-- Call procedure
CALL get_user_details(1, @name, @email, @phone);
SELECT @name, @email, @phone;

      NULL Best Practices

      Design Recommendations

      -- Use NULL for optional data
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255),        -- Optional: can be NULL
    phone VARCHAR(20),        -- Optional: can be NULL
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Use DEFAULT values instead of NULL when appropriate
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status VARCHAR(20) DEFAULT 'pending',  -- Better than NULL
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

      Query Performance

      -- Index columns with NULL
-- NULL values can affect index usage
CREATE INDEX idx_users_email ON users(email);

-- Use IS NOT NULL for indexed columns
SELECT * FROM users WHERE email IS NOT NULL;

      Data Integrity

      -- Use NOT NULL for required fields
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,  -- Required
    email VARCHAR(255) NOT NULL,     -- Required
    phone VARCHAR(20),               -- Optional
    birth_date DATE                  -- Optional
);

      Summary

      Handling NULL values properly is essential for:

      • Data Accuracy: NULL represents missing data
      • Query Correctness: Use IS NULL instead of = NULL
      • Data Integrity: NOT NULL constraints for required fields
      • Display: COALESCE and IFNULL for user-friendly output
      • Calculations: Handle NULL in arithmetic operations

      Previous: JOINs

      Next: Regular Expressions