NULL Values

Overview

A NULL value in SQL represents missing or unknown data. It is different from zero or an empty string - it means the value is unknown, not applicable, or has not been assigned.

Understanding how to work with NULL values is crucial for accurate SQL queries, as NULL values behave differently than other values in comparisons and operations.

What is NULL?

NULL is a special marker used in SQL to indicate that a data value does not exist in the database. Important characteristics:

• NULL is not zero (0)
• NULL is not an empty string ('')
• NULL is not a space
• NULL represents "no value" or "unknown value"
• NULL values are excluded from most aggregate functions

IS NULL and IS NOT NULL

You cannot use comparison operators (=, <>, etc.) to test for NULL values. You must use IS NULL or IS NOT NULL.

IS NULL

SELECT * FROM Customers
WHERE email IS NULL;

IS NOT NULL

SELECT * FROM Customers
WHERE email IS NOT NULL;

Why You Can't Use = with NULL

Comparison operators with NULL always return NULL (unknown), not TRUE or FALSE:

-- This will NOT find NULL values!
SELECT * FROM Customers
WHERE email = NULL;  -- Always returns no rows

-- This is correct:
SELECT * FROM Customers
WHERE email IS NULL;

Comparison with NULL:
  NULL = NULL  → NULL (unknown)
  NULL != NULL → NULL (unknown)
  5 = NULL     → NULL (unknown)
  5 != NULL    → NULL (unknown)

Use IS NULL or IS NOT NULL instead!

Examples

Example 1: Find Missing Email Addresses

SELECT name, phone
FROM Customers
WHERE email IS NULL;

Example 2: Find Records with Email

SELECT name, email
FROM Customers
WHERE email IS NOT NULL;

Example 3: Combining NULL Checks

SELECT *
FROM Customers
WHERE email IS NULL
   OR phone IS NULL;

Example 4: NULL with Other Conditions

SELECT *
FROM Orders
WHERE total_amount > 100
  AND shipping_date IS NULL;

NULL in INSERT Statements

You can explicitly insert NULL values:

INSERT INTO Customers (name, email, phone)
VALUES ('John Doe', 'john@example.com', NULL);

Or omit columns that allow NULL:

-- If phone allows NULL, this sets phone to NULL
INSERT INTO Customers (name, email)
VALUES ('John Doe', 'john@example.com');

NULL in UPDATE Statements

You can set columns to NULL:

UPDATE Customers
SET phone = NULL
WHERE customer_id = 101;

NULL and Aggregate Functions

Most aggregate functions ignore NULL values:

-- COUNT(*) counts all rows including NULL
SELECT COUNT(*) AS total_customers FROM Customers;

-- COUNT(column) counts only non-NULL values
SELECT COUNT(email) AS customers_with_email FROM Customers;

-- SUM, AVG, MIN, MAX ignore NULL values
SELECT 
    AVG(price) AS avg_price,  -- Ignores NULL prices
    SUM(quantity) AS total_qty -- Ignores NULL quantities
FROM Products;

NULL in Calculations

Arithmetic operations with NULL result in NULL:

-- Any arithmetic with NULL returns NULL
SELECT 
    price,
    discount,
    price * (1 - discount) AS final_price
FROM Products;

-- If discount is NULL, final_price will be NULL
-- Use COALESCE or NULL functions to handle this (see NULL Functions tutorial)

NULL in String Concatenation

Concatenating strings with NULL typically results in NULL:

-- If first_name or last_name is NULL, result is NULL
SELECT first_name || ' ' || last_name AS full_name
FROM Customers;

-- Solution: Use COALESCE or CONCAT_WS
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM Customers;
-- Or use database-specific NULL handling functions

NULL in WHERE Clauses

Be careful with NULL in WHERE clauses:

-- This excludes rows where email is NULL
SELECT * FROM Customers
WHERE email != 'test@example.com';

-- To include NULL rows, use OR:
SELECT * FROM Customers
WHERE email != 'test@example.com'
   OR email IS NULL;

NULL in JOINs

NULL values don't match in JOIN conditions:

-- NULL values in JOIN columns don't match
SELECT *
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;

-- Rows where customer_id is NULL won't match
-- Use ISNULL, COALESCE, or handle NULL separately if needed

COALESCE Function

Use COALESCE to replace NULL with a default value (covered in detail in NULL Functions):

-- Replace NULL phone with 'N/A'
SELECT 
    name,
    COALESCE(phone, 'N/A') AS phone
FROM Customers;

Best Practices

• Always use IS NULL/IS NOT NULL: Never use = or != with NULL
• Handle NULL in calculations: Use COALESCE or NULL functions
• Consider default values: Use DEFAULT constraints when appropriate
• Document NULL behavior: Make NULL handling clear in your code
• Test NULL scenarios: Test queries with NULL values
• Use NOT NULL when appropriate: Prevent NULLs when they're not valid

Common Mistakes

• Using = with NULL: Always returns no rows
• Forgetting NULL in conditions: NULL rows may be unintentionally excluded
• NULL in calculations: Results in NULL, not zero
• Assuming COUNT(*) = COUNT(column): They differ when NULLs exist

Next Steps

Learn about handling NULL values with NULL Functions like COALESCE and ISNULL, or continue with other SQL operations.