SQL NULL VALUES

What are SQL NULL values and how are they handled?

Represents missing or unknown data in a table column and propagates three-valued logic in expressions.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL NULL VALUES: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, DB2, and all ANSI-compliant systems

SQL NULL VALUES Full Explanation

NULL is not a value but a special marker that signifies missing, undefined, or inapplicable data. Whenever a column contains NULL, mathematical and string operations return NULL, and comparison operators ( =, <, > ) evaluate to UNKNOWN instead of TRUE or FALSE. This behavior introduces three-valued logic (TRUE, FALSE, UNKNOWN) in SQL. NULL can be inserted explicitly or implicitly when a column allows it and no value is provided. To test for NULL you must use the predicates IS NULL or IS NOT NULL; equality operators do not work. Functions like COALESCE, NVL, IFNULL, and NULLIF help convert or handle NULL for safer calculations. Aggregate functions ignore NULL except COUNT(*). Constraints such as PRIMARY KEY and NOT NULL prevent NULL storage. Indexes may treat NULL rows differently across vendors, so query plans can vary. Understanding how NULL interacts with joins, GROUP BY, and ORDER BY is critical to avoid incorrect results.

SQL NULL VALUES Syntax

-- Testing for null
column_name IS NULL
column_name IS NOT NULL

-- Inserting a null explicitly
INSERT INTO table_name(column_a, column_b)
VALUES (123, NULL);

-- Replacing nulls at query time
SELECT COALESCE(column_b, 0) AS column_b_default
FROM table_name;

SQL NULL VALUES Parameters

Example Queries Using SQL NULL VALUES

-- Find users who have never logged in
SELECT id, email
FROM users
WHERE last_login IS NULL;

-- Insert a record with an unknown phone number
INSERT INTO contacts(name, phone)
VALUES ('Ada Lovelace', NULL);

-- Replace null revenue with 0 and calculate totals
SELECT COALESCE(revenue, 0) AS revenue,
       SUM(COALESCE(revenue, 0)) OVER() AS total_revenue
FROM sales;

-- Avoid division by null using NULLIF
SELECT total / NULLIF(count, 0) AS avg_value
FROM metrics;

Expected Output Using SQL NULL VALUES

Queries that use IS NULL or IS NOT NULL return only rows matching those predicates
Inserts place a NULL marker in the specified column
COALESCE replaces NULL at runtime, producing non-null output when the fallback expression is chosen

Use Cases with SQL NULL VALUES

Filter rows missing a required attribute (quality control)
Insert placeholder records when certain fields are unknown
Safely aggregate numeric data while ignoring missing entries
Prevent divide-by-zero errors and replace blanks in reports
Audit data completeness by counting NULL occurrences

Common Mistakes with SQL NULL VALUES

Using = NULL or <> NULL instead of IS NULL / IS NOT NULL (always returns UNKNOWN)
Assuming aggregate functions count NULL rows (COUNT(column) skips them)
Forgetting that expressions with NULL propagate NULL, leading to empty totals
Expecting NOT IN (...) to behave as anti-join when NULLs exist in the list
Believing NULL equals empty string or zero (it does not)

First Introduced In

SQL-92 standard

Frequently Asked Questions

Why does = NULL return no rows?

SQL considers NULL unknown, so comparisons with = or <> resolve to UNKNOWN, which fails filtering. Always use IS NULL or IS NOT NULL.

Do aggregate functions include NULLs?

All aggregates except COUNT(*) ignore NULL. Use COUNT(*) to include every row or COALESCE to convert NULL before aggregating.

Is NULL the same as zero or empty string?

No. Zero and empty strings are actual values. NULL signifies the absence of any value and does not compare equal to anything, including itself.

How do I replace NULLs in query results?

Wrap columns in COALESCE(column, fallback) or vendor-specific functions like IFNULL (MySQL) or NVL (Oracle) to output a substitute when the column is NULL.