SQL SELECT DISTINCT Explained with Examples | Remove Duplicate Records in SQL

 

SQL SELECT DISTINCT

When querying a database, you may encounter duplicate values in your results. The SQL SELECT DISTINCT statement allows you to return only unique values, making your data cleaner and easier to analyze.

Whether you're finding unique countries, product categories, job titles, or customer names, the DISTINCT keyword helps eliminate duplicate records from the result set.

In this guide, you'll learn how the SQL SELECT DISTINCT statement works, when to use it, and how to apply it effectively with practical examples.

What Is SQL SELECT DISTINCT?

The DISTINCT keyword is used with the SELECT statement to remove duplicate values from the query results.

Instead of returning every matching row, SQL returns only unique values.

---

SQL SELECT DISTINCT Syntax

Basic syntax:

SELECT DISTINCT column_name
FROM table_name;

You can also retrieve unique combinations of multiple columns.

SELECT DISTINCT column1, column2
FROM table_name;

---

Sample Customers Table

The examples below use the following table.

CustomerID	FirstName	Country	City
1	John	USA	New York
2	Alice	Canada	Toronto
3	David	Kenya	Nairobi
4	Grace	Tanzania	Mwanza
5	James	Kenya	Kisumu
6	Sophia	Canada	Toronto

Notice that some countries and cities appear more than once.

Selecting Unique Values

Retrieve every country only once.

SELECT DISTINCT Country
FROM Customers;

Result

Country
USA
Canada
Kenya
Tanzania

Without DISTINCT, Kenya and Canada would appear multiple times.

Selecting Distinct Values from Multiple Columns

The DISTINCT keyword can be applied to more than one column.

SELECT DISTINCT Country, City
FROM Customers;

SQL returns only unique Country-City combinations.

---

Using DISTINCT with ORDER BY

You can sort unique results using the ORDER BY clause.

SELECT DISTINCT Country
FROM Customers
ORDER BY Country ASC;

Result

Country
Canada
Kenya
Tanzania
USA

---

Using DISTINCT with WHERE

Filter records before removing duplicates.

Example:

SELECT DISTINCT City
FROM Customers
WHERE Country = 'Canada';

Result:

City
Toronto

---

Using DISTINCT with COUNT

Count the number of unique values.

SELECT COUNT(DISTINCT Country) AS TotalCountries
FROM Customers;

Result:

TotalCountries
4

This query counts the number of different countries in the table.

DISTINCT and NULL Values

If a column contains multiple NULL values, DISTINCT returns only one NULL in the result.

Example table:

ProductID	Category
1	Electronics
2	NULL
3	Electronics
4	NULL

Query:

SELECT DISTINCT Category
FROM Products;

Result:

Category
Electronics
NULL

---

DISTINCT vs GROUP BY

Both DISTINCT and GROUP BY can produce unique values, but they serve different purposes.

DISTINCT

Returns unique values.

SELECT DISTINCT Country
FROM Customers;

GROUP BY

Groups rows for aggregation.

SELECT Country, COUNT(*) AS TotalCustomers
FROM Customers
GROUP BY Country;

Use DISTINCT when you only need unique values. Use GROUP BY when you need calculations such as COUNT(), SUM(), or AVG().

Practical Examples

Retrieve unique job titles

SELECT DISTINCT JobTitle
FROM Employees;

Retrieve unique product categories

SELECT DISTINCT CategoryName
FROM Products;

Retrieve unique customer countries

SELECT DISTINCT Country
FROM Customers
ORDER BY Country;

Count unique departments

SELECT COUNT(DISTINCT Department)
FROM Employees;

Common Mistakes

Expecting DISTINCT to Remove Duplicate Rows Based on One Column Only

Consider this query:

SELECT DISTINCT Country, City
FROM Customers;

DISTINCT checks the combination of both columns, not just Country.

If two rows have the same country but different cities, both rows are returned.

Using DISTINCT Unnecessarily

Incorrect:

SELECT DISTINCT CustomerID
FROM Customers;

Since CustomerID is already unique, DISTINCT has no effect.

Best Practices

• Use DISTINCT only when duplicate values need to be removed.

• Avoid using DISTINCT as a substitute for proper database design.

• Combine DISTINCT with WHERE to filter data before removing duplicates.

• Use ORDER BY to display unique values in a meaningful order.

• Remember that DISTINCT applies to all selected columns together, not individually.

• Avoid unnecessary use of DISTINCT on primary key columns, since they are already unique.

When Should You Use SQL SELECT DISTINCT?

Use SELECT DISTINCT when you need to:

• Display unique countries or cities.

• Find unique product categories.

• Retrieve different job titles.

• Count unique values in a column.

• Remove duplicate records from query results.

• Generate clean reports without repeated values.

Conclusion

The SQL SELECT DISTINCT statement is a simple yet powerful tool for removing duplicate values from query results. It helps create cleaner reports, improves data analysis, and makes it easier to identify unique records in a database.

By combining DISTINCT with clauses such as WHERE, ORDER BY, and aggregate functions like COUNT(), you can write more effective SQL queries that return meaningful and organized results.

As you continue learning SQL, SELECT DISTINCT will become an essential part of your toolkit for querying and analyzing relational databases.