DISTINCT in SQL

Suppose a Students table contains the following courses:

If we execute:

SELECT course
FROM Students;

The result may contain:

Python
Java
Python
PHP
Python

To display each course only once, use DISTINCT.

The basic syntax is:

SELECT DISTINCT column_name
FROM table_name;

SELECT DISTINCT course
FROM Students;

This returns each unique course.

Example result:

Python
Java
PHP

Suppose many students are from the same city.

SELECT DISTINCT city
FROM Students;

This displays each city only once.

SELECT DISTINCT department
FROM Employees;

This query returns a list of unique departments.

For example:

IT
HR
Sales
Accounts

DISTINCT can be applied to multiple columns.

SELECT DISTINCT course, city
FROM Students;

Here, SQL considers the combination of course and city.

Consider this data:

Query:

SELECT DISTINCT course, city
FROM Students;

The duplicate Python + Patna combination appears only once.

DISTINCT only changes the query result. It does not remove duplicate records from the table.

SELECT DISTINCT course
FROM Students;

The original duplicate course values remain stored in the table.

DISTINCT is a query operation, while DELETE changes the stored data.

DISTINCT can be combined with the WHERE clause.

SELECT DISTINCT course
FROM Students
WHERE age > 18;

This returns unique courses only from students whose age is greater than 18.

You can sort unique values using ORDER BY.

SELECT DISTINCT course
FROM Students
ORDER BY course;

The unique courses will be displayed in ascending order.

SELECT DISTINCT course
FROM Students
ORDER BY course DESC;

This displays unique courses in descending order.

In MySQL, DISTINCT can be combined with LIMIT.

SELECT DISTINCT course
FROM Students
LIMIT 5;

This returns up to five unique course values.

One of the most useful combinations is COUNT(DISTINCT column).

SELECT COUNT(DISTINCT course)
FROM Students;

This returns the number of different courses in the table.

SELECT COUNT(DISTINCT city)
FROM Students;

This returns the number of unique cities represented in the table.

SELECT COUNT(DISTINCT department)
FROM Employees;

This can be used to determine how many different departments exist.

SELECT COUNT(DISTINCT course)
FROM Students
WHERE age > 18;

This counts the number of unique courses among students older than 18.

DISTINCT can also be used when retrieving data from multiple tables.

SELECT DISTINCT Students.name, Courses.course_name
FROM Students
INNER JOIN Courses
ON Students.course_id = Courses.course_id;

This can help avoid duplicate result rows when the same combination appears more than once.

If a column contains multiple NULL values, DISTINCT treats the NULL values as one distinct result value.

SELECT DISTINCT city
FROM Students;

If several records have NULL as their city, the result contains a single NULL entry for that distinct result.

SELECT DISTINCT name
FROM Students;

This returns each different student name only once.

Keep in mind that if two different people happen to have the same name, DISTINCT treats those identical name values as duplicates in this result.

SELECT DISTINCT fee
FROM Students;

This displays each different fee amount only once.

For example:

4000.00
10000.00
15000.00
35000.00

SELECT DISTINCT course, fee
FROM Students;

DISTINCT considers the complete combination of course and fee.

DISTINCT can be used inside aggregate functions.

SELECT COUNT(DISTINCT course)
FROM Students;

The query counts unique courses instead of counting every student row.

DISTINCT and GROUP BY can sometimes produce similar-looking results, but they are used for different purposes.

SELECT DISTINCT course
FROM Students;

This simply returns unique courses.

SELECT course
FROM Students
GROUP BY course;

GROUP BY is mainly used when grouping rows for aggregate calculations.

Suppose a training institute has students enrolled in several courses:

Python
Python
ADCA
Tally
Python
ADCA
Java
Tally

To find the available unique courses:

SELECT DISTINCT course
FROM Students;

Possible result:

Python
ADCA
Tally
Java

SELECT DISTINCT city
FROM Students
ORDER BY city;

This is useful for creating reports or understanding the different locations represented in a database.

SELECT DISTINCT course
FROM Students
WHERE fee > 5000
ORDER BY course;

This query returns unique courses where the fee is greater than 5000, sorted alphabetically.

DISTINCT should be placed immediately after SELECT.

Correct:

SELECT DISTINCT course
FROM Students;

Incorrect:

SELECT course DISTINCT
FROM Students;

Consider:

SELECT *
FROM Students;

This returns all columns and all matching rows.

Whereas:

SELECT DISTINCT course
FROM Students;

This returns only unique course values.

SELECT DISTINCT course
FROM Students
WHERE age >= 18
ORDER BY course ASC
LIMIT 10;

This query:

• Selects only the course column.
• Removes duplicate course values.
• Includes students aged 18 or above.
• Sorts courses in ascending order.
• Returns up to 10 unique courses.