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.

MySQL USE DATABASE

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.

MySQL CREATE TABLE

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.

MySQL INSERT INTO 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.

MySQL SELECT DISTINCT SINGLE COLUMN

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.

MySQL SELECT DISTINCT MULTIPLE COLUMNS

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'.

MySQL SELECT DISTINCT WITH WHERE

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.

MySQL SELECT DISTINCT WITH ORDER BY

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.