SQL IS NULL and IS NOT NULL: Find Missing Values

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

NULL is a marker for missing or unknown information, not a particular string or number. The expression amount = NULL does not ask the database to identify missing amounts in the intended way. Ordinary comparison with NULL yields unknown, including NULL = NULL. Since WHERE retains only true conditions, = NULL generally returns no rows. Write amount IS NULL to test the marker directly, and amount IS NOT NULL to require a known value.

Three-valued logic explains many surprising filters. amount <> 20 excludes the missing amount as well as the known twenties: the unknown amount cannot be established as different from twenty. If the report intends to include unknown amounts, write amount <> 20 OR amount IS NULL. Conversely, IS NOT NULL only confirms presence. It says nothing about whether a string is meaningful, a number is positive, or a date is inside an acceptable range. Add the appropriate separate checks.

Most arithmetic with NULL produces NULL. Adding an unknown fee to a known price produces an unknown total unless the application deliberately defines a replacement. Most ordinary aggregate functions skip NULL inputs, while COUNT(*) counts rows regardless. Consequently a report should distinguish total records from records with known amounts. Sale 104 still exists even though its amount is missing. Counting known amounts alone does not reveal how many sales need correction unless you also compare with the row count.

Outer joins can introduce NULL placeholders for an absent matching row. A LEFT JOIN customer with no sale produces a missing sales.id even if the sales table defines that id as a non-NULL primary key. Testing the right-side primary key for NULL can identify unmatched customers. Testing sales.amount instead is unsafe here, because sale 104 is a real match with a missing amount. Choose the column whose NULL status proves the specific fact you want to detect.

IS NULL and IS NOT NULL are portable across all four systems. Empty text generally differs from NULL in these systems. NULL sort position varies by engine, and unique-constraint treatment of NULL has differences too. Use explicit NULL tests rather than applying assumptions from sorting or uniqueness to ordinary comparisons.

Check the engine-specific syntax and version requirements in PostgreSQL comparison and NULL predicates.

Return the customer names with a known city. Sort by customer id, and do not replace the missing city with a display label.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT id,name FROM customers
WHERE city IS NOT NULL ORDER BY id;

id | name
1 | Ana
2 | Ben
3 | Cara

Ana and Ben have Leeds, while Cara has Porto. Dev’s city is missing, so the presence predicate is false for him. The query keeps the saved city values intact and simply filters which customer rows appear.

Compare amount<>20 with amount<>20 OR amount IS NULL. The second version should include sale 104 while the first should not. Then use IS NULL on customer.city to find Dev. If you add a known zero amount in a disposable table, verify it appears under IS NOT NULL; a numeric zero must never become a missing-value match.