SQL SELECT DISTINCT — How to Remove Duplicate Rows from Results

The Problem: Duplicate Values

Let’s start by looking at a common situation. Using our Employees table, suppose you want to see which departments exist in the company. If you have not yet learned the basic SELECT command, check Chapter 3: SQL SELECT first.

+----+-----------+------------+--------+-----------+
| ID | Name      | Department | Salary | City      |
+----+-----------+------------+--------+-----------+
| 1  | Priya K.  | Sales      | 55000  | Mumbai    |
| 2  | John D.   | Marketing  | 62000  | London    |
| 3  | Arun M.   | IT         | 78000  | Delhi     |
| 4  | Emma W.   | Sales      | 51000  | Sydney    |
| 5  | Ravi S.   | IT         | 72000  | Mumbai    |
| 6  | Sara L.   | HR         | 58000  | New York  |
+----+-----------+------------+--------+-----------+

If you run a simple SELECT on the Department column:

SELECT Department
FROM Employees;

+------------+
| Department |
+------------+
| Sales      |
| Marketing  |
| IT         |
| Sales      |  ← duplicate
| IT         |  ← duplicate
| HR         |
+------------+
6 rows returned

The result shows Sales twice and IT twice. That is because multiple employees belong to the same department. This is where DISTINCT comes in.

SQL SELECT DISTINCT Syntax

The SELECT DISTINCT statement returns only unique values, automatically removing all duplicate rows from the result:

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

DISTINCT on a Single Column

Now let’s run the same query with DISTINCT:

SELECT DISTINCT Department
FROM Employees;

+------------+
| Department |
+------------+
| Sales      |
| Marketing  |
| IT         |
| HR         |
+------------+
4 rows returned

The duplicates are gone. Instead of 6 rows, we get only 4 unique departments.

Let’s try another column — unique cities where employees are located:

SELECT DISTINCT City
FROM Employees;

+-----------+
| City      |
+-----------+
| Mumbai    |
| London    |
| Delhi     |
| Sydney    |
| New York  |
+-----------+
5 rows returned

Mumbai appeared twice in the table (Priya K. and Ravi S.), but DISTINCT returns it only once.

DISTINCT on Multiple Columns

When you use DISTINCT with multiple columns, SQL removes rows where the combination of all listed columns is duplicated. Each unique pair (or group) of values is treated as one result:

SELECT DISTINCT Department, City
FROM Employees;

+------------+-----------+
| Department | City      |
+------------+-----------+
| Sales      | Mumbai    |
| Marketing  | London    |
| IT         | Delhi     |
| Sales      | Sydney    |
| IT         | Mumbai    |
| HR         | New York  |
+------------+-----------+
6 rows returned

Notice that Sales appears twice and IT appears twice — but the combination of Department + City is different each time. “Sales, Mumbai” is not the same as “Sales, Sydney”, so both are kept.

SELECT DISTINCT vs SELECT — Comparison

COUNT with DISTINCT

You can combine DISTINCT with the COUNT() function to count how many unique values exist in a column. This is incredibly useful for quick analysis. You will learn more about COUNT and other aggregate functions in Chapter 14: SQL COUNT, AVG, SUM.

SELECT COUNT(DISTINCT Department) AS TotalDepartments
FROM Employees;

+------------------+
| TotalDepartments |
+------------------+
| 4                |
+------------------+

Without DISTINCT, COUNT(Department) would return 6 (counting duplicates). With DISTINCT, it correctly returns 4 unique departments.

SELECT COUNT(DISTINCT City) AS TotalCities
FROM Employees;

+-------------+
| TotalCities |
+-------------+
| 5           |
+-------------+

For a complete reference of the DISTINCT syntax, see the official MySQL SELECT documentation. If you want to filter rows based on conditions instead of just removing duplicates, learn about the SQL WHERE clause in the next chapter.

If you work with data regularly, explore our ready-made Dashboard Templates on NextGenTemplates that use SQL-powered data for business reporting.

📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.

SELECT COUNT(DISTINCT City) AS UniqueCities
FROM Employees;

SELECT DISTINCT City
FROM Employees
ORDER BY City ASC;

+-----------+
| City      |
+-----------+
| Delhi     |
| London    |
| Mumbai    |
| New York  |
| Sydney    |
+-----------+

📖 Chapter 4 of 30 — SQL Tutorial on Neotech Navigators