DISTINCT Clause in MySQL

Introduction

When working with data, you may often encounter duplicate values in a column.

To retrieve unique (non-duplicate) values, MySQL provides the DISTINCT clause.

It helps clean and filter data by removing duplicates from query results.

What is the DISTINCT Clause

The DISTINCT clause is used with the SELECT statement to return unique values.

It eliminates duplicate rows from the result set.

Basic Syntax

SELECT DISTINCT column_name

FROM table_name;

Example

SELECT DISTINCT city FROM Students;

This returns unique city names from the Students table.

DISTINCT on Multiple Columns

You can apply DISTINCT to multiple columns.

SELECT DISTINCT city, age FROM Students;

This returns unique combinations of city and age.

How DISTINCT Works

  • It compares values in selected columns
  • Removes duplicate rows
  • Returns unique combinations

DISTINCT vs GROUP BY

Feature

DISTINCT

GROUP BY

Purpose

Remove duplicates

Group data

Aggregation

Not required

Often used with functions

Output

Unique rows

Grouped results

Example:

SELECT DISTINCT city FROM Students;

vs

SELECT city FROM Students GROUP BY city;

DISTINCT with ORDER BY

SELECT DISTINCT city

FROM Students

ORDER BY city ASC;

DISTINCT with WHERE

SELECT DISTINCT city

FROM Students

WHERE age > 18;

DISTINCT with COUNT

SELECT COUNT(DISTINCT city) FROM Students;

This counts unique cities.

Important Notes

  • DISTINCT applies to the entire row (or selected columns)
  • It is processed after SELECT
  • Works only with the SELECT statement
  • Can affect performance on large datasets

Execution Order

DISTINCT is applied after:

  1. FROM
  2. WHERE
  3. SELECT
  4. DISTINCT
  5. ORDER BY

Example Scenario

Find unique cities:

SELECT DISTINCT city FROM Students;

Count unique cities:

SELECT COUNT(DISTINCT city) FROM Students;

Common Mistakes

  • Expecting DISTINCT to remove partial duplicates
  • Using DISTINCT with unnecessary columns
  • Confusing DISTINCT with GROUP BY
  • Ignoring performance impact

Key Points to Remember

  • DISTINCT removes duplicate rows
  • Works with one or multiple columns
  • Used with SELECT only
  • Helps clean and filter data
  • Can be combined with WHERE, ORDER BY, and COUNT