SELECT DISTINCT in SQL: unique rows, NULL and GROUP BY

When I need a list of different values, I first decide what one output row should represent. A unique name, a name-and-city pair and a complete customer record are different sets. SELECT DISTINCT compares all selected expressions together. It does not automatically choose one customer per name or solve the problem of identifying people.

These examples were executed in SQLite 3.53.4 through Node 24. They form a self-contained exercise with synthetic data. The basic rule of comparing complete output rows is also described in the PostgreSQL documentation. Check type and collation details for your engine; executing SQLite does not test every SQL dialect.

Prepare the data

Run this block in a new, empty database. Repeated names and cities are deliberate. I do not assume two people with the same name are one customer.

-- example: fixture
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT
);
INSERT INTO customers VALUES
    (1, 'Ada', 'Warsaw'),
    (2, 'Ben', 'Krakow'),
    (3, 'Ada', 'Warsaw'),
    (4, 'Ada', 'Krakow'),
    (5, 'Celina', NULL),
    (6, 'Celina', NULL),
    (7, 'ada', 'Warsaw');

The table contains seven records. The id column is unique, while other values may repeat. Simple identifiers and English city names let both language versions use exactly the same executable code.

DISTINCT on one column and several columns

First, I select different names. ORDER BY explicitly controls their order; DISTINCT alone does not guarantee sorting.

-- example: names
SELECT DISTINCT name
FROM customers
ORDER BY name;

The default BINARY collation in this table distinguishes Ada and ada. Now I add the city:

-- example: pairs
SELECT DISTINCT name, city
FROM customers
ORDER BY name, city;

Two source rows containing Ada/Warsaw produce one combination. Two Celina/NULL rows also produce one. Ada/Krakow remains separate because its city differs. The result contains five rows. Add the identifier to the SELECT list and all seven records remain distinct. DISTINCT therefore does not remove duplicates independently from each column.

NULL and COUNT(DISTINCT) answer different questions

For duplicate elimination, missing values in otherwise matching combinations are treated as equivalent. This does not change the behaviour of an ordinary NULL = NULL condition. The SQLite SELECT documentation explains the duplicate-removal rules.

-- example: cities
SELECT DISTINCT city
FROM customers
ORDER BY city;

There are three entries, one representing a missing city. The aggregate behaves differently:

-- example: city-counts
SELECT COUNT(DISTINCT city) AS known_cities,
       (SELECT COUNT(*) FROM
           (SELECT DISTINCT city FROM customers)
       ) AS city_options
FROM customers;

COUNT(DISTINCT city) ignores NULL and counts two known cities. The subquery counts every row in the DISTINCT result, including the missing category. This follows the SQLite COUNT rules. A report’s measurement name should make clear whether missing data is a separate category or excluded from the count.

DISTINCT can be combined with GROUP BY

There is no general prohibition against using the two constructs together. I can count different names within each city:

-- example: grouped
SELECT city, COUNT(DISTINCT name) AS unique_names
FROM customers
GROUP BY city
ORDER BY city;

Warsaw contains two spellings: Ada and ada. This measures different values, rather than uniquely identified people. That limitation remains important even when the query is technically correct.

SELECT DISTINCT can also operate on the result of grouping:

-- example: group-sizes
SELECT DISTINCT COUNT(*) AS group_size
FROM customers
GROUP BY city
ORDER BY group_size;

The city groups contain two, two and three records. Because I select only the size, DISTINCT removes the repeated two. Including the city would already distinguish the group rows. Before combining these constructs, decide whether removing duplicates at this stage serves the question you are asking.

Letter case and comparison rules

Collation determines how text is compared. In SQLite, I can explicitly compare two settings:

-- example: collations
SELECT COUNT(DISTINCT name COLLATE BINARY) AS binary_names,
       COUNT(DISTINCT name COLLATE NOCASE) AS nocase_names
FROM customers;

NOCASE treats Ada and ada as equivalent here. SQLite’s built-in NOCASE handles ASCII letter case; this is not evidence of comprehensive case-insensitive comparison for every Unicode character. See the type and collation documentation. Likewise, do not remove spaces or accents without an agreed rule for the data.

Avoid hiding a faulty join with DISTINCT

When a join increases the row count, I first inspect the relationship. One customer can have several orders, so a repeated customer name may be correct. If I need a list of customers with orders, DISTINCT can answer that question. If I need order totals, suppressing rows may instead remove valid observations.

I also avoid assuming DISTINCT is always slower than GROUP BY. I examine the query’s meaning, execution plan and measurements on the relevant data. Indexes, result size and the optimiser’s choices matter. Replacing one keyword with another is not evidence of improved performance.

Exercise: define what you are counting

Using the same data, list known cities without NULL. Krakow and Warsaw should remain. Then select different combinations of id, name and city: you should receive seven rows. Check the table’s record count after all the queries too. It remains seven because SELECT does not delete source data.

To limit the size of an ordered list, continue with LIMIT and pagination. To investigate why joining creates several pairs, see SELF JOIN. In either case, start with a sentence describing one expected output row.