Distinctness applies to the whole row: SELECT DISTINCT a, b returns each unique combination of a and b, not the unique values of each column on its own. GROUP BY a, b returns the same combinations and also lets you count or total each one. The keyword works this way in SQL Server, MySQL, PostgreSQL, SQLite and Oracle; the differences between them show up with NULLs, counting and keeping one full row per value.
SELECT DISTINCT city, state
FROM customers;
See it on a small table
The table below is an illustrative example, not real data. Picture a customers table with these six rows in its city and state columns:
| city | state |
|---|---|
| Springfield | IL |
| Springfield | IL |
| Springfield | MO |
| Portland | OR |
| Portland | ME |
| Portland | OR |
Here is what three queries return from it:
| Query | Rows returned | Result |
|---|---|---|
SELECT DISTINCT city FROM customers; |
2 | Springfield, Portland |
SELECT DISTINCT city, state FROM customers; |
4 | Springfield IL, Springfield MO, Portland OR, Portland ME |
SELECT city, state FROM customers; |
6 | Every row, duplicates included |
Springfield appears twice in the second result because it pairs with two different states. A row is dropped only when every listed column matches another row. PostgreSQL’s manual puts it plainly: two rows “are considered distinct if they differ in at least one column value.” Oracle’s SQL reference says the same thing from the other side: duplicate rows are “those with matching values for each expression in the select list.”
Where DISTINCT goes in the query
DISTINCT is written once, directly after SELECT, and covers the entire select list. The vendors’ syntax diagrams all show it that way, for example SQL Server’s SELECT [ ALL | DISTINCT ] ... <select_list>. A few practical consequences:
- You can’t apply DISTINCT to one column while keeping other columns “as is” in the same select. Every column you add becomes part of what counts as a duplicate.
SELECT DISTINCT(city), stateis not a function call. The parentheses just wrap the first column, and DISTINCT still covers bothcityandstate.ALLis the opposite keyword and the default, so leaving DISTINCT out keeps duplicates.
Filters and sorting work as usual around it. This returns the unique city and state pairs in two states, sorted:
SELECT DISTINCT city, state
FROM customers
WHERE state IN ('IL', 'MO')
ORDER BY state, city;
How NULL values are treated
If two rows have NULL in the same column and match everywhere else, DISTINCT keeps only one of them. The engines document this in different places:
- SQL Server: “Null values are considered equal for the purposes of the DISTINCT keyword.” (SELECT clause reference)
- PostgreSQL: “Null values are considered equal in this comparison.”
- MySQL: “When using DISTINCT, GROUP BY, or ORDER BY, all NULL values are regarded as equal.” (Problems with NULL values)
- SQLite: “For the purposes of detecting duplicate rows, values are compared using the IS DISTINCT FROM operator. Thus two NULL values are considered to be equal.” (SELECT reference)
- Oracle: the SELECT reference doesn’t say anything about NULL and DISTINCT, so check your own results if this matters.
So in the sample table, two rows of Portland with a NULL state would come back as one Portland, NULL row.
Count the unique combinations
This is where engines differ most. The portable way is a subquery that counts the rows DISTINCT returns:
SELECT COUNT(*) AS unique_locations
FROM (SELECT DISTINCT city, state FROM customers) t;
On the sample table this returns 4. The alias t is written without AS because Oracle’s syntax for a table alias has no AS keyword, while SQL Server, MySQL, PostgreSQL and SQLite show AS as optional there. SQL Server and MySQL also require the alias. Because the inner query keeps one row for each NULL combination, a Portland, NULL row is counted too.
MySQL also accepts a list of columns inside COUNT(DISTINCT ...):
SELECT COUNT(DISTINCT city, state) AS unique_locations
FROM customers;
The MySQL aggregate functions page says this returns “the number of distinct expression combinations that do not contain NULL.” That means it can come out lower than the subquery when some rows have NULL in either column. The SQL Server, SQLite and Oracle references document COUNT(DISTINCT ...) with a single expression, and PostgreSQL documents count with one input, so use the subquery there.
For a single column, COUNT(DISTINCT city) is documented in all five engines. It counts unique non-NULL cities, which is not the same as counting unique city and state pairs.
DISTINCT or GROUP BY?
For a plain list of unique combinations, these two queries return the same rows:
SELECT DISTINCT city, state FROM customers;
SELECT city, state FROM customers GROUP BY city, state;
MySQL’s manual describes this directly, saying that in most cases DISTINCT can be treated as a special case of GROUP BY. The practical difference is that GROUP BY lets you add an aggregate for each combination, which DISTINCT can’t:
SELECT city, state, COUNT(*) AS customers
FROM customers
GROUP BY city, state
ORDER BY customers DESC;
On the sample table this shows Springfield IL and Portland OR with 2 each, and the other two pairs with 1. Use DISTINCT when you only need the list and GROUP BY when you need numbers per group. Which one runs faster depends on the engine and the data; none of the vendor pages cited here promises a speed difference, so check your own execution plan if it matters.
Keep one full row per value
Sometimes you want one complete row per city, such as the newest customer in each. DISTINCT can’t do this, because adding the extra columns changes what counts as a duplicate.
PostgreSQL: DISTINCT ON
SELECT DISTINCT ON (city) city, state, name, created_at
FROM customers
ORDER BY city, created_at DESC;
PostgreSQL’s SELECT reference says DISTINCT ON “keeps only the first row of each set of rows where the given expressions evaluate to equal,” that the first row “is unpredictable unless ORDER BY is used,” and that the DISTINCT ON expressions “must match the leftmost ORDER BY expression(s).” That is why city comes first in the ORDER BY above. DISTINCT ON is PostgreSQL syntax; the other four engines covered here don’t use it.
Other engines: a window function
Engines that support window functions can number the rows in each group and keep the first:
SELECT city, state, name, created_at
FROM (
SELECT city, state, name, created_at,
ROW_NUMBER() OVER (PARTITION BY city ORDER BY created_at DESC) AS rn
FROM customers
) t
WHERE rn = 1;
Window function support arrived in different releases of each engine, so check your version’s documentation before relying on it.
Engine notes worth knowing
| Engine | What the vendor documents |
|---|---|
| SQL Server | With SELECT DISTINCT, column names and aliases used in ORDER BY must be in the select list. |
| Oracle | UNIQUE is a synonym for DISTINCT. ORDER BY can’t refer to columns outside the select list when DISTINCT is used. DISTINCT isn’t allowed when the select list contains LOB columns. |
| MySQL | DISTINCTROW is a synonym for DISTINCT; specifying both ALL and DISTINCT is an error. |
| PostgreSQL | DISTINCT ON for one row per key, with the ORDER BY rule above. |
| SQLite | Text values are compared using the column’s collation when it looks for duplicate rows. |
The ORDER BY rule catches people often. If you sort by a column you didn’t select, SQL Server and Oracle reject the query, because after duplicates are removed there may be several different values of that column behind one result row. Add the column to the select list, or sort by a column you did select.
Mistakes that make DISTINCT look broken
- Selecting an ID or timestamp. Add a unique column and nearly every row becomes unique, so DISTINCT seems to do nothing. Select only the columns that define a duplicate.
- Covering up a join. Duplicates that appear after joining two tables can mean the join matches more rows than you expected. Check the join condition before adding DISTINCT.
- Comparing the wrong counts.
COUNT(DISTINCT city), MySQL’sCOUNT(DISTINCT city, state)and the subquery count can all give different numbers on the same table, for the reasons above.
DISTINCT query answers
Can I make DISTINCT apply to only one of several selected columns?
Not with plain DISTINCT. Either select only that column, use GROUP BY with an aggregate for the others (for example MAX(created_at)), or use DISTINCT ON in PostgreSQL or a window function elsewhere.
Why does MySQL’s COUNT(DISTINCT a, b) give a smaller number than my subquery?
MySQL leaves out combinations that contain NULL, while the subquery keeps one row for each NULL combination and counts it.
Is SELECT UNIQUE the same as SELECT DISTINCT?
In Oracle, yes: the two keywords are synonyms. DISTINCT is the form all five engines accept. A UNIQUE constraint is a different thing that stops duplicates from being stored.
Where can I practice these queries?
Any local database works. You can install SQLite on Windows 11 for a single-file database, install SQL Server Express on Windows 10, or connect MySQL Workbench to a local server. If a test database won’t delete afterwards, see dropping a SQL database that is in use.
If your data lives in a spreadsheet rather than a database, the QUERY function in Google Sheets uses a SQL-like language of its own. For a database, start with the subquery count and the GROUP BY version, and you’ll have the two queries that answer most “how many unique combinations” questions.
To practice on a database that arrived as a file, first follow attaching an MDF file to SQL Server.

Kermit Matthews runs Live2Tech from just outside Philadelphia. He is an IT specialist with over 20 years of experience, from a university help desk to small-business IT, IT management and his own consulting practice, and has written tech guides full time since 2020. Steps are checked against the current software on a real device or the maker’s official documentation. More about Kermit.