Why = NULL Returns Nothing

You wrote WHERE city = NULL to find customers with no city, and the query returned nothing, with no error. That is not a bug. In SQL, NULL means "unknown", and nothing is ever equal to unknown. This page shows why and what to write instead.

Step 1: reproduce it

CREATE TABLE customers (name text, city text);
INSERT INTO customers VALUES ('Ana Reyes', 'Manila'), ('Dan Lim', NULL), ('Gina Ortiz', NULL);

CREATE TABLE
INSERT 0 3

SELECT name FROM customers WHERE city = NULL;

 name
------
(0 rows)

Two customers have no city, and none came back.

Step 2: see what the comparison returns

Ask PostgreSQL directly. First, tell psql to show NULL as text so you can see it.

\pset null '(null)'
SELECT NULL = NULL AS equal, NULL IS NULL AS is_null;

Null display is "(null)".
 equal  | is_null
--------+---------
 (null) | t
(1 row)

NULL = NULL is not true or false. It is unknown, shown here as (null). WHERE keeps only rows where the condition is true, so unknown rows are dropped. <> NULL is just as bad.

Step 3: pick the fix

To find the missing values, use IS NULL.

SELECT name FROM customers WHERE city IS NULL;

    name
------------
 Dan Lim
 Gina Ortiz
(2 rows)

To find everyone who has a value, use IS NOT NULL.

SELECT name FROM customers WHERE city IS NOT NULL;

   name
-----------
 Ana Reyes
(1 row)

To treat missing as an ordinary value in a comparison, use IS DISTINCT FROM. Compare with <>, which also drops NULL rows, and watch the difference.

SELECT name FROM customers WHERE city <> 'Manila';
SELECT name FROM customers WHERE city IS DISTINCT FROM 'Manila' ORDER BY name;

 name
------
(0 rows)

    name
------------
 Dan Lim
 Gina Ortiz
(2 rows)

The first query returns nobody, because both remaining customers have NULL cities. The second returns the two customers whose city is not Manila, including the unknown ones. IS DISTINCT FROM is standard SQL, but not every database supports it. The portable version is city <> 'Manila' OR city IS NULL.

To show a default in a result, use COALESCE. It does not change the stored data.

SELECT name, COALESCE(city, 'unknown') AS city FROM customers ORDER BY name;

    name    |  city
------------+---------
 Ana Reyes  | Manila
 Dan Lim    | unknown
 Gina Ortiz | unknown
(3 rows)

Related course: SQL for Beginners.