SELECT DISTINCT in MySQL

What DISTINCT Changes

DISTINCT removes duplicate rows from the displayed result after selected expressions are evaluated. It does not modify the table and it does not promise an order.

SELECT DISTINCT class_name
FROM students
ORDER BY class_name;

One Column: Unique Values

Without DISTINCT the class appears once per student. With DISTINCT, identical class_name results collapse. The comparison follows the column collation, so case or accent variants may compare equal under a case-insensitive collation.

SELECT DISTINCT status
FROM students
ORDER BY status;

DISTINCT on Multiple Columns

SELECT DISTINCT class_name, status
FROM students
ORDER BY class_name, status;

DISTINCT applies to the pair, not separately to each column. Adding student_id, a unique key, makes every selected row unique and therefore makes DISTINCT unnecessary.

NULL and COUNT(DISTINCT)

SELECT DISTINCT marks FROM students;

SELECT COUNT(DISTINCT class_name) AS class_count
FROM students;

SELECT DISTINCT can show one NULL among repeated NULL results. COUNT(DISTINCT expression) counts distinct non-NULL values. For multiple expressions, read the current MySQL manual because NULL and tuple behavior must match your exact version and intention.

Displayed Duplicates vs Stored Duplicates

Mistakes and Practice

• Expecting DISTINCT to sort.
• Selecting a unique ID and wondering why nothing collapses.
• Using DISTINCT to hide bad joins.
• Confusing result uniqueness with database constraints.

1. List each status once.
2. List unique class/status combinations and predict three rows.
3. Count distinct classes.
4. Design a UNIQUE constraint for a school roll number.

Quick Summary

• DISTINCT removes duplicate selected rows only.
• Multiple columns form one comparison combination.
• ORDER BY is still required for predictable display order.
• Constraints, not DISTINCT, prevent invalid stored duplicates.

Official References

• SELECT Statement — MySQL 8.4
• COUNT(DISTINCT) — Aggregate Functions
• PRIMARY KEY and UNIQUE Constraints

References reviewed 14 August 2026.