How do I work with NULL values in SQL?

By Wayan in Learning SQL 0 Comment

Real data is rarely complete. A customer may not have provided a phone number, an order may not yet have a shipping date, and a book may not yet have been assigned a category. SQL uses a special marker, NULL, to represent this missing or unknown information. Working with NULL correctly is one of the most important skills in SQL, because NULL does not behave like ordinary values in comparisons, arithmetic, or aggregation.

In this tutorial, you will learn what NULL really means, how to test for it, how it interacts with operators and functions, and how to replace it with sensible defaults. You will also see the most common mistakes beginners make with NULL and how to avoid them.

Prerequisites

To follow along, you should be comfortable with:

  • writing a basic SELECT statement;
  • filtering rows with WHERE;
  • running SQL against a local database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).

If you have not created a sample database yet, the setup script below will produce everything you need.

Sample Database

We will use a small online bookstore schema with two tables: authors and books.

CREATE TABLE authors (
    author_id   INTEGER      PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    country     VARCHAR(50)
);

CREATE TABLE books (
    book_id       INTEGER       PRIMARY KEY,
    title         VARCHAR(200)  NOT NULL,
    author_id     INTEGER       NOT NULL,
    category      VARCHAR(50),
    price         DECIMAL(8, 2),
    discount      DECIMAL(4, 2),
    published_on  DATE,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id) REFERENCES authors (author_id)
);

INSERT INTO authors (author_id, author_name, country) VALUES
    (1, 'Jane Austen',              'United Kingdom'),
    (2, 'Haruki Murakami',          'Japan'),
    (3, 'Chimamanda Ngozi Adichie',  NULL),
    (4, 'Gabriel Garcia Marquez',   'Colombia');

INSERT INTO books (book_id, title, author_id, category, price, discount, published_on) VALUES
    (1, 'Pride and Prejudice',           1, 'Classic',    12.50, 1.00, '1813-01-28'),
    (2, 'Emma',                          1, 'Classic',    10.00, NULL, '1815-12-23'),
    (3, 'Norwegian Wood',                2, 'Fiction',    15.75, 2.00, '1987-09-04'),
    (4, 'Kafka on the Shore',            2, 'Fiction',    18.20, NULL, '2002-09-12'),
    (5, 'Half of a Yellow Sun',          3, 'Historical', 16.00, 1.50, '2006-08-11'),
    (6, 'Americanah',                    3, 'Contemporary', NULL, NULL, '2013-05-14'),
    (7, 'One Hundred Years of Solitude', 4, 'Classic',    22.00, 3.00, '1967-05-30'),
    (8, 'Untitled Draft',                2,  NULL,         NULL, NULL,  NULL);

Notice that several columns intentionally contain NULL:

  • authors.country is NULL for one author (unknown country).
  • books.category, books.price, books.discount, and books.published_on are NULL for one or more books.

We will use these rows to demonstrate how NULL behaves.

What Does NULL Actually Mean?

NULL is a marker that represents missing, unknown, or unavailable information. It is not:

  • the number zero;
  • an empty string '';
  • the boolean value false;
  • a “default” value.

Two NULL values are not considered equal to each other in comparisons, because “unknown = unknown” is itself unknown.

Three-Valued Logic

Most languages use two-valued boolean logic: a condition is either true or false. SQL uses three-valued logic: true, false, and unknown. Any comparison involving NULL returns unknown.

Expression Result
1 = 1 true
1 = 2 false
1 = NULL unknown
NULL = NULL unknown
NULL <> 'x' unknown

The WHERE clause keeps a row only when the condition evaluates to true. Rows for which the condition is false or unknown are excluded.

Basic Syntax

To test whether a column contains NULL, use IS NULL or IS NOT NULL:

SELECT column_list
FROM table_name
WHERE column_name IS NULL;

SELECT column_list
FROM table_name
WHERE column_name IS NOT NULL;

Do not use = or <> with NULL. They will not raise an error, but they will never match anything.

Practical Example

Suppose the bookstore manager wants to see every book that is missing a category, so the catalog team can fill in the gaps.

SELECT
    book_id,
    title,
    category
FROM books
WHERE category IS NULL;

Read the query in logical order:

  1. FROM books — start with every row in the books table.
  2. WHERE category IS NULL — keep only rows whose category is missing.
  3. SELECT book_id, title, category — return these three columns.

Expected Result

book_id title category
8 Untitled Draft NULL

Only one row is returned because only Untitled Draft has a missing category.

Additional Examples

Finding Rows Where a Column Is Not NULL

To list every book that already has a category:

SELECT
    book_id,
    title,
    category
FROM books
WHERE category IS NOT NULL
ORDER BY book_id;

NULL and Inequality

A common surprise is that <> does not return rows where the column is NULL:

SELECT
    book_id,
    title,
    category
FROM books
WHERE category <> 'Classic';

This query returns fiction, historical, and contemporary books, but not Untitled Draft, because NULL <> 'Classic' is unknown. To include rows with a missing category, add an explicit NULL check:

SELECT
    book_id,
    title,
    category
FROM books
WHERE category <> 'Classic'
   OR category IS NULL;

NULL in Arithmetic

Any arithmetic expression involving NULL produces NULL:

SELECT
    book_id,
    title,
    price,
    discount,
    price - discount AS final_price
FROM books;

For any row where either price or discount is NULL, final_price is also NULL. Zero and NULL are not interchangeable.

Replacing NULL with COALESCE

COALESCE returns the first non-NULL argument. It is the portable, ANSI-standard way to substitute a default value for a missing one.

SELECT
    book_id,
    title,
    price,
    discount,
    price - COALESCE(discount, 0) AS final_price
FROM books;

Now the discount is treated as 0 when it is missing, and final_price is computed correctly for every row that has a price.

You can pass more than two arguments; COALESCE returns the first one that is not NULL:

SELECT
    author_id,
    author_name,
    COALESCE(country, 'Unknown') AS country
FROM authors;

NULLIF: The Inverse of COALESCE

NULLIF(a, b) returns NULL when a equals b, and returns a otherwise. It is useful for avoiding division by zero:

SELECT
    book_id,
    title,
    price / NULLIF(discount, 0) AS price_to_discount_ratio
FROM books;

If discount is 0, NULLIF converts it to NULL, and the division produces NULL instead of raising an error.

NULL and Aggregate Functions

Most aggregate functions ignore NULL values:

SELECT
    COUNT(*)          AS total_rows,
    COUNT(price)      AS rows_with_price,
    AVG(price)        AS average_price,
    SUM(discount)     AS total_discount
FROM books;
  • COUNT(*) counts every row, including rows where all columns are NULL.
  • COUNT(price) counts only rows where price IS NOT NULL.
  • AVG(price) is the average of the non-NULL prices; it is not affected by rows where price is NULL.
  • SUM(discount) adds only non-NULL discounts. If every value is NULL, SUM returns NULL, not 0.

This distinction matters when you report metrics: AVG over a partially-null column is the average of the values that exist, not the average of “value or zero”.

NULL and ORDER BY

Databases differ in where NULL values appear when you sort:

  • PostgreSQL and Oracle Database place NULL last in ascending order by default.
  • MySQL, MariaDB, SQL Server, and SQLite place NULL first in ascending order by default.

To control the placement portably where supported, use NULLS FIRST or NULLS LAST (PostgreSQL, Oracle Database, and SQLite 3.30+):

SELECT
    book_id,
    title,
    published_on
FROM books
ORDER BY published_on ASC NULLS LAST;

MySQL, MariaDB, and SQL Server do not support NULLS LAST directly; you can emulate it with an expression:

SELECT
    book_id,
    title,
    published_on
FROM books
ORDER BY
    CASE WHEN published_on IS NULL THEN 1 ELSE 0 END,
    published_on ASC;

NULL and DISTINCT / GROUP BY

DISTINCT and GROUP BY treat all NULL values as belonging to a single group, even though NULL = NULL is not true in a comparison:

SELECT DISTINCT category
FROM books;

This query returns each category once, plus a single row for NULL.

NULL and IN

Be careful when a subquery used with IN can return NULL:

SELECT title
FROM books
WHERE author_id NOT IN (SELECT author_id FROM authors WHERE country IS NULL);

If the subquery returns any NULL, the entire NOT IN condition becomes unknown for every row, and no rows are returned. Prefer NOT EXISTS when the subquery may produce NULL:

SELECT b.title
FROM books AS b
WHERE NOT EXISTS (
    SELECT 1
    FROM authors AS a
    WHERE a.author_id = b.author_id
      AND a.country IS NULL
);

Using NULL from Application Code

When you fetch nullable columns in JDBC, primitive types cannot represent NULL. Use the wrapper getters, or explicitly test the result with ResultSet.wasNull() after reading the value:

String sql = """
        SELECT book_id, title, price, discount
        FROM books
        WHERE book_id = ?
        """;

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setLong(1, bookId);

    try (ResultSet resultSet = statement.executeQuery()) {
        if (resultSet.next()) {
            BigDecimal price = resultSet.getBigDecimal("price");
            BigDecimal discount = resultSet.getBigDecimal("discount");

            // getBigDecimal returns null for SQL NULL,
            // so an explicit wasNull() check is optional here.
            if (discount == null) {
                discount = BigDecimal.ZERO;
            }

            BigDecimal finalPrice = price == null
                    ? null
                    : price.subtract(discount);
        }
    }
}

When inserting a NULL, use PreparedStatement.setNull(index, sqlType) rather than passing an empty string or zero.

Common Mistakes

Comparing a Value to NULL with =

Incorrect:

SELECT *
FROM books
WHERE category = NULL;

Correct:

SELECT *
FROM books
WHERE category IS NULL;

The first query returns zero rows because category = NULL evaluates to unknown, never true.

Assuming NULL Equals NULL

The following query does not return every row:

SELECT *
FROM books
WHERE category = category;

For rows where category IS NULL, the expression NULL = NULL is unknown, so those rows are excluded. If you truly want every row, remove the WHERE clause.

Treating NULL as Zero in Arithmetic

Incorrect assumption:

SELECT
    book_id,
    price - discount AS final_price
FROM books;

For rows where discount IS NULL, final_price will also be NULL — not price. Use COALESCE:

SELECT
    book_id,
    price - COALESCE(discount, 0) AS final_price
FROM books;

Using NOT IN with a Nullable Subquery

If a subquery inside NOT IN can return NULL, the outer query silently returns no rows. Prefer NOT EXISTS when nullability is possible.

Expecting Aggregates to Fail on NULL

Aggregates skip NULL instead of raising an error. If AVG(price) looks “too high” or SUM(discount) looks “too low”, check whether the source column contains NULL values and whether that is the behavior you want.

Database Compatibility

NULL, IS NULL, IS NOT NULL, COALESCE, and NULLIF are part of standard SQL and work in:

  • PostgreSQL;
  • MySQL;
  • MariaDB;
  • SQL Server;
  • Oracle Database;
  • SQLite.

Notable differences:

  • NULLS FIRST / NULLS LAST are supported by PostgreSQL, Oracle Database, and SQLite 3.30 and later. MySQL, MariaDB, and SQL Server require a CASE expression in ORDER BY to emulate the behavior.
  • Default NULL ordering differs between databases, as noted earlier.
  • ISNULL exists in both SQL Server and MySQL, but with different meanings. In SQL Server, ISNULL(a, b) behaves like COALESCE(a, b). In MySQL, ISNULL(x) is a single-argument function that returns 1 when x IS NULL. Prefer the portable COALESCE and IS NULL forms.
  • NVL is Oracle Database’s non-standard equivalent of COALESCE with two arguments. Prefer COALESCE for portability.
  • Empty string vs. NULL. Oracle Database historically treats an empty string '' as NULL for VARCHAR2 columns. Other databases treat '' and NULL as distinct values.

Consult the documentation for your database and version when in doubt.

Best Practices

  • Model missing data deliberately. Decide up front whether a column should allow NULL. If a value is always required, declare the column NOT NULL.
  • Use IS NULL / IS NOT NULL for tests. Never write = NULL or <> NULL.
  • Use COALESCE for portable defaults. It is standard SQL and easier to read than vendor-specific functions such as NVL or ISNULL.
  • Guard against NULL in arithmetic. Wrap potentially null operands with COALESCE(column, 0) when zero is the correct substitute.
  • Beware of NOT IN with nullable subqueries. Prefer NOT EXISTS.
  • Document nullability at the application boundary. In Java, use wrapper types or Optional for columns that can be NULL, and use PreparedStatement.setNull when inserting.
  • Check aggregate semantics. Know that COUNT(*), COUNT(column), and SUM(column) treat NULL differently.
  • Be explicit about ordering. If sort order matters, specify NULLS FIRST or NULLS LAST where supported, or emulate it with CASE.

Conclusion

You learned that NULL represents missing or unknown information in SQL, that comparisons involving NULL use three-valued logic, and that you must use IS NULL and IS NOT NULL instead of = and <> to test for it. You also saw how COALESCE and NULLIF help you substitute defaults and guard against division by zero, and how NULL affects arithmetic, aggregation, sorting, and NOT IN. Treating NULL carefully will save you from a large class of subtle, silent bugs.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.