SQL SELECT DISTINCT: unique values, combinations and traps

You have a customer table and want to see which countries your customers come from. Selecting country gives you a long list of repetitions. You add DISTINCT, and the result looks sensible. That is a useful starting point. Trouble starts when the same move becomes your universal method for repairing reports.

Before adding the keyword, I decide what one result row should represent. A country? A customer? A country and city combination? The answer determines which repetitions I am allowed to collapse. I will use small synthetic data throughout this guide. The syntax and behavior refer to PostgreSQL 18, so the examples do not depend on guessing which database engine is involved.

What SELECT DISTINCT does

SELECT DISTINCT keeps one occurrence of each different result row. The comparison includes every expression selected by SELECT. If at least one selected value differs, both rows can remain. This is how the PostgreSQL documentation defines the operation.

The operation affects the query result. It does not delete records, alter the customer table or establish a uniqueness rule for future data. Another reader can still retrieve the entire underlying table with all its repetitions. If your assignment is to repair the source, adding DISTINCT to a report does not complete that assignment.

Think of a dashboard filter showing available countries. You want to show each country once, regardless of how many customers live there. That is a reasonable application. If you are listing purchases, however, two purchases with the same amount are still two purchases. Their resemblance does not justify throwing one away.

Small data you can read in full

I use six customers. Every customer identifier is unique. In the following table, NULL means that the country or city is unknown; it is not a four-letter text value.

The first query is self-contained. Its CTE supplies synthetic data for this statement alone. You do not need an existing customer table in your database.

WITH customers(customer_id, country, city) AS (
    VALUES
        (1, 'PL', 'Warsaw'),
        (2, 'PL', 'Warsaw'),
        (3, 'PL', 'Krakow'),
        (4, 'DE', 'Berlin'),
        (5, 'ES', 'Madrid'),
        (6, NULL, NULL)
)
SELECT DISTINCT country
FROM customers
ORDER BY country NULLS LAST;

The result contains DE, ES, PL and one NULL. Three occurrences of PL become one because country is the only selected value. The customer identifier and city do not participate in that comparison. Being present in the source does not automatically make them part of the output identity.

In the remaining fragments, customers means this same dataset. To run each fragment independently, place the same WITH block before it. A CTE is not a permanent table and does not remain available after its statement finishes. This matters when you copy a short example into a new editor tab.

Adding a column changes what unique means

Now I need a list of country and city pairs. I change the output columns while keeping the data unchanged.

SELECT DISTINCT country, city
FROM customers
ORDER BY country NULLS LAST, city NULLS LAST;

The result has five pairs: DE/Berlin, ES/Madrid, PL/Krakow, PL/Warsaw and NULL/NULL. The two Warsaw customers produce one pair. Krakow and Warsaw remain separate entries. Sharing the same country is insufficient to merge the two cities into a single result row.

If I add customer_id, the result has six rows. Every identifier differs. This explains a common surprise: you added DISTINCT, yet the report did not become shorter. Inspect the entire selection, particularly identifiers, timestamps and technical columns. One changing value is enough to distinguish otherwise similar rows.

You cannot use this construction to obtain a single Poland row while also preserving every different Polish city as one ordinary scalar value. Choose the intended answer: a list of pairs, an aggregated list of cities or one representative record selected by an explicit rule. That choice defines what the report means.

A value list and a value count answer different questions

SELECT DISTINCT country returns a list. COUNT(DISTINCT country) returns the number of different non-null values. Our list has four rows, but the count is three because this form of COUNT ignores NULL. The aggregate expression documentation explains the distinction.

SELECT COUNT(*) AS customer_rows,
       COUNT(country) AS known_country_rows,
       COUNT(DISTINCT country) AS known_countries
FROM customers;

The answer is 6, 5 and 3, respectively. The first number covers every customer. The second counts customers with a populated country. The third counts the different known countries represented. These numbers are not interchangeable, even though all three calculations concern the same dataset.

To count the rows of the distinct list itself, I can put that list in a subquery and apply COUNT(*) outside it. That gives four. The same approach can count the five different country and city pairs. It also avoids presenting one engine’s multi-column counting syntax as if every SQL dialect accepted it.

I would label the metric “number of known customer countries.” A short label such as “countries: 3” hides the missing information. The reader could reasonably assume that the geographical data is complete. A correct count needs a label that communicates its scope and treatment of missing values.

When the source has no rows, its distinct country list is empty too. The distinct country count is zero. Distinguish that case from one customer with an unknown country: the list then contains a missing value, although the count remains zero.

Distinct-country counts also cannot be blindly added across segments. Imagine online customers from PL and DE, and physical-store customers from PL and ES. Each channel has two known countries, but their combined list has three. Poland occurs in both channels. For the overall population, establish the distinct set again instead of adding the prepared counts. The same question matters when combining monthly customer reports: one person can return in another month.

NULL, empty text and the missing-value rule

PostgreSQL considers NULL values equal for duplicate elimination. If two customers had an unknown country, the single-column country list would still include only one NULL. Do not extend this rule to an ordinary country = NULL condition. Use IS NULL when checking whether a value is missing.

In PostgreSQL, empty text '' differs from NULL. A country consisting of a space is another case again. If an import uses all these representations, a unique list can contain more entries than you expect. First decide which representations actually mean the same thing in your source.

I do not automatically replace missing values with the text unknown before looking at the data. If that text already appears as a genuine source value, the replacement combines two categories. During a quality investigation, keeping technical absence, empty text and an explicit response separate can help reveal their different causes.

A small diagnostic report showing the frequency of each representation helps. Before standardizing anything, inspect how many records the change would affect. DISTINCT is useful for seeing the variants. By itself, it does not show whether an odd value affects one record or most of your customer population.

When I choose GROUP BY

When I want the number of customers per country, I use grouping. With the same missing-value policy, I can compare frequencies and understand why repetitions exist.

SELECT country, COUNT(*) AS customers_in_country
FROM customers
GROUP BY country
ORDER BY country NULLS LAST;

The result is DE: 1, ES: 1, PL: 3 and NULL: 1. The country list now includes record counts. GROUP BY collects rows into groups on which aggregates can operate. DISTINCT serves the simpler purpose of showing different output rows.

Using GROUP BY country without aggregates can produce the same list as SELECT DISTINCT country. I choose the expression that communicates the question clearly. I do not mechanically swap the two constructions when additional columns, aggregates or conditions are involved. A small textual change can alter the answer or make the statement invalid.

The business question is a good guide. “Which countries occur?” asks for a set of values. “How many customers belong to each country?” asks for groups and a measure. Writing the question above your query often makes the intended construction obvious before you start changing SQL.

Repetitions after JOIN need investigation

Imagine one customer with two orders. Joining customers to orders produces that customer’s identifier twice. This is a natural consequence of a one-to-many relationship. Selecting only the customer identifier and applying DISTINCT can legitimately produce a list of customers with matching orders.

Summing sales is a different task. Joining orders to line items and then to payments can multiply amounts. DISTINCT on the final report cannot automatically reconstruct the correct sum, especially when aggregation has already happened. Two separate transactions can have exactly the same amount. Equal amounts do not prove that the underlying events are duplicates.

I inspect identifiers on both sides and the expected number of matches. Does the join use the complete key? Does a dimension contain several versions of a customer? Is the report mixing order and line-item detail? These questions lead toward a cause. Making the output shorter may merely conceal a symptom.

If I only need customers who have an order, a correlated WHERE EXISTS can express that directly. The documentation defines EXISTS as a test for a returned row. I do not need to return every matching order. For a closer look at the relationship itself, see INNER JOIN.

Ordering and choosing the latest record

DISTINCT does not guarantee presentation order. That is the role of ORDER BY. If a country list appears sorted without it, do not make that an export or interface contract. A different execution plan may return the same set in a different order.

Choosing the latest customer record is another separate problem. Ordinary DISTINCT customer_id, updated_at retains different timestamps for the same customer. Adding a date to ORDER BY does not change which output combinations are compared. You need a selection rule that uses the date and resolves ties.

PostgreSQL provides the DISTINCT ON extension. Its selection expressions must match the leftmost ordering expressions, while later criteria establish which record is preferred. Other engines often use ROW_NUMBER followed by an outer filter. I would not present PostgreSQL’s extension as portable SQL accepted by every database.

Before selecting the latest row, establish what the date means. Import time, event time and correction time describe different things. If two records share a timestamp, a stable additional identifier resolves a technical tie. It does not replace a business decision about which version is the right one to report.

Text comparisons and query cost

For text, equality also depends on types and comparison settings. PostgreSQL collations support comparison rules that can disregard some differences. I therefore do not assume that every engine treats letter case, accents or trailing spaces identically. An example that works under one configuration needs that context when moved elsewhere.

If a company considers PL and pl the same code, record that rule and apply it through a controlled transformation. Preserve the original values, because normalization can remove useful distinctions. Product names, identifiers and survey responses do not necessarily share one valid comparison policy.

Duplicate elimination has a cost. Its size depends on the plan, row count, result width, indexes and available resources. I do not promise that DISTINCT is always slow or that replacing it with GROUP BY will make a query faster. Use EXPLAIN and an appropriate measurement environment to assess a particular statement.

A unique customer is different from a unique customer name

Return to the question of identity. Two people can share a name. One person can change their surname. Counting different names gives you the number of different name strings, not automatically the number of customers. Names are useful for display, but counting people usually requires a stable identifier.

In our dataset, customers one and two live in the same city. Even if they shared a first name, they could still be separate people. Collapsing them would be appropriate for a list of first names and wrong for a list of program participants. A query can be syntactically valid while answering the wrong business question.

The same issue appears with product names. Two variants can share a marketing name while having different codes and package sizes. Before counting unique products, I establish whether the unit is a model, variant, stock item or seller offer. Each level requires a different set of fields. DISTINCT implements the definition you select; it cannot select that definition on your behalf.

When the source has no stable identifier, state the limitation. You can produce candidates for manual review using several attributes, but do not declare them proven duplicates. Matching country, city and surname is evidence worth inspecting. Two legitimate people may still share all those attributes.

DISTINCT inside a sum can remove valid sales

Consider a separate synthetic example: three orders have amounts 100, 100 and 50. Each has a different identifier. Total sales are 250. Although two amounts are equal, they belong to separate purchases that should both remain in the report.

Applying SUM with DISTINCT to the amount feeds only the different values 100 and 50 into the aggregate. The answer is 150. That is the correct sum of different numeric values, but the wrong revenue for these orders. The aggregate expression documentation cited earlier defines this selection of distinct arguments. A transaction amount is not a transaction identifier.

When diagnosing multiplication after a join, I compare counts and totals at the event identifier level. An order with three line items can repeat its header amount three times. A solution may involve aggregating line items first or joining sources at compatible detail. The right approach depends on the question the report must answer.

I also keep a small control case with two equal, legitimate amounts. It quickly exposes the idea of repairing every sum by inserting DISTINCT. If the change removes one of two different orders, the correction does not satisfy the report definition. A smaller number is not, by itself, evidence of a better answer.

Handing the result to someone else

When I deliver a country list, I include its source and scope. Does it cover every customer or active customers only? Is country taken from a delivery address or a profile? Does the list represent current attributes or order history? Each choice can change the set without changing the keyword DISTINCT.

I do not append an arbitrary city just to make an export look richer. An extra column changes the unit represented by a row, while the recipient may still assume that each country occurs once. When both views are needed, I provide two explicitly named tables: countries, and country and city combinations.

Before handing over the result, compare its row count with its stated definition. Inspect missing values, a frequent variant and a rare one. In this guide’s small dataset, reading six records is enough. The same reasoning helps plan checks for a larger report instead of assuming that a short output must be correct.

Exercise: before adding DISTINCT

Add customer 7, with country PL and city Gdansk. Predict the number of entries in the country list, the number of country and city pairs, and the row count when all three columns are selected. Explain each answer by naming the complete combination being compared.

The country list still has four entries, and the known-country count is still three. The pair list grows to six. The complete result including the identifier has seven rows. This new customer adds a city and an identifier, but adds no new country to the population.

Apply the same reasoning to one of your own reports. Write a sentence defining a row, inspect repetitions together with their keys and decide whether they are valid. Then choose DISTINCT, grouping or a corrected join. For a reminder of the underlying query structure, continue with SELECT in SQL.