DataPlayArena
beginner

DISTINCT Keyword

6 min read

The DISTINCT keyword eliminates duplicate records from a query's result set, returning only unique values. It is a fundamental SQL operator used for data deduplication, exploratory analysis, and counting unique entities.

1. Single Column DISTINCT

When applied to a single column, DISTINCT evaluates all rows in that column and retains only the unique values.

sql
SELECT DISTINCT department
FROM employees;

Input Table (employees):

| employee_id | first_name | department | | :--- | :--- | :--- | | 101 | Alice | Engineering | | 102 | Bob | Marketing | | 103 | Charlie | Engineering | | 104 | Diana | Sales | | 105 | Evan | Marketing |

Output Result:

| department | | :--- | | Engineering | | Marketing | | Sales |

2. Multi-Column DISTINCT

When multiple columns follow DISTINCT, SQL evaluates the combination of all specified columns across rows. A row is considered a duplicate only if the values across all specified columns are identical.

sql
SELECT DISTINCT department, job_title
FROM employees;

Output Result:

| department | job_title | | :--- | :--- | | Engineering | Backend Engineer | | Engineering | Frontend Engineer | | Marketing | Content Lead | | Sales | Account Exec |

3. Combining COUNT() with DISTINCT

To calculate the number of unique entries in a column, pass DISTINCT inside the COUNT() aggregate function:

sql
SELECT COUNT(DISTINCT department) AS unique_department_count
FROM employees;

Result:

| unique_department_count | | :--- | | 3 |

4. Handling NULL Values with DISTINCT

In standard SQL, NULL represents an unknown value. When applying DISTINCT:

  • All NULL values in a column are grouped together as a single unique NULL entry.
  • COUNT(DISTINCT column_name) ignores NULL values automatically.
sql
SELECT DISTINCT city
FROM customers;

If city contains ['Seattle', 'Seattle', NULL, NULL, 'Boston'], the query returns ['Seattle', NULL, 'Boston'].

5. Performance Optimization: DISTINCT vs GROUP BY

Both DISTINCT and GROUP BY can be used to remove duplicate rows, but they serve different semantic goals in SQL engines.

sql
-- Approach A: Using DISTINCT
SELECT DISTINCT department
FROM employees;

-- Approach B: Using GROUP BY
SELECT department
FROM employees
GROUP BY department;
Optimization Tip

Modern database query planners (PostgreSQL, DuckDB, Spark SQL, Snowflake) convert both DISTINCT and GROUP BY into identical hash-aggregate or sort-aggregate execution plans. Use DISTINCT when you simply want unique rows, and GROUP BY when performing aggregations like SUM(), AVG(), or MAX().

6. Summary Key Rules

  1. DISTINCT comes immediately after SELECT.
  2. SELECT DISTINCT colA, colB evaluates unique combinations of colA AND colB.
  3. COUNT(DISTINCT col) ignores NULL values.
  4. DISTINCT causes a shuffle/sort or hash table build, so use it intentionally on large datasets.
Previous LessonLIMIT / TOP
Next LessonComparison Operators