Working with NULL Values

Introduction

NULL is one of the most misunderstood concepts in SQL. It's not zero, not an empty string, not false—it represents the absence of a value or unknown data.

Understanding NULL is critical because it behaves differently than regular values and can cause unexpected results if not handled properly.

What is NULL?

NULL means:

• Missing data: Email address not provided
• Unknown data: Birth date not recorded
• Not applicable: Middle name (not everyone has one)

NULL is NOT:

• Zero (0)
• Empty string ('')
• FALSE

SELECT
    NULL IS NOT 0,
    NULL IS NOT '',
    NULL IS NOT FALSE;

All return TRUE—NULL is distinct from these values.

Testing for NULL

IS NULL and IS NOT NULL

-- ❌ WRONG - doesn't work
SELECT * FROM users WHERE email = NULL;

-- ✅ CORRECT
SELECT * FROM users WHERE email IS NULL;

-- Find non-NULL emails
SELECT * FROM users WHERE email IS NOT NULL;

Why = NULL doesn't work:

NULL represents "unknown". Comparing anything to "unknown" yields "unknown", not TRUE or FALSE.

SELECT NULL = NULL;  -- Returns NULL (not TRUE!)
SELECT NULL <> NULL; -- Returns NULL (not TRUE!)
SELECT NULL IS NULL; -- Returns TRUE ✅

NULL in Comparisons

-- All these return NULL (not TRUE or FALSE)
SELECT NULL = 5;
SELECT NULL <> 5;
SELECT NULL > 5;
SELECT NULL < 5;
SELECT 5 + NULL;  -- NULL
SELECT 'Hello' || NULL;  -- NULL

Key rule: Any operation involving NULL returns NULL (with few exceptions).

NULL in Logical Operations

AND

SELECT TRUE AND NULL;   -- NULL
SELECT FALSE AND NULL;  -- FALSE (short-circuit)
SELECT NULL AND NULL;   -- NULL

OR

SELECT TRUE OR NULL;    -- TRUE (short-circuit)
SELECT FALSE OR NULL;   -- NULL
SELECT NULL OR NULL;    -- NULL

NOT

SELECT NOT NULL;  -- NULL

NULL in WHERE Clause

-- Users without email
SELECT * FROM users WHERE email IS NULL;

-- Users with email
SELECT * FROM users WHERE email IS NOT NULL;

-- Complex conditions
SELECT * FROM users
WHERE age > 30 OR age IS NULL;  -- Include unknowns

COALESCE - Providing Defaults

COALESCE returns the first non-NULL value:

SELECT COALESCE(NULL, NULL, 'default', 'other');
-- Result: 'default'

-- Provide default for missing emails
SELECT
    name,
    COALESCE(email, 'No email provided') AS email
FROM users;

Practical Example

-- Calculate total price with optional discount
SELECT
    product_name,
    price,
    COALESCE(discount, 0) AS discount,
    price - COALESCE(discount, 0) AS final_price
FROM products;

NULLIF - Creating NULLs

NULLIF(value1, value2) returns NULL if values are equal:

SELECT NULLIF(5, 5);  -- NULL
SELECT NULLIF(5, 3);  -- 5

-- Avoid division by zero
SELECT
    revenue / NULLIF(orders, 0) AS avg_order_value
FROM sales;

NULL in Aggregates

COUNT Behavior

CREATE TABLE test (val INTEGER);
INSERT INTO test VALUES (1), (2), (NULL), (3), (NULL);

SELECT COUNT(*) FROM test;      -- 5 (counts all rows)
SELECT COUNT(val) FROM test;    -- 3 (counts non-NULL values)

Other Aggregates Skip NULLs

SELECT
    SUM(val) AS total,    -- 6 (NULLs ignored)
    AVG(val) AS average,  -- 2 (6/3, not 6/5)
    MAX(val) AS maximum,  -- 3
    MIN(val) AS minimum   -- 1
FROM test;

NULL in Sorting

Default NULL ordering (PostgreSQL):

• ASC: NULLs last
• DESC: NULLs first

SELECT * FROM users ORDER BY age;
-- 22, 28, 34, NULL, NULL

SELECT * FROM users ORDER BY age DESC;
-- NULL, NULL, 34, 28, 22

Control NULL Placement

-- NULLs first
SELECT * FROM users ORDER BY age NULLS FIRST;

-- NULLs last
SELECT * FROM users ORDER BY age DESC NULLS LAST;

NULL in DISTINCT

NULL values are considered equal for DISTINCT:

SELECT DISTINCT age FROM users;
-- 22, 28, 34, NULL (only one NULL, even if multiple exist)

NULL in JOIN

NULL values don't match in JOINs:

-- Customers table: id, name, region_id
-- Regions table: id, name

-- Customers with NULL region_id won't match any region
SELECT c.name, r.name AS region
FROM customers c
LEFT JOIN regions r ON c.region_id = r.id;

NOT NULL Constraint

Prevent NULL values in columns:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,  -- Required
    phone VARCHAR(20)              -- Optional
);

-- ❌ Error
INSERT INTO users (phone) VALUES ('555-1234');

-- ✅ Success
INSERT INTO users (email, phone)
VALUES ('user@example.com', '555-1234');

Default Values Instead of NULL

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    stock INTEGER DEFAULT 0,      -- Default instead of NULL
    active BOOLEAN DEFAULT TRUE
);

INSERT INTO products (name) VALUES ('Widget');

SELECT * FROM products;
-- stock: 0 (not NULL)
-- active: TRUE (not NULL)

Three-Valued Logic

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN (NULL).

SELECT * FROM users WHERE age > 30;
-- Rows where age > 30 is TRUE
-- Rows where age > 30 is FALSE or NULL are excluded

This can cause surprising behavior:

SELECT * FROM users WHERE age <= 30 OR age > 30;
-- Doesn't return rows where age IS NULL!

To include NULLs:

SELECT * FROM users
WHERE age <= 30 OR age > 30 OR age IS NULL;

Common Patterns

Pattern 1: Optional Fields

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    middle_name VARCHAR(100),  -- Optional
    bio TEXT                    -- Optional
);

Pattern 2: Soft Deletes

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    deleted_at TIMESTAMPTZ  -- NULL = active
);

-- Active posts
SELECT * FROM posts WHERE deleted_at IS NULL;

Pattern 3: Handle Missing Data

SELECT
    name,
    COALESCE(phone, 'No phone') AS phone,
    COALESCE(email, 'No email') AS email
FROM contacts;

Common Mistakes

Mistake 1: Using = NULL

-- ❌ Wrong
WHERE email = NULL

-- ✅ Correct
WHERE email IS NULL

Mistake 2: Forgetting NULLs in Negation

-- Want all users NOT named 'John'
-- ❌ Excludes NULLs too!
SELECT * FROM users WHERE name <> 'John';

-- ✅ Include NULLs explicitly
SELECT * FROM users
WHERE name <> 'John' OR name IS NULL;

Mistake 3: NULL in Calculations

-- ❌ Results in NULL if discount is NULL
SELECT price - discount AS final_price;

-- ✅ Handle NULL
SELECT price - COALESCE(discount, 0) AS final_price;

Practice Exercises

Exercise 1

Find all users without an email address.

SELECT * FROM users WHERE email IS NULL;

Exercise 2

Show all users, displaying "Unknown" for NULL ages.

SELECT
    name,
    COALESCE(age::TEXT, 'Unknown') AS age
FROM users;

Exercise 3

Count users with and without email addresses.

SELECT
    COUNT(CASE WHEN email IS NOT NULL THEN 1 END) AS with_email,
    COUNT(CASE WHEN email IS NULL THEN 1 END) AS without_email
FROM users;

Key Takeaways

• ✅ NULL represents missing/unknown data
• ✅ IS NULL / IS NOT NULL test for NULL (not = or <>)
• ✅ NULL in operations returns NULL
• ✅ COALESCE provides default values
• ✅ Aggregates ignore NULL (except COUNT(*))
• ✅ NOT NULL constraint prevents NULL values
• ✅ Consider NULLs in WHERE, JOIN, and calculations

Next Steps

NULL handling is essential for robust SQL. In the next lesson, we'll cover Best Practices for Data Manipulation—patterns and techniques for safe, efficient database operations.