MySQL DISTINCT Keyword

MySQL DISTINCT Keyword

The MySQL DISTINCT keyword is used to return only distinct (different) values. This is essential for eliminating duplicate rows from the result set.

---

Syntax

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

The DISTINCT keyword has the following components:

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

---

Example MySQL DISTINCT Statement

Let's look at some examples of the MySQL DISTINCT keyword:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50)
);

This query creates a table named employees with columns for id, first_name, last_name, and department.

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO employees (first_name, last_name, department)
VALUES ('John', 'Doe', 'HR'),
       ('Jane', 'Smith', 'IT'),
       ('Jim', 'Brown', 'IT'),
       ('Jake', 'White', 'HR');

This query inserts four rows into the employees table.

Step 4: Using DISTINCT on a Single Column

Select distinct values from a single column:

SELECT DISTINCT department FROM employees;

This query retrieves distinct values from the department column in the employees table.

Step 5: Using DISTINCT on Multiple Columns

Select distinct combinations of multiple columns:

SELECT DISTINCT first_name, last_name FROM employees;

This query retrieves distinct combinations of the first_name and last_name columns in the employees table.

Step 6: Combining DISTINCT with WHERE

Select distinct values with a condition:

SELECT DISTINCT department 
FROM employees 
WHERE last_name = 'Doe';

This query retrieves distinct values from the department column in the employees table where the last_name is 'Doe'.

Step 7: Ordering Results with DISTINCT

Select distinct values and sort the results:

SELECT DISTINCT department FROM employees ORDER BY department ASC;

This query retrieves distinct values from the department column in the employees table, sorted in ascending order.

---

Conclusion

The MySQL DISTINCT keyword is a powerful tool for eliminating duplicate rows from the result set. Understanding how to use the DISTINCT keyword is essential for effective data querying and analysis in MySQL.