SQL SELECT DISTINCT: Remove Duplicates Correctly

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.

The database first forms the selected row values and then removes repeats from that result. SELECT DISTINCT city compares cities, while SELECT DISTINCT city, name compares city-and-name pairs. Ana and Ben share Leeds but have different names, so both pairs remain. Adding an identifier usually makes every selected row unique. This is why DISTINCT id, city does not produce one row per city when id is a primary key.

Duplicate elimination treats NULL values as the same for this operation. Multiple unknown cities become one NULL result even though ordinary NULL = NULL comparisons are unknown. This special result-set behaviour does not make NULL a normal comparable value everywhere else. Empty strings and NULL are still different values. Collation also matters for text: a case-insensitive collation may consider two differently cased labels equal, while another collation keeps both.

DISTINCT has no sorting promise. A database might sort internally to remove duplicates, or it might use hashing; neither implementation is an output-order contract. Add ORDER BY for presentation. A useful portable way to place unknown values last is ordering by CASE WHEN city IS NULL THEN 1 ELSE 0 END, then city, outside a derived table containing the distinct values. Separating these steps avoids dialect restrictions on ordering a DISTINCT query by an unselected expression.

Use COUNT(DISTINCT city) when the question asks for the number of different known cities. In this dataset that is two, because COUNT with a column ignores NULL. SELECT DISTINCT city returns three rows because it includes one unknown city. The two queries answer different questions. For the latest sale per customer, use an appropriate ranking query rather than DISTINCT customer_id, created_on. Distinct date-and-customer pairs are not automatically the latest pair. If unexpected duplicates originate from a join, inspect the matching keys before adding DISTINCT, which can conceal a relationship error.

DISTINCT works across all four systems. PostgreSQL DISTINCT ON is an additional feature with different syntax and is not portable. Multi-expression COUNT(DISTINCT ...) also differs by engine; select distinct pairs in a subquery and count that result if you need a portable pair count.

Check the engine-specific syntax and version requirements in SQLite DISTINCT processing.

Return the number of distinct known customer cities. Do not count the unknown city as a named location.

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

SELECT COUNT(DISTINCT city) AS known_cities FROM customers;

known_cities
2

There are two different non-NULL labels, Leeds and Porto. The second Leeds row does not increase the distinct count, and Dev’s missing city is excluded by COUNT(city). Contrast this scalar answer with the three-row city list in the earlier example.

Change the city-list SELECT to DISTINCT city,name and predict four rows, because no city-name pair repeats. Then count DISTINCT city and explain why its answer is two rather than the three-row city list. Add another customer with a missing city in a disposable copy: the list should still have one NULL while the count of known cities remains unchanged.