MySQL DISTINCT Clause

Introduction

In this chapter, we will learn about the DISTINCT clause in MySQL. The DISTINCT clause is used to remove duplicate rows from the result set of a SELECT statement, ensuring that only unique rows are returned. This is particularly useful when you want to avoid redundant data and get a clear view of unique values in a column or combination of columns. We will cover the syntax, examples, and important considerations for using the DISTINCT clause.

Syntax

The basic syntax for the DISTINCT clause is:

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

• column1, column2, ...: The columns to retrieve unique values from.
• table_name: The name of the table from which to select data.

Using DISTINCT

Example

SELECT DISTINCT last_name
FROM students;

This example retrieves unique values from the last_name column in the students table.

Example with Multiple Columns

SELECT DISTINCT first_name, last_name
FROM students;

This example retrieves unique combinations of first_name and last_name from the students table.

Combining DISTINCT with Other Clauses

Using DISTINCT with WHERE

SELECT DISTINCT last_name
FROM students
WHERE enrollment_date > '2023-01-01';

This example retrieves unique values from the last_name column for students who enrolled after January 1, 2023.

Using DISTINCT with ORDER BY

SELECT DISTINCT last_name
FROM students
ORDER BY last_name;

This example retrieves unique values from the last_name column and sorts the result set in ascending order.

Using DISTINCT with LIMIT

SELECT DISTINCT last_name
FROM students
LIMIT 3;

This example retrieves the first 3 unique values from the last_name column.

Full Example

Let's go through a full example where we create a table, insert data into it, and use the DISTINCT clause to retrieve unique values.

1. Create a Database:

CREATE DATABASE school;

2. Select the Database:

USE school;

3. Create a Table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    enrollment_date DATE
);

4. Insert Data into the Table:

INSERT INTO students (first_name, last_name, email, enrollment_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01'),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02'),
('Amit', 'Kumar', 'amit.kumar@example.com', '2023-07-03'),
('Neha', 'Verma', 'neha.verma@example.com', '2023-07-04'),
('Sahil', 'Mehta', 'sahil.mehta@example.com', '2023-07-05'),
('Rahul', 'Sharma', 'rahul.sharma2@example.com', '2023-07-06');

5. Use the DISTINCT Clause:

SELECT DISTINCT last_name
FROM students;

Output

last_name
Sharma
Singh
Kumar
Verma
Mehta

6. Use the DISTINCT Clause with Multiple Columns:

SELECT DISTINCT first_name, last_name
FROM students;

Output

first_name	last_name
Rahul	Sharma
Priya	Singh
Amit	Kumar
Neha	Verma
Sahil	Mehta

7. Combine DISTINCT with WHERE:

SELECT DISTINCT last_name
FROM students
WHERE enrollment_date > '2023-01-01';

Output

last_name
Sharma
Singh
Kumar
Verma
Mehta

8. Combine DISTINCT with ORDER BY:

SELECT DISTINCT last_name
FROM students
ORDER BY last_name;

Output

last_name
Kumar
Mehta
Sharma
Singh
Verma

9. Combine DISTINCT with LIMIT:

SELECT DISTINCT last_name
FROM students
LIMIT 3;

Output

last_name
Sharma
Singh
Kumar

Important Considerations

• Performance: Using DISTINCT can impact performance, especially on large datasets. Ensure that appropriate indexing is in place to optimize query performance.
• Combination of Columns: When using DISTINCT with multiple columns, the result set will include unique combinations of the specified columns.
• NULL Values: MySQL considers NULL values as distinct. If a column contains NULL values, each NULL is treated as a unique value.

Conclusion

The DISTINCT clause is used for retrieving unique values from a column or combination of columns in MySQL. This chapter covered how to use the DISTINCT clause, including examples of retrieving unique values, combining it with other clauses like WHERE, ORDER BY, and LIMIT.