Using DISTINCT to Retrieve Unique Values in MySQL

Course: MySQL Training

Course Link: MySQL Training Course – JustAcademy

Demo Link: Register for a Course Demo

1. Introduction to DISTINCT in MySQL

In MySQL, the DISTINCT keyword is used with the SELECT statement to retrieve unique values or unique combinations of values from a table. When a column contains repeated values, a normal SELECT query displays those values multiple times. Adding DISTINCT removes duplicate rows from the query result.

For example, a student database may contain several students enrolled in the same course. If you want to find which courses are available without displaying the same course repeatedly, you can use SELECT DISTINCT course FROM students;.

DISTINCT changes the result set returned by the query. It does not delete duplicate records from the original database table.

Why Is DISTINCT Important?

• Retrieves unique values from a column.
• Removes duplicate rows from query results.
• Helps identify different cities, courses, departments, or categories.
• Finds unique combinations of values across multiple columns.
• Supports data exploration and report preparation.
• Can be combined with filtering, sorting, and limiting results.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand the purpose of the DISTINCT keyword.
• Retrieve unique values from a single column.
• Retrieve unique combinations from multiple columns.
• Combine DISTINCT with WHERE, ORDER BY, and LIMIT.
• Understand how duplicate values and NULL values appear in results.
• Differentiate between DISTINCT and GROUP BY.
• Count unique values using COUNT(DISTINCT ...).
• Identify common mistakes and apply best practices.
• Use DISTINCT in practical database queries.

3. Understanding Duplicate and Unique Values

A duplicate value is a value that appears more than once in a particular column or result set. A unique value appears only once in the result after duplicate elimination.

Consider the following sample data from a students table.

In this table, the course value MySQL appears twice, and Python also appears twice. The city Mumbai appears twice. These repeated values can be removed from the query result by using DISTINCT.

4. Basic Syntax of DISTINCT

Syntax

SELECT DISTINCT column_name
FROM table_name;

Explanation:

• SELECT retrieves data from the table.
• DISTINCT removes duplicate rows from the selected result.
• column_name specifies the column to retrieve.
• FROM identifies the source table.

Example

SELECT DISTINCT course
FROM students;

This query returns each distinct course value from the students table. In the sample data, the result contains MySQL, Python, and Java, with each value appearing once. The order is not guaranteed unless you specify ORDER BY.

5. Creating a Sample Database and Table

Follow these steps to practise the examples in MySQL Workbench or the MySQL command-line client.

Step 1: Create and Select a Database

CREATE DATABASE college_db;
USE college_db;

Step 2: Create the Students Table

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    course VARCHAR(50),
    city VARCHAR(50),
    marks DECIMAL(5,2),
    status VARCHAR(20)
);

Step 3: Insert Sample Records

INSERT INTO students
(student_id, student_name, course, city, marks, status)
VALUES
(1, 'Rahul Sharma', 'MySQL', 'Mumbai', 78.50, 'Active'),
(2, 'Priya Patel', 'Python', 'Pune', 85.00, 'Active'),
(3, 'Amit Verma', 'MySQL', 'Mumbai', 69.50, 'Active'),
(4, 'Sneha Singh', 'Java', 'Delhi', 92.00, 'Active'),
(5, 'Karan Mehta', 'Python', 'Nashik', 55.00, 'Inactive'),
(6, 'Neha Joshi', 'Java', 'Pune', 88.00, 'Active'),
(7, 'Rohit Shah', 'SQL', 'Mumbai', 73.00, 'Active'),
(8, 'Meera Nair', 'MySQL', 'Delhi', 90.00, 'Active');

Step 4: View All Records

SELECT * FROM students;

Viewing the complete table helps you understand which values are repeated before applying DISTINCT.

6. Retrieving Unique Values from One Column

The most common use of DISTINCT is retrieving unique values from a single column.

Example 1: Find All Available Courses

SELECT DISTINCT course
FROM students;

Expected unique values: MySQL, Python, Java, and SQL. Although some courses belong to multiple students, each course appears once in the result.

Example 2: Find All Student Cities

SELECT DISTINCT city
FROM students;

This query returns each city represented in the student table. Mumbai, Pune, Delhi, and Nashik are returned once each.

Example 3: Find All Student Statuses

SELECT DISTINCT status
FROM students;

This query displays the different status values currently stored in the table, such as Active and Inactive.

Example 4: Retrieve Unique Marks

SELECT DISTINCT marks
FROM students;

This query returns each different marks value once. If several students have exactly the same stored marks value, that value appears only once in the result.

7. DISTINCT with Multiple Columns

You can apply DISTINCT to more than one selected column. In this case, MySQL removes duplicate rows based on the combination of values in all selected columns.

Syntax

SELECT DISTINCT column1, column2
FROM table_name;

Example 1: Unique Course and City Combinations

SELECT DISTINCT course, city
FROM students;

This query returns each unique combination of course and city. For example, MySQL, Mumbai is one combination and MySQL, Delhi is another. Both can appear because the combination is different, even though the course is the same.

Example 2: Unique Status and City Combinations

SELECT DISTINCT status, city
FROM students;

This displays each distinct pairing of student status and city. If several active students live in Mumbai, the combination Active, Mumbai appears only once.

Example 3: Unique Course, City, and Status Combinations

SELECT DISTINCT course, city, status
FROM students;

MySQL considers all three selected columns together when determining whether a result row is a duplicate.

Important Rule

SELECT DISTINCT course, city does not mean that course values and city values are each independently unique. It means the combination of course and city is unique in the returned rows.

8. Difference Between DISTINCT on One Column and Multiple Columns

Adding more columns can produce more rows because the combined values are more likely to differ. Always choose the columns that represent the exact kind of uniqueness you need.

9. Using DISTINCT with WHERE

The WHERE clause filters records according to a condition. DISTINCT then removes duplicate rows from the selected results.

Example 1: Unique Courses for Active Students

SELECT DISTINCT course
FROM students
WHERE status = 'Active';

This query returns the unique courses represented among active students only.

Example 2: Unique Cities for Students with Marks Above 80

SELECT DISTINCT city
FROM students
WHERE marks > 80;

Only students with marks greater than 80 are considered. Their city values are then returned without duplicates.

Example 3: Unique Courses in a Particular City

SELECT DISTINCT course
FROM students
WHERE city = 'Mumbai';

This query identifies the different courses taken by students living in Mumbai.

Example 4: Combine Multiple Conditions

SELECT DISTINCT course
FROM students
WHERE status = 'Active'
  AND marks >= 80;

This returns the unique courses of students who are active and have marks of at least 80.

10. Using DISTINCT with ORDER BY

The ORDER BY clause sorts the result set. It can be combined with DISTINCT to display unique values in a predictable order.

Example 1: Sort Unique Courses Alphabetically

SELECT DISTINCT course
FROM students
ORDER BY course ASC;

ASC sorts values in ascending order. For text values, this is generally alphabetical according to the applicable collation.

Example 2: Sort Unique Cities in Descending Order

SELECT DISTINCT city
FROM students
ORDER BY city DESC;

DESC sorts the returned city values in descending order.

Example 3: Retrieve Unique Course and City Combinations in Order

SELECT DISTINCT course, city
FROM students
ORDER BY course ASC, city ASC;

This sorts the unique combinations first by course and then by city.

11. Using DISTINCT with LIMIT

The LIMIT clause restricts the number of rows returned by a query. When combined with DISTINCT, the result contains only the requested number of unique rows.

Example 1: Retrieve the First Three Unique Courses

SELECT DISTINCT course
FROM students
ORDER BY course
LIMIT 3;

This query sorts the unique course values and returns at most three of them.

Example 2: Retrieve the First Two Unique Cities

SELECT DISTINCT city
FROM students
ORDER BY city
LIMIT 2;

Using ORDER BY with LIMIT makes the selection predictable when the underlying data remains the same.

12. DISTINCT and NULL Values

In MySQL, NULL represents a missing or unknown value. When DISTINCT is applied to a column containing multiple NULL values, the result includes only one NULL entry.

Example: Create a Table with NULL Values

CREATE TABLE course_enrollments (
    enrollment_id INT PRIMARY KEY,
    course VARCHAR(50),
    city VARCHAR(50)
);

INSERT INTO course_enrollments
(enrollment_id, course, city)
VALUES
(1, 'MySQL', 'Mumbai'),
(2, 'Python', 'Pune'),
(3, NULL, 'Delhi'),
(4, NULL, 'Mumbai'),
(5, 'MySQL', NULL);

Retrieve Unique Course Values

SELECT DISTINCT course
FROM course_enrollments;

The result contains MySQL, Python, and one NULL entry. The order is not guaranteed unless an ordering clause is used.

Exclude NULL Values

SELECT DISTINCT course
FROM course_enrollments
WHERE course IS NOT NULL
ORDER BY course;

Use IS NOT NULL when you want only actual non-NULL course values. An empty string is different from NULL and may need separate filtering.

13. Counting Unique Values with COUNT(DISTINCT)

The aggregate function COUNT(DISTINCT ...) counts distinct non-NULL values for the specified expression. It is useful when you need a number rather than a list of unique values.

Example 1: Count Different Courses

SELECT COUNT(DISTINCT course) AS unique_course_count
FROM students;

This query returns the number of different non-NULL course values in the table.

Example 2: Count Different Cities

SELECT COUNT(DISTINCT city) AS unique_city_count
FROM students;

This returns the number of distinct non-NULL cities represented in the student records.

Example 3: Count Unique Course and City Combinations

SELECT COUNT(*) AS unique_combinations
FROM (
    SELECT DISTINCT course, city
    FROM students
) AS distinct_course_city;

This query counts the distinct course-and-city combinations returned by the subquery. Unlike COUNT(DISTINCT course), which counts unique values in one expression, this approach counts rows formed from multiple selected columns. A combination containing NULL is still a row in the subquery and is counted by the outer COUNT(*).

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)

14. Difference Between DISTINCT and GROUP BY

Both DISTINCT and GROUP BY can produce results with one row per unique value or combination in many straightforward queries, but they serve different purposes.

Example Using GROUP BY for Course Counts

SELECT course, COUNT(*) AS student_count
FROM students
GROUP BY course;

This query returns each course and the number of student records associated with it. It answers a different question from simply listing unique course names.

15. DISTINCT with Aggregate Functions

DISTINCT can also be used inside certain aggregate functions to operate on unique values rather than every occurrence.

Example: Sum Distinct Marks

SELECT SUM(DISTINCT marks) AS sum_of_unique_marks
FROM students;

This adds each distinct non-NULL marks value once. It is different from summing all students' marks because repeated marks values are counted only once.

Example: Average of Distinct Marks

SELECT AVG(DISTINCT marks) AS average_of_unique_marks
FROM students;

This calculates the average of the distinct non-NULL marks values. It is not necessarily the same as the average across all student records.

When Should You Use Aggregate DISTINCT?

• Use COUNT(DISTINCT column) to count different non-NULL values.
• Use SUM(DISTINCT column) only when repeated values should intentionally be counted once.
• Use AVG(DISTINCT column) when the average should be calculated from distinct values rather than all records.
• Check the business requirement carefully because removing repeated numeric values can change the meaning of a calculation.

16. DISTINCT with JOIN Queries

When joining tables, one record from a table may match several records in another table. This can create repeated values in the result. DISTINCT can help return unique selected values or combinations.

Example: Create a Course Table

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

INSERT INTO courses (course_id, course_name)
VALUES
(1, 'MySQL'),
(2, 'Python'),
(3, 'Java');

Retrieve Unique Courses from Enrollment Records

SELECT DISTINCT s.course
FROM students AS s
JOIN courses AS c
  ON s.course = c.course_name;

This returns unique student course values that match a course in the courses table. Repeated student enrollments do not cause a course name to appear multiple times in this result.

Retrieve Unique Course and City Combinations

SELECT DISTINCT s.course, s.city
FROM students AS s
JOIN courses AS c
  ON s.course = c.course_name
ORDER BY s.course, s.city;

This query returns distinct course-and-city combinations among the rows that satisfy the join condition.

17. Common Mistakes When Using DISTINCT

18. Best Practices for DISTINCT

• Select only the necessary columns: Additional columns can change which rows are considered duplicates.
• Use ORDER BY when order matters: Do not rely on the order in which unique values happen to be returned.
• Understand NULL handling: A DISTINCT result can include one NULL value, while COUNT(DISTINCT column) ignores NULL.
• Choose the correct tool: Use DISTINCT for duplicate elimination and GROUP BY for grouped calculations.
• Check query performance: Removing duplicates can require extra processing, especially for large result sets.
• Use suitable indexes where appropriate: Indexes may help some queries, depending on the query plan and data distribution.
• Inspect execution plans for large queries: Use EXPLAIN to understand how MySQL plans to execute a query.
• Do not confuse result cleanup with data cleanup: DISTINCT does not fix duplicate records stored in a table.
• Validate the business meaning: Confirm that uniqueness is defined by the correct column or combination of columns.

19. Practical Project: Generate a Unique Course and City Report

In this project, you will use DISTINCT to explore the student database and create a simple report of unique values.

Task 1: List Unique Courses

SELECT DISTINCT course
FROM students
ORDER BY course;

Goal: Display each course once in alphabetical order.

Task 2: List Unique Cities

SELECT DISTINCT city
FROM students
ORDER BY city;

Goal: Identify all cities represented in the student data.

Task 3: List Unique Course and City Combinations

SELECT DISTINCT course, city
FROM students
ORDER BY course, city;

Goal: Find every different combination of course and city.

Task 4: Find Unique Courses Among High-Scoring Students

SELECT DISTINCT course
FROM students
WHERE marks >= 80
ORDER BY course;

Goal: Identify the courses taken by students who have marks of at least 80.

Task 5: Count Unique Courses

SELECT COUNT(DISTINCT course) AS unique_course_count
FROM students;

Goal: Return the number of different non-NULL course values.

Task 6: Count Students by Course

SELECT course, COUNT(*) AS student_count
FROM students
GROUP BY course
ORDER BY course;

Goal: Compare a unique-value report with a grouped report that calculates the number of students in each course.

Project Completion Checklist

• Confirm that repeated course values appear only once in the DISTINCT course query.
• Verify that different course-and-city combinations remain separate.
• Check that WHERE filters records before the distinct result is formed.
• Use ORDER BY to make the report easier to read.
• Compare COUNT(DISTINCT course) with the list of unique courses.
• Explain why DISTINCT does not delete duplicate records from the original table.

20. Frequently Asked Interview Questions

Q1. What is DISTINCT in MySQL?

DISTINCT is a keyword used with SELECT to eliminate duplicate rows from the result set.

Q2. Does DISTINCT delete duplicate records from a table?

No. It removes duplicate rows only from the query result. The original table remains unchanged.

Q3. Can DISTINCT be used with multiple columns?

Yes. It returns unique combinations of the selected columns.

Q4. What is the difference between DISTINCT and GROUP BY?

DISTINCT eliminates duplicate result rows. GROUP BY forms groups and is commonly used with aggregate functions to calculate results for each group.

Q5. Can DISTINCT be combined with WHERE?

Yes. WHERE filters the qualifying records, and DISTINCT removes duplicate rows from the selected result.

Q6. Can DISTINCT be combined with ORDER BY?

Yes. ORDER BY sorts the distinct result rows.

Q7. What happens to NULL values with DISTINCT?

Multiple NULL values in the selected column are represented by one NULL entry in the distinct result.

Q8. What does COUNT(DISTINCT column) do?

It counts distinct non-NULL values in the specified expression.

Q9. Does DISTINCT guarantee alphabetical ordering?

No. Use ORDER BY when you need a particular order.

Q10. What happens when extra columns are added to SELECT DISTINCT?

Uniqueness is evaluated across all selected columns, so adding columns may cause more rows to be considered distinct.

Q11. Can DISTINCT be used with LIMIT?

Yes. LIMIT restricts how many rows are returned after duplicate elimination, and ORDER BY can make the chosen rows predictable.

Q12. When should DISTINCT be avoided?

Avoid unnecessary DISTINCT operations when the selected data is already unique or when the actual requirement is to aggregate or correct underlying data. Extra duplicate-elimination work can be costly for large queries.

21. Practice Questions

1. Write a query to retrieve all unique courses from the students table.
2. Write a query to display each city only once.
3. Write a query to retrieve unique course and city combinations.
4. Write a query to list unique courses for active students only.
5. Write a query to display unique cities for students with marks greater than 75.
6. Write a query to sort unique course names alphabetically.
7. Write a query to retrieve the first three unique cities in alphabetical order.
8. Write a query to count the number of distinct non-NULL course values.
9. Explain the difference between SELECT DISTINCT course and SELECT DISTINCT course, city.
10. Explain how DISTINCT handles NULL values.
11. Write a query to count students in each course using GROUP BY.
12. Explain why DISTINCT cannot be used to permanently remove duplicate records from a table.

22. Quick Revision

• SELECT DISTINCT column_name FROM table_name; retrieves unique values from a column.
• DISTINCT removes duplicate rows from the result set, not from the source table.
• Multiple selected columns are evaluated together to determine uniqueness.
• WHERE filters records according to a condition.
• ORDER BY sorts the resulting rows.
• LIMIT restricts the number of rows returned.
• DISTINCT returns at most one NULL entry for a selected column containing multiple NULL values.
• COUNT(DISTINCT column) counts distinct non-NULL values.
• GROUP BY is useful for grouped calculations and aggregate functions.
• Use only the columns required to define uniqueness and check query performance for large datasets.

23. Official MySQL Documentation

For more details about DISTINCT and SELECT syntax, refer to the official MySQL documentation:

• MySQL 8.4 Reference Manual – SELECT Statement
• MySQL Reference Manual – DISTINCT Optimization

24. Conclusion

The DISTINCT keyword is a useful part of MySQL data retrieval because it allows you to display unique values without repeated entries in the query result. It is especially helpful when exploring categories such as courses, cities, departments, and statuses, and when generating concise reports from tables containing repeated values.

By combining DISTINCT with WHERE, ORDER BY, LIMIT, COUNT, and JOIN operations, you can create more focused queries and understand the data in your database. Remember that uniqueness is determined by all selected columns, NULL values require special consideration, and DISTINCT does not change the underlying records.

Continue learning: Explore the JustAcademy MySQL Training Course or register for a course demo to learn more about MySQL and database querying.