SQL DISTINCT and NULL: Understand Unique and Missing Values

A report can go wrong before any complicated calculation appears. Repeated city names can be mistaken for separate places, while a missing value can be mistaken for zero. SQL DISTINCT and NULL help you deal with these two different problems clearly.

Practice setup: These lessons use SQLite and the fictional shop database from the QueryFun SQL practice lab. Open that lab, run its setup once in a fresh practice database, then return here. Do not run the INSERT statements again for each lesson. You can use SQLite Fiddle without installing a database server.

The customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional currency units. Each order belongs to one customer. A customer can have several orders.

List unique values with DISTINCT

The customers table has three rows but only two cities. To list the cities once each, write:

SELECT DISTINCT city
FROM customers
ORDER BY city;

The result contains Mombasa and Nairobi. DISTINCT removes repeated combinations from the query result. It does not delete customers or alter the underlying table. Amina and Chao are still separate customers even though they share a city.

Before using DISTINCT, describe what one output row should represent. If the answer is “one city,” selecting just city makes sense. If the answer is “one customer,” merging customers just because they share a city would lose information.

DISTINCT applies to the whole selected combination

Try this variation:

SELECT DISTINCT city, name
FROM customers
ORDER BY city, name;

You now get three rows. Nairobi with Amina is different from Nairobi with Chao. DISTINCT considers all selected expressions together. It does not independently remove repeated entries from each column.

If a join creates repeated records unexpectedly, do not immediately add DISTINCT. First check whether the join conditions are correct and whether multiple matching orders are supposed to produce multiple rows. Hiding the repetition can hide an error in your question.

Meet NULL using a tiny temporary example

Our shop's city column does not allow missing values, so use a common table expression for a separate demonstration. A CTE is a named result available to the statement that follows it. This example does not create or update a saved table.

WITH contacts(name, email) AS (
  VALUES ('Amina', 'amina@example.com'),
         ('Brian', NULL),
         ('Chao', '')
)
SELECT name, email
FROM contacts
WHERE email IS NULL;

Only Brian is returned. NULL marks a missing or unknown value. Chao has an empty text string, written as two single quotes, which is a present string with no characters. Neither value automatically tells you why the email is absent.

Use IS NULL rather than equals NULL

email = NULL is not the right test for missing email. Comparisons involving NULL normally produce an unknown result, and WHERE keeps only conditions that are true. Use email IS NULL for missing values and email IS NOT NULL for present values.

An unknown amount is also different from zero. Zero says the measured amount is none. NULL says you do not have an amount. Replacing missing amounts with zero without a clear rule can make an average or total look more complete than it is.

Show a fallback with COALESCE

COALESCE returns the first non-NULL argument. This can make a report easier to read:

WITH contacts(name, email) AS (
  VALUES ('Amina', 'amina@example.com'),
         ('Brian', NULL),
         ('Chao', '')
)
SELECT name, COALESCE(email, 'Not supplied') AS email_label
FROM contacts
ORDER BY name;

Brian's label becomes Not supplied. Chao's label stays empty because an empty string is not NULL. When a business rule treats both as absent, COALESCE(NULLIF(email, ''), 'Not supplied') first converts the exact empty string to NULL. Spaces still require a separate cleaning decision.

Try it yourself

Using the same three-row contacts CTE, count all rows and count the non-NULL email values. Predict whether Chao's empty string contributes to the email count. Then list the two distinct cities in the shop database.

WITH contacts(name, email) AS (
  VALUES ('Amina', 'amina@example.com'),
         ('Brian', NULL),
         ('Chao', '')
)
SELECT COUNT(*) AS people,
       COUNT(email) AS non_null_emails
FROM contacts;

Keep learning: Return to the SQL learning path to choose your next lesson.

Further reading: SQLite SELECT documentation.