Learning Tips | Published Nov 17, 2026 | 8 min read

SQL NULL Values and Three-Valued Logic

Understand SQL NULL comparisons, UNKNOWN results, IS NULL, NOT IN pitfalls, COALESCE and aggregate counts with practical examples.

Truth table diagram showing a comparison with SQL NULL produces UNKNOWN, WHERE excludes UNKNOWN, and IS NULL explicitly tests missing values

SQL NULL represents missing or unknown information, not an ordinary value such as zero or an empty string. Because comparisons involving NULL can be unknown rather than true or false, filters, aggregates, and subqueries may behave differently than expected. This guide shows safe ways to test and work with NULL, using PostgreSQL examples where behavior is engine-specific.

NULL is not zero, blank, or equal to itself

A nullable column can contain a value or no known value. The database cannot infer that a missing salary is zero, or that an unknown date is an empty string. Keep those meanings separate in your data model.

Ordinary comparisons with NULL return unknown rather than true or false. Therefore, this filter does not find missing email addresses:

SELECT id
FROM customers
WHERE email = NULL;  -- does not test for missing values

Use IS NULL or IS NOT NULL to test for missing values:

SELECT id
FROM customers
WHERE email IS NULL;

Two unknown values are not proven equal, so NULL = NULL is also unknown. Some databases provide a null-safe equality operator; PostgreSQL uses IS NOT DISTINCT FROM when a comparison should treat two NULLs as matching. Check your database's syntax before relying on that predicate.

Understand three-valued logic in WHERE

SQL predicates can evaluate to TRUE, FALSE, or UNKNOWN. A WHERE clause keeps rows only when its condition is TRUE; FALSE and UNKNOWN rows are both filtered out. For a nullable discount column, discount > 0 excludes rows whose discount is NULL because the comparison is unknown.

Negating an unknown result does not turn it into true: NOT UNKNOWN is still UNKNOWN. This explains why discount <= 0 does not automatically include rows with no discount value. If the business rule treats missing discount as zero, make that policy explicit with COALESCE(discount, 0) <= 0. If missing means “not yet evaluated,” keep the row separate instead.

Watch for NULL inside NOT IN

A nullable value returned by a NOT IN subquery can make the result unknown for otherwise non-matching values. For example, if blocked_ids contains a NULL, a test like customer_id NOT IN (SELECT customer_id FROM blocked_ids) may return no rows you expect, because comparisons against that NULL cannot prove a non-match.

When the intent is “there is no matching row,” NOT EXISTS usually expresses that more safely:

SELECT c.id
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_ids AS b
    WHERE b.customer_id = c.id
);

For matching non-null identifiers this avoids the NULL-poisoning behavior of NOT IN. Decide separately whether a customer row with a NULL identifier is meaningful; comparisons involving it still need an explicit policy.

COALESCE supplies a value, but changes meaning

COALESCE(a, b, ...) returns the first argument that is not NULL. It is useful for a display fallback or an intentional calculation default:

SELECT COALESCE(display_name, 'Name not provided') AS label
FROM customers;

Do not replace NULL with zero, an empty string, or a label automatically just to make output look tidy. A default can hide incomplete data or change a calculation. Use a replacement only when it matches the meaning of the field and the requirement of the query.

Count rows and count known values differently

In PostgreSQL, COUNT(*) counts input rows, while COUNT(column_name) counts rows where that column is not NULL. That distinction matters in reports:

SELECT
    COUNT(*) AS customer_rows,
    COUNT(phone) AS customers_with_phone
FROM customers;

Most aggregate functions ignore NULL inputs; an aggregate over no non-NULL values may return NULL rather than zero. If a report needs zero, apply COALESCE deliberately to the aggregate result. Verify the specific aggregate's behavior in your database documentation.

Make NULL rules explicit in schema and queries

  • Use NOT NULL when a value is required for valid records.
  • Use IS NULL and IS NOT NULL for missing-value filters.
  • Decide whether unknown data should be included, excluded, or treated as a chosen default in each business rule.
  • Test subqueries containing NULL before using NOT IN; consider NOT EXISTS for an absence check.
  • Distinguish row counts from counts of non-null values.
  • Check database-specific syntax and behavior for null-safe comparisons and aggregates.

NULL handling is less surprising when the query states what missing information means. Before adding a fallback, ask whether the value is truly equivalent to the substitute or whether it should remain visibly unknown.

Further reading

Want a structured Java learning path?

Explore Jaipur Engineers course options to connect Java fundamentals, debugging, backend development and project practice in a guided learning plan.

Explore the Java program   Ask for course guidance