VMware ESXi and vSphere Cluster Management

SQL SELECT DISTINCT: Return Unique Values

Learn how SQL SELECT DISTINCT removes duplicate result rows, returns unique values and combinations, handles NULL, filtering, ordering, and common troubleshooting cases.

SELECT DISTINCT returns only unique rows in an SQL result set. It is useful when a table contains repeated values but you need a list of the different values represented, such as cities, states, categories, or product types.

This lesson assumes you understand basic SELECT and FROM queries.

What SELECT DISTINCT Does

SELECT is the SQL clause used to choose columns or expressions for a query result. An ordinary SELECT returns every row that matches the query, including repeated values.

DISTINCT is a keyword placed immediately after SELECT. It removes duplicate result rows. Uniqueness is evaluated in the query output; DISTINCT does not delete or change data stored in the table.

For example, a customer table may contain many customers from the same city. A query for distinct cities returns each city once, making it useful for discovering which different cities are represented.

Basic Syntax

For one column, use this pattern:

SELECT DISTINCT column_name
FROM table_name;

For multiple columns, use this pattern:

SELECT DISTINCT column_name1, column_name2
FROM table_name;

The selected column list identifies the values or expressions to return. The FROM clause identifies the source table containing those columns.

Customer Sample Data

The following Customer table contains repeated city values:

Customer sample data

Columns: id, name, address, city, state, zip

Row 1: 1 | Bill Smith | 123 Main Street | Hope | CA | 98765

Row 2: 2 | Mary Smith | 123 Dorian Street | Harmony | AZ | 98765

Row 3: 3 | Bob Smith | 123 Laugh Street | Hope | CA | 98765

Rows 1 and 3 have the same city, Hope, even though they represent different customers.

Distinct Values in One Column

To return each city once, select only the city column with DISTINCT:

SELECT DISTINCT city
FROM Customer;

Result:

city

Hope

Harmony

The ordinary query preserves the repeated value:

SELECT city
FROM Customer;

Its result contains Hope, Harmony, and Hope. The distinct query compares the output values and returns Hope only once.

Query: SELECT city FROM Customer;

Returned city values: Hope, Harmony, Hope

Explanation: Ordinary SELECT returns every matching row.

Query: SELECT DISTINCT city FROM Customer;

Returned city values: Hope, Harmony

Explanation: DISTINCT removes the repeated output value.

DISTINCT with Multiple Columns

When you select more than one column, DISTINCT applies to the complete selected row, or combination of selected expressions.

SELECT DISTINCT state, city
FROM Customer;

A result row is considered a duplicate only when both state and city match another result row. A repeated city in different states can therefore appear more than once because the complete pair is different.

Selected columns: city

How duplicates are identified: Rows with the same city value

Example result: Hope, Harmony

Selected columns: state, city

How duplicates are identified: Rows with the same state-and-city pair

Example result: CA | Hope, AZ | Harmony

Selected columns: id, city

How duplicates are identified: Rows with the same id and city

Example result: 1 | Hope, 2 | Harmony, 3 | Hope

Be careful when selecting an identifier:

SELECT DISTINCT id, city
FROM Customer;

An identifier such as id is normally different for every customer. Because each selected row has a different id, the rows remain unique even when their cities match. If the goal is a list of cities, select city without the customer id.

DISTINCT and NULL Values

NULL is a marker for a missing or unknown value. If several source rows contain NULL in a selected column, DISTINCT returns one NULL entry for that column.

SELECT DISTINCT city
FROM Customer;

If some customers have no known city, the result may contain the non-NULL cities plus one NULL row.

This behavior is separate from equality comparisons in a WHERE clause. You do not test for NULL with = NULL. Use IS NULL or IS NOT NULL instead:

SELECT DISTINCT city
FROM Customer
WHERE city IS NOT NULL;

This query excludes the NULL entry before the distinct result is produced.

Filtering Before DISTINCT

WHERE filters source rows before DISTINCT removes duplicate output rows. This lets you find unique values within a subset of the table.

SELECT DISTINCT city
FROM Customer
WHERE state = 'CA';

The database first keeps customers whose state is CA. It then returns one instance of each city among those remaining customers.

Logical idea: filter the input rows with WHERE, choose the output column with SELECT, and remove duplicate output rows with DISTINCT.

Controlling Result Ordering

DISTINCT does not guarantee the order of returned rows. If the values must appear in a predictable order, add ORDER BY.

SELECT DISTINCT city
FROM Customer
ORDER BY city ASC;

ORDER BY city ASC displays the unique cities in ascending, usually alphabetical, order. Use the desired result column or columns in the ORDER BY clause.

Common Query Patterns

Goal: Return unique values from one column

Query pattern: SELECT DISTINCT column_name FROM table_name;

Key behavior: Repeated values appear once.

Goal: Return unique values after filtering

Query pattern: SELECT DISTINCT column_name FROM table_name WHERE condition;

Key behavior: WHERE limits the source rows before duplicate removal.

Goal: Return unique values in a specified order

Query pattern: SELECT DISTINCT column_name FROM table_name ORDER BY column_name ASC;

Key behavior: ORDER BY controls presentation order.

Goal: Return unique combinations

Query pattern: SELECT DISTINCT column_name1, column_name2 FROM table_name;

Key behavior: The complete pair or combination must match for a row to be removed.

When SELECT DISTINCT Is Appropriate

Creating a list of unique product categories, locations, states, or other repeated attributes.
Finding the different values represented in a column.
Returning unique combinations, such as state-and-city pairs.
Presenting a deduplicated list after intentionally selecting only the attributes that define uniqueness.

DISTINCT can hide duplicate rows in a query result, but it does not correct duplicate data in the underlying table. If it appears unexpectedly necessary, inspect the selected columns and any joins. A join may produce multiple matches for one entity, or the query may be selecting columns that make rows look different.

Performance Awareness

Removing duplicates requires database work. Depending on the database and query, it may use sorting, hashing, or another strategy to compare result rows.

Select only the columns you actually need.
Filter rows with WHERE before deduplicating when possible.
Avoid adding identifiers or other unnecessary columns when you want unique attribute values.
For large queries, inspect the query plan and measure performance using the tools provided by your database system.

These practices reduce the amount of data that must be compared, although exact performance depends on the database, data, and query.

Troubleshooting SELECT DISTINCT

Repeated values still appear

Likely cause: More than one column was selected, and the complete selected values differ between rows.

Check: Inspect every selected column, including expressions and identifiers.

Resolution: Select only the column or column combination that defines the desired unique result.

Every row remains after adding DISTINCT

Likely cause: A unique field such as a primary key, timestamp, or customer id is included in the select list.

Resolution: Remove the row-specific field if the goal is a list of repeated attributes such as cities.

Distinct values appear in an unexpected order

Likely cause: No ordering was requested. SQL does not guarantee row order without ORDER BY.

Resolution: Add ORDER BY with the desired result column or columns.

A NULL value appears in the unique result

Likely cause: One or more source rows have NULL in the selected column.

Resolution: Keep the single NULL entry if missing values should be represented, or exclude it with a condition such as WHERE city IS NOT NULL.

DISTINCT is being used to fix unexpected duplicates from a join

Likely cause: The join may produce multiple matches per entity.

Check: Review join keys, join conditions, and the number of matching rows in each joined table.

Resolution: Correct the join or aggregation logic first. Use DISTINCT only when unique output rows are genuinely the intended result.

Exam-Relevant Notes

DISTINCT appears immediately after SELECT.
It removes duplicate result rows; it does not modify stored table data.
With one selected column, duplicates are compared by that column's output value.
With multiple selected columns, duplicates are compared by the complete combination of selected values.
A unique id usually prevents duplicate elimination when included in the select list.
Multiple NULL values produce one distinct NULL result for the relevant selected column or combination.
WHERE filters rows before DISTINCT produces the final unique result.
Use ORDER BY when result order matters.