DISTINCT

The DISTINCT Function

The DISTINCT clause is used to remove duplicate rows from query results. Without DISTINCT, all rows, including duplicates, are displayed.

Basic Syntax

SELECT DISTINCT column1, column2
FROM table_name;

SELECT without DISTINCT

SELECT DEPARTMENT FROM EMPLOYEE;

If there are 20 employees across 5 different departments, this query returns 20 rows with many repeated values.

SELECT with DISTINCT

SELECT DISTINCT DEPARTMENT FROM EMPLOYEE;

This query returns only 5 rows — one for each unique department.

A Simple Example

The EMPLOYEE table:

SELECT DISTINCT CITY FROM EMPLOYEE;
-- Result:
-- Chicago
-- New York
-- San Francisco

DISTINCT on Multiple Columns

DISTINCT can be applied to a combination of several columns.

SELECT DISTINCT DEPARTMENT, STATUS FROM EMPLOYEE;

Returns the unique combinations of department and status. For example: (IT, Active), (IT, Inactive), (HR, Active).

COUNT(DISTINCT ...)

Counts the number of unique values in a specific column.

SELECT COUNT(DISTINCT CITY) AS TOTAL_CITY_UNIQUE
FROM EMPLOYEE;
-- Result: 3

Without DISTINCT, COUNT will count all rows, including duplicates.

Counting Employees per Unique City

SELECT COUNT(DISTINCT CITY) AS CITY,
       COUNT(*) AS TOTAL_EMPLOYEE
FROM EMPLOYEE;
-- | CITY | TOTAL_EMPLOYEE |
-- | 3 | 20 |

DISTINCT in a Subquery

SELECT NAME FROM EMPLOYEE
WHERE CITY IN (SELECT DISTINCT CITY FROM EMPLOYEE WHERE SALARY > 5000000);

Fetches the names of employees who are from cities that are also the cities of high-salary employees.

DISTINCT with ORDER BY

SELECT DISTINCT DEPARTMENT
FROM EMPLOYEE
ORDER BY DEPARTMENT ASC;

Returns the list of unique departments in alphabetical order.

DISTINCT vs GROUP BY

DISTINCT and GROUP BY often produce the same output for simple cases:

-- Both of these produce the same output:
SELECT DISTINCT DEPARTMENT FROM EMPLOYEE;

SELECT DEPARTMENT FROM EMPLOYEE
GROUP BY DEPARTMENT;

However, GROUP BY is more flexible because it can be used with aggregate functions such as COUNT, SUM, and AVG.

Usage Tips

1. Use DISTINCT only when necessary — removing duplicates requires resources
2. Consider whether GROUP BY is more suitable for analytical needs
3. DISTINCT works on the entire combination of selected columns, not per column

After understanding how to remove duplicates, we will learn the BETWEEN and IN operators for more effective data filtering.