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.

How do I test null and non-null values in JUnit?

By Wayan in JUnit 0 Comment

To test null and non-null values in JUnit, use these assertions:

  • assertNull(value) — passes if the value is null
  • assertNotNull(value) — passes if the value is not null

In JUnit 5, these methods are available from org.junit.jupiter.api.Assertions.

Basic Example

import org.junit.jupiter.api.Test;

import static org.junit.jupiter.api.Assertions.assertNull;
import static org.junit.jupiter.api.Assertions.assertNotNull;

class NullCheckTest {

    @Test
    void valueShouldBeNull() {
        String name = null;

        assertNull(name);
    }

    @Test
    void valueShouldNotBeNull() {
        String name = "John";

        assertNotNull(name);
    }
}

Using Assertion Messages

You can add a message to make test failures easier to understand.

import org.junit.jupiter.api.Test;

import static org.junit.jupiter.api.Assertions.assertNull;
import static org.junit.jupiter.api.Assertions.assertNotNull;

class NullCheckTest {

    @Test
    void valueShouldBeNull() {
        String result = null;

        assertNull(result, "The result should be null");
    }

    @Test
    void valueShouldNotBeNull() {
        String result = "Hello JUnit";

        assertNotNull(result, "The result should not be null");
    }
}

Testing a Method That May Return Null

Suppose you have a method that returns a username:

class UserService {

    String findUsernameById(int id) {
        if (id == 1) {
            return "Alice";
        }
        return null;
    }
}

You can test both cases like this:

import org.junit.jupiter.api.Test;

import static org.junit.jupiter.api.Assertions.assertNull;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertEquals;

class UserServiceTest {

    private final UserService userService = new UserService();

    @Test
    void findUsernameByIdReturnsNameWhenUserExists() {
        String username = userService.findUsernameById(1);

        assertNotNull(username);
        assertEquals("Alice", username);
    }

    @Test
    void findUsernameByIdReturnsNullWhenUserDoesNotExist() {
        String username = userService.findUsernameById(99);

        assertNull(username);
    }
}

Testing Null Arguments

If your code should reject null values, use assertThrows() to check that an exception is thrown.

import org.junit.jupiter.api.Test;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertThrows;

class PersonTest {

    @Test
    void constructorThrowsExceptionWhenNameIsNull() {
        NullPointerException exception = assertThrows(
                NullPointerException.class,
                () -> new Person(null)
        );

        assertEquals("Name cannot be null", exception.getMessage());
    }
}

class Person {

    private final String name;

    Person(String name) {
        if (name == null) {
            throw new NullPointerException("Name cannot be null");
        }

        this.name = name;
    }
}

Summary

Use:

assertNull(value);
assertNotNull(value);

For null-related exceptions, use:

assertThrows(NullPointerException.class, () -> {
    // code that should throw NullPointerException
});

These assertions are the standard way to test null and non-null values in JUnit 5.

How do I use the safe call operator `?.` in Kotlin?

By Wayan in Kotlin 0 Comment

In Kotlin, the safe call operator ?. lets you access a property or call a function only if the value is not null.

If the value is null, the expression simply returns null instead of throwing a NullPointerException.

val name: String? = null

val length = name?.length

println(length) // null

Here, name is nullable because its type is String?. Since name is null, name?.length does not try to access .length; it returns null.

Basic syntax

nullableValue?.property
nullableValue?.function()

Example:

val user: User? = getUser()

val email = user?.email

If user is not null, email gets user.email.

If user is null, email becomes null.

Chaining safe calls

You can chain multiple safe calls together:

val city = user?.address?.city

This means:

  • if user is null, return null
  • otherwise check address
  • if address is null, return null
  • otherwise return city

Using ?. with a default value

Often, you combine ?. with the Elvis operator ?::

val length = name?.length ?: 0

This means:

  • if name is not null, use name.length
  • if name is null, use 0

Using ?.let

Use ?.let when you want to run code only when a value is not null:

val name: String? = "Kotlin"

name?.let {
    println("Name is $it")
    println("Length is ${it.length}")
}

The block runs only if name is not null.

Safe call on assignment

Safe calls can also be used on the left side of an assignment:

person?.address?.city = "Paris"

If person or address is null, the assignment is skipped.

Summary

val result = nullableValue?.someProperty

Use ?. when:

  • a value might be null
  • you want to avoid NullPointerException
  • returning null is acceptable when the receiver is null

Common pattern:

val result = nullableValue?.someProperty ?: defaultValue

How do I use Optional to refactor nested null checks?

By Wayan in Util Package 0 Comment

Using Optional in Java is a great way to refactor nested null checks into more readable and maintainable code. Below, I’ll explain how you can use Optional to replace deeply nested null checks step by step with examples.

Example of Nested Null Checks

Consider this code with deeply nested null checks:

String streetName = null;

if (user != null) {
    Address address = user.getAddress();
    if (address != null) {
        Street street = address.getStreet();
        if (street != null) {
            streetName = street.getName();
        }
    }
}

Here, multiple if statements are used to avoid NullPointerException. This can make the code verbose and harder to read.

Refactoring with Optional

You can refactor this using Optional to create a chain of operations that handle nulls more elegantly:

String streetName = Optional.ofNullable(user)
    .map(User::getAddress)  // get Address if user is not null
    .map(Address::getStreet) // get Street if Address is not null
    .map(Street::getName)    // get Name if Street is not null
    .orElse(null);           // return null if any step is null

This way, you eliminate the explicit null checks and reduce the overall complexity of the code.

Explanation of the Refactored Code

  • Optional.ofNullable(user)
    Wraps the user object in an Optional. If user is null, it creates an empty Optional to safely handle further processing.

  • .map()

    • Applies the method if the value is present; otherwise, it returns an empty Optional.
    • For example, map(User::getAddress) calls getAddress only if user is not null.
  • .orElse(null)
    Provides a fallback value in case the chain results in an empty Optional, i.e., if any intermediate object was null.

Variations

1. Provide a Default Value Instead of Null

You can replace null with any default value like this:

String streetName = Optional.ofNullable(user)
    .map(User::getAddress)
    .map(Address::getStreet)
    .map(Street::getName)
    .orElse("Default Street");

If user or any intermediate object is null, "Default Street" will be assigned to streetName.

2. Throw Exception if Value is Missing

String streetName = Optional.ofNullable(user)
    .map(User::getAddress)
    .map(Address::getStreet)
    .map(Street::getName)
    .orElseThrow(() -> new IllegalArgumentException("Street name not found!"));

This method will throw an exception if any object in the chain is null.

3. Perform an Action if Value Exists

You can perform a side effect or some action if the resulting value isn’t null:

Optional.ofNullable(user)
    .map(User::getAddress)
    .map(Address::getStreet)
    .map(Street::getName)
    .ifPresent(name -> System.out.println("Street: " + name));

This approach avoids the need to explicitly check equality with null.

Benefits of Using Optional for Null Checks

  1. Improved Readability:
    Eliminates nested if statements and reduces verbosity.

  2. Clear Intent:
    It’s evident that the code is handling potentially null objects.

  3. Avoid NullPointerException:
    Safeguards code without explicit null checks by the chaining mechanism.

  4. Encourages Functional Style:
    Methods like map, orElse, and ifPresent allow for a clean, declarative style of programming.

When Not to Use Optional

While Optional is a powerful tool, it’s not meant to replace all null checks. Avoid using Optional:

  1. For fields in entities/classes (use only for method return values).
  2. When null checks aren’t deeply nested (a simple if might be more appropriate).

With Optional, you get safer and cleaner null handling in your Java code, making it easier to maintain and debug!

How do I avoid null checks using Optional?

By Wayan in Util Package 0 Comment

Using the Optional class in Java is a great way to handle the potential absence of a value and avoid explicit null checks in your code. Here’s a detailed explanation of how you can use Optional effectively to avoid null checks:

1. Use Optional Instead of null

Instead of returning null from a method, return an Optional instance. There are three main factory methods available:

  • Optional.of(value): Creates an Optional with the provided non-null value. Throws a NullPointerException if the value is null.
  • Optional.ofNullable(value): Creates an Optional with the given value, which can be null.
  • Optional.empty(): Returns an empty Optional.

Example:

package org.kodejava.util;

import java.util.Optional;

public class Example {
    public Optional<String> getName(String input) {
        return Optional.ofNullable(input);
    }
}

2. Access the Value Safely

To avoid null checks, you can access the value in an Optional using several methods:

2.1 isPresent() and get() (Not Preferred)

Before Java 11, developers often used isPresent to check if a value exists and then call get(). While functional, it’s not ideal because it still requires an “if-present” style:

String name = getName().isPresent() ? getName().get() : "default";

2.2 ifPresent()

Instead of checking isPresent, use the ifPresent method to perform an operation if the value exists:

Optional<String> name = getName("John");
name.ifPresent(n -> System.out.println("Name is: " + n));

2.3 orElse()

Provide a default value in case the Optional is empty:

String name = getName("John").orElse("default");
System.out.println(name);

2.4 orElseGet()

If providing a default value involves computation, use orElseGet. This will execute the supplier only when the Optional is empty:

String name = getName(null).orElseGet(() -> "computedDefault");

2.5 orElseThrow()

If the absence of a value is an error, throw an exception:

String name = getName(null).orElseThrow(() -> new IllegalArgumentException("Name is missing!"));

3. Transform the Value with map and flatMap

Instead of performing a null check and then transforming the value, use the map or flatMap methods to apply a function to the value inside the Optional:

Map Example:

Optional<String> name = getName("John");
Optional<Integer> nameLength = name.map(String::length);
nameLength.ifPresent(System.out::println); // Prints: 4

FlatMap Example:

Use flatMap when the function you’re applying returns another Optional:

Optional<String> email = getEmail();
Optional<String> domain = email.flatMap(e -> Optional.ofNullable(e.split("@")[1]));
domain.ifPresent(System.out::println);

4. Filter Optional Values

You can filter values inside an Optional using a predicate:

Optional<String> name = getName("John");
Optional<String> filteredName = name.filter(n -> n.startsWith("J"));
filteredName.ifPresent(System.out::println); // Prints: John

5. Chaining and Functional Style

Optional works well with lambda expressions and method references, encouraging a concise and functional programming style:

String name = getName(null)
                  .filter(n -> n.length() > 3)
                  .map(String::toUpperCase)
                  .orElse("DEFAULT");

System.out.println(name);

6. Avoid Misuse of Optional

  • Don’t use Optional as a method parameter. It should only be used for return types.
  • Don’t use Optional.get() without first checking isPresent(). This defeats the purpose of avoiding null.
  • Prefer specific methods like orElse or orElseThrow over manual isPresent() checks for better readability and safety.

Example: Practical Use in a Service

package org.kodejava.util;

import java.util.Map;
import java.util.Optional;

public class UserService {

    private final Map<Long, String> users =
            Map.of(1L, "Alice", 2L, "Bob", 3L, null);

    public Optional<String> getUserById(Long id) {
        return Optional.ofNullable(users.get(id));
    }

    public void displayUser(Long id) {
        getUserById(id)
                .map(String::toUpperCase)
                .ifPresentOrElse(
                        user -> System.out.println("User: " + user),
                        () -> System.out.println("User not found")
                );
    }
}

Output Example:

UserService service = new UserService();
service.displayUser(1L); // Prints: "User: ALICE"
service.displayUser(3L); // Prints: "User not found"

By using Optional this way, you can avoid null checks and make your code cleaner, safer, and more readable!