SQL DISTINCT Keyword — Eliminating Duplicates in Queries

The office needs one occurrence of each class name even when several students belong to that class.

DISTINCT removes duplicate rows from the projected result. Equality is evaluated over the selected values, rather than over every stored field.

SELECT DISTINCT class compares the class values returned by the query. SELECT DISTINCT class, city compares the pair, so one class can appear more than once when its cities differ. DISTINCT reads a deduplicated result; it does not remove duplicate source records.

Related concepts: Group and Summarize and INNER JOIN.

Imagine you have a students table, and you're asked: "How many different classes are there?" Simply selecting the class column will list all entries — including duplicates. This is where DISTINCT shines. It helps you remove duplicates and see only unique values.

SQL · portable concepts; preserved examples identify their engine

SELECT DISTINCT column1, column2, ...
FROM table_name;

The DISTINCT keyword in SQL is used to remove duplicate rows from the result set. When you apply SELECT DISTINCT to one or more columns, it ensures that only unique combinations of those column values appear in the final output.

For example, in the syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name;

SQL returns only one instance of each unique combination of column1, column2, etc., from table_name. If there are multiple rows with the same values in all the specified columns, only one of those rows will appear in the result.

SQL · portable concepts; preserved examples identify their engine

Let’s work with this school dataset:

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10),
  city VARCHAR(30)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '10A', 'Delhi'),
(2, 'Diya Iyer', '9B', 'Chennai'),
(3, 'Rohit Menon', '10A', 'Kochi'),
(4, 'Sneha Patil', '8C', 'Pune'),
(5, 'Mehul Agarwal', '9B', 'Delhi'),
(6, 'Aisha Khan', '8C', 'Hyderabad');

SQL · portable concepts; preserved examples identify their engine

Let’s list all the unique classes:

Use case: A school administrator wants to identify all the unique classes available in the school to create a summary of class sections.

SQL query description: SQL query to select the distinct values of the class column from the students table, eliminating any duplicate entries.

SELECT DISTINCT class
FROM students;

Result description: Result contains a list of unique class names that exist in the students table.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

If we don’t use DISTINCT:

SELECT class
FROM students;

You can see how duplicate values clutter the output — DISTINCT solves that instantly.

SQL · portable concepts; preserved examples identify their engine

You can also find unique combinations — like each unique (class, city) pair:

Use case: A school administrator wants to identify all unique combinations of classes and cities represented in the student database for planning region-wise class distribution.

SQL query description: SQL query to select distinct pairs of class and city from the students table, eliminating duplicate combinations.

SELECT DISTINCT class, city
FROM students;

Result description: Result contains a list of unique class and city combinations present in the student records.

SQL · portable concepts; preserved examples identify their engine

How many unique cities are students from?

Use case: A school administrator wants to find out how many unique cities students come from to analyze geographical diversity.

SQL query description: SQL query to count the number of distinct values in the city column from the students table, and label the result as unique_city_count.

SELECT COUNT(DISTINCT city) AS unique_city_count
FROM students;

Result description: Result contains a single number representing the total count of unique cities that students in the database belong to.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

• Use DISTINCT only when needed — it may slow down performance on large datasets.
• Understand column combinations — DISTINCT class and city is not the same as DISTINCT city alone.
• Prefer GROUP BY if you're also using aggregate functions (we’ll explore this in advanced topics).

Use a key to identify individual students and an appropriate selected tuple to define duplicates. DISTINCT can conceal an unintended join multiplication without fixing it. Add ORDER BY if the deduplicated list needs a stated display order.

Return each class once and explain why roll number should not be included in that DISTINCT list.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT DISTINCT class FROM practice_students ORDER BY class;

Including a unique roll number would make every selected pair different. The required result is about classes, so only the class value defines the duplicate.

DISTINCT removes duplicate rows from the projected result. Equality is evaluated over the selected values, rather than over every stored field.

Including a unique roll number would make every selected pair different. The required result is about classes, so only the class value defines the duplicate.

Continue your understanding with Group and Summarize and INNER JOIN.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.