SQL SELECT DISTINCT Clause: Syntax, Usage, and Examples

The SELECT DISTINCT clause in SQL helps you retrieve only unique values from a dataset. Instead of returning every record, it filters out duplicates so that each row in the result set is different. You’ll use it when you want to remove redundancy or get a clear list of distinct entries.

How to Use SQL SELECT DISTINCT

The basic syntax of a SQL SELECT DISTINCT query looks like this:

SELECT DISTINCT column1, column2, ...
FROM table_name;

This returns only the rows where the combination of values in the selected columns is unique. If you're selecting just one column, it returns all different values found in that column.

Here’s an example using a customers table:

SELECT DISTINCT city
FROM customers;

This query lists every city only once, no matter how many customers live there.

When to Use SELECT DISTINCT in SQL

Remove Duplicate Results

If your query joins tables or pulls from datasets with repeating entries, SELECT DISTINCT cleans up the final list. For instance:

SELECT DISTINCT product_id
FROM orders;

Even if a product appears in multiple orders, each product ID shows up just once.

List All Unique Categories or Types

Let’s say you want a list of all departments in your organization:

SELECT DISTINCT department
FROM employees;

Without SELECT DISTINCT, you’d get a department name for each employee. This way, you only get each department once.

Count Distinct Records

Sometimes, you want to know how many different values exist:

SELECT COUNT(DISTINCT department)
FROM employees;

This returns the number of different departments. This use case combines SELECT DISTINCT with COUNT, a common pattern in data analysis.

Build Drop-down Menus or Filters in Applications

When designing web forms, dashboards, or user interfaces, you often want to populate drop-downs with distinct values from the database:

SELECT DISTINCT category
FROM products;

You get a clean list to populate filters or categories in your UI.

Examples of SQL SELECT DISTINCT in Practice

Example 1: Distinct Cities From Customers

SELECT DISTINCT city
FROM customers;

If you have 1,000 customer records across 20 cities, this returns 20 rows—one per city.

Example 2: Unique Combinations of Columns

You can also select distinct combinations:

SELECT DISTINCT first_name, last_name
FROM employees;

This removes duplicates where both names are identical. If you use first_name alone, it might miss that multiple people have the same first name but different last names.

Example 3: SQL SELECT DISTINCT Count

SELECT COUNT(DISTINCT job_title)
FROM employees;

Here, you're getting the number of unique job titles in your dataset, which is common when analyzing diversity or structure.

Example 4: SQL Query Select DISTINCT on Multiple Tables

When using joins, duplicates often sneak in. Let’s say you want to list all customers who’ve placed at least one order:

SELECT DISTINCT c.customer_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

Even if a customer placed multiple orders, they’ll appear only once.

Learn More About SELECT DISTINCT in SQL

SELECT DISTINCT vs GROUP BY

At first glance, GROUP BY and SELECT DISTINCT might look similar. Both can give you unique combinations, but they behave differently.

• SELECT DISTINCT simply filters duplicates.
• GROUP BY allows aggregation functions (like SUM, COUNT, etc.).

For example:

SELECT DISTINCT department
FROM employees;

vs.

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

The first gives each department once. The second tells you how many employees are in each department.

SELECT COUNT DISTINCT SQL Gotchas

You might write:

SELECT COUNT(DISTINCT first_name, last_name)
FROM employees;

But that syntax will throw an error in many databases. Instead, you can concatenate fields or use tuples (if supported):

SELECT COUNT(DISTINCT CONCAT(first_name, ' ', last_name))
FROM employees;

Or:

SELECT COUNT(*)
FROM (
  SELECT DISTINCT first_name, last_name
  FROM employees
) AS unique_names;

This approach handles complex uniqueness.

Performance Considerations

Using SELECT DISTINCT can slow things down on large datasets. That’s because the database needs to sort or hash the results to identify and remove duplicates. If you only need part of the data or have indexes, consider alternatives like GROUP BY, subqueries, or limiting results with LIMIT.

SQL SELECT DISTINCT With WHERE

You can combine SELECT DISTINCT with WHERE clauses to filter data before checking for uniqueness:

SELECT DISTINCT city
FROM customers
WHERE country = 'USA';

This pulls unique cities from U.S. customers only.

SQL SELECT DISTINCT With ORDER BY

You can sort the results of a distinct query using ORDER BY:

SELECT DISTINCT department
FROM employees
ORDER BY department ASC;

The ORDER BY clause runs after DISTINCT, sorting the filtered result set.

Aliases in DISTINCT Queries

You can rename columns in your result using AS:

SELECT DISTINCT department AS dept
FROM employees;

This doesn’t affect how DISTINCT works but helps with readability, especially when returning results to users or apps.

SQL SELECT DISTINCT With NULLs

SQL treats NULL as a unique unknown. So if you have many NULL values in a column, SELECT DISTINCT will count them as one:

SELECT DISTINCT email
FROM users;

Even if 100 users have NULL emails, you’ll get only one row showing NULL.

SELECT DISTINCT on Part of the Row

SQL doesn’t support selecting distinct values on just part of the row unless the rest of the selected fields match exactly. For example:

SELECT DISTINCT first_name, email
FROM users;

Won’t return unique first_name values unless each email is also distinct.

If you want truly unique first names, use a subquery:

SELECT DISTINCT first_name
FROM users;

Or use GROUP BY with aggregate functions:

SELECT first_name, MAX(created_at)
FROM users
GROUP BY first_name;

SELECT DISTINCT With Joins and Aggregations

When you join tables, duplicates are common. Use SELECT DISTINCT to trim them:

SELECT DISTINCT p.product_id, p.name
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id;

Or combine with aggregates:

SELECT product_id, COUNT(DISTINCT order_id)
FROM order_items
GROUP BY product_id;

This gives the number of unique orders per product.

The SQL SELECT DISTINCT clause helps you write cleaner, smarter queries. You get rid of redundant results, simplify drop-downs, and calculate real counts without looping through extra data. Whether you’re querying large datasets, joining multiple tables, or building an analytics dashboard, using SELECT DISTINCT SQL techniques will streamline your results.