DISTINCT — eliminate duplicate rows

DISTINCT removes duplicate rows from the result. It considers the combination of all selected columns.

Syntax

SELECT DISTINCT column1, column2
FROM table_name;

Finding unique values in a column

-- How many different departments do employees belong to?
SELECT DISTINCT department_id
FROM employees
ORDER BY department_id;

DISTINCT across multiple columns

When you list multiple columns, DISTINCT de-duplicates on the combination:

-- Unique (department, job) pairs
SELECT DISTINCT department_id, job_id
FROM employees
ORDER BY department_id, job_id;

COUNT(DISTINCT col) — count unique values

SELECT COUNT(DISTINCT department_id) AS unique_departments
FROM employees;

Counts only the distinct non-NULL values in the column.

When to use DISTINCT

• Exploring data: quickly see what values exist.
• After a JOIN that multiplies rows: de-duplicate when the same row can appear via multiple join paths.
• Checking cardinality: how many unique values does a column have?

Alternatives to DISTINCT

For large tables, GROUP BY is often faster and more explicit:

SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id;

Tips

• DISTINCT applies to the entire row, not just the first column.
• ORDER BY can only reference columns that appear in the SELECT when DISTINCT is used.
• DISTINCT ON (col) (PostgreSQL-only) keeps one row per distinct value of a specific column.