Command guide

intermediate null-handling MySQL MariaDB SQL Server PostgreSQL SQLite

Compare Values Including NULLs Safely

Match rows where two values are equal, treating two NULLs as equal instead of unknown.

Created Apr 30, 2026 Last updated Apr 30, 2026 0/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Find rows where two nullable name columns match

Ada matches because both nullable columns are NULL. Bob matches because both columns contain Q. Carol and Dan do not match because only one side is NULL. A plain middle_name = preferred_middle_name predicate would return Bob only and silently miss Ada.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE people (
  id INT,
  name VARCHAR(50),
  middle_name VARCHAR(50),
  preferred_middle_name VARCHAR(50)
);

INSERT INTO
  people (id, name, middle_name, preferred_middle_name)
VALUES
  (1, 'Ada', NULL, NULL),
  (2, 'Bob', 'Q', 'Q'),
  (3, 'Carol', NULL, 'R'),
  (4, 'Dan', 'X', NULL);

Validated query

SQL

SELECT
  id,
  name
FROM
  people
WHERE
  middle_name <= > preferred_middle_name
ORDER BY
  id;

Expected result

id	name
1	Ada
2	Bob

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE people (
  id INT,
  name VARCHAR(50),
  middle_name VARCHAR(50),
  preferred_middle_name VARCHAR(50)
);

INSERT INTO
  people (id, name, middle_name, preferred_middle_name)
VALUES
  (1, 'Ada', NULL, NULL),
  (2, 'Bob', 'Q', 'Q'),
  (3, 'Carol', NULL, 'R'),
  (4, 'Dan', 'X', NULL);

Validated query

SQL

SELECT
  id,
  name
FROM
  people
WHERE
  middle_name = preferred_middle_name
  OR (
    middle_name IS NULL
    AND preferred_middle_name IS NULL
  )
ORDER BY
  id;

Expected result

id	name
1	Ada
2	Bob

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE people (
  id INT,
  name VARCHAR(50),
  middle_name VARCHAR(50),
  preferred_middle_name VARCHAR(50)
);

INSERT INTO
  people (id, name, middle_name, preferred_middle_name)
VALUES
  (1, 'Ada', NULL, NULL),
  (2, 'Bob', 'Q', 'Q'),
  (3, 'Carol', NULL, 'R'),
  (4, 'Dan', 'X', NULL);

Validated query

SQL

SELECT
  id,
  name
FROM
  people
WHERE
  middle_name IS NOT DISTINCT
FROM
  preferred_middle_name
ORDER BY
  id;

Expected result

id	name
1	Ada
2	Bob

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE people (
  id INT,
  name TEXT,
  middle_name TEXT,
  preferred_middle_name TEXT
);

INSERT INTO
  people (id, name, middle_name, preferred_middle_name)
VALUES
  (1, 'Ada', NULL, NULL),
  (2, 'Bob', 'Q', 'Q'),
  (3, 'Carol', NULL, 'R'),
  (4, 'Dan', 'X', NULL);

Validated query

SQL

SELECT
  id,
  name
FROM
  people
WHERE
  middle_name IS preferred_middle_name
ORDER BY
  id;

Expected result

id	name
1	Ada
2	Bob

The operator differs by engine, but all examples match both ordinary equal values and pairs where both values are NULL.

Useful when

Where this command helps.

comparing nullable columns during data quality checks
joining or filtering rows where missing values should count as matching

Explanation

What the command is doing.

Ordinary equality does not match two NULL values because NULL = NULL evaluates to unknown, not true. Null-safe equality fixes that when missing values should be considered equivalent. PostgreSQL supports IS NOT DISTINCT FROM; MySQL and MariaDB support the <=> operator; SQL Server uses an explicit (a = b OR (a IS NULL AND b IS NULL)) predicate; SQLite can use IS for null-safe value comparison.

Compare Values Including NULLs Safely

Find rows where two nullable name columns match

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.