SQL - NULL Values

SQL - Handling NULL Values

1. Understanding NULL: The Concept of ‘Unknown’

In SQL, NULL is a special marker used to indicate that a data value does not exist in the database. It is crucial to understand that NULL is not the same as a zero (0), an empty string (''), or a boolean false. It represents a missing or unknown value.

Analogy: Think of a form with a field for ‘Middle Name’. If a person has no middle name, the field is left blank. This absence of a value is what NULL represents. It’s not that their middle name is ‘zero’ or an empty word; it’s simply unknown or not applicable.

2. The Pitfall of Comparison: NULL and Three-Valued Logic

A fundamental rule in SQL is that you cannot use standard comparison operators (=, !=, <, >) to test for NULL. This is because any arithmetic or comparison operation involving a NULL results in NULL (unknown).

-- These comparisons will NOT work as expected
SELECT * FROM customers WHERE salary = NULL; -- Incorrect
SELECT * FROM customers WHERE salary != NULL; -- Incorrect

The result of NULL = NULL is not TRUE, it is UNKNOWN. To correctly check for NULL values, you must use the IS NULL and IS NOT NULL operators.

3. Defining Columns: NOT NULL Constraint

By default, a column in a table can hold NULL values. To enforce that a column must always have a value, you use the NOT NULL constraint during table creation.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL, -- This column cannot be NULL
    department VARCHAR(50) NOT NULL,   -- This column also cannot be NULL
    last_known_address VARCHAR(255)    -- This column CAN be NULL
);

Attempting to insert a row without a value for first_name or department (or explicitly setting them to NULL) will result in a database error.

4. Filtering for NULLs: IS NULL and IS NOT NULL

Let’s use a sample customers table where some salary information is missing.

CREATE TABLE customers(
    ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(255) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(255),
    SALARY DECIMAL(18, 2)
);

INSERT INTO customers VALUES
    (1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
    (2, 'Khilan', 25, 'Delhi', 1500.00),
    (3, 'Kaushik', 23, 'Kota', 2000.00),
    (4, 'Chaitali', 25, 'Mumbai', 6500.00),
    (5, 'Hardik', 27, 'Bhopal', 8500.00),
    (6, 'Komal', 22, 'Hyderabad', NULL), -- Salary is unknown
    (7, 'Muffy', 24, 'Indore', NULL);      -- Salary is unknown

To find all customers with a known salary, use IS NOT NULL:

SELECT ID, NAME, SALARY FROM customers WHERE SALARY IS NOT NULL;

To find all customers whose salary is missing, use IS NULL:

SELECT ID, NAME FROM customers WHERE SALARY IS NULL;

5. Handling NULLs in Practice: UPDATE and DELETE

You can use IS NULL in the WHERE clause of UPDATE and DELETE statements to target rows with missing data. For example, let’s assign a default salary of 5000.00 to all customers with a NULL salary.

-- Update all records where salary is currently missing
UPDATE customers
SET SALARY = 5000.00
WHERE SALARY IS NULL;

Similarly, you could delete records with missing critical information:

-- This would delete Komal and Muffy from the table
-- Be careful with DELETE statements!
DELETE FROM customers WHERE SALARY IS NULL;

6. Replacing NULLs for Calculations: COALESCE() and Other Functions

When performing calculations or displaying data, NULL values can be problematic. SQL provides functions to substitute NULLs with a default value. The most standard function is COALESCE().

COALESCE(value1, value2, ...) returns the first non-NULL value in its argument list. It’s perfect for providing a default.

-- Display all customers, but show 0.00 if salary is NULL
SELECT
    NAME,
    COALESCE(SALARY, 0.00) AS display_salary
FROM customers;

Note: Some databases have their own functions, like IFNULL(value, default) in MySQL and ISNULL(value, default) in SQL Server. COALESCE is the ANSI SQL standard and is more portable.

7. NULLs and Aggregate Functions: An Important Distinction

Aggregate functions behave differently with NULLs, which is a common source of bugs:

• COUNT(column_name): Counts the number of rows where column_name is NOT NULL.
• COUNT(*): Counts the total number of rows, regardless of NULL values.
• SUM(), AVG(), MIN(), MAX(): These functions all ignore NULL values in their calculations. For example, AVG(SALARY) will be the sum of known salaries divided by the count of customers with known salaries.

SELECT
    COUNT(*) AS total_customers, -- Result: 7
    COUNT(SALARY) AS customers_with_salary, -- Result: 5
    AVG(SALARY) AS average_of_known_salaries, -- Calculated on 5 people, not 7
    AVG(COALESCE(SALARY, 0)) AS average_with_zero_default -- Calculated on all 7 people
FROM customers;