MySQL DISTINCT Keyword

The DISTINCT keyword in MySQL is used to remove duplicate records from the results of a SELECT query. It ensures that the query returns only unique values in the specified columns.

Usage

The DISTINCT keyword is employed when you want to ensure that the query results contain only unique entries, eliminating any duplicate rows. It is placed directly after the SELECT keyword.

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

In this syntax, DISTINCT is applied to the combination of column(s) specified, ensuring only unique combinations of values are returned.

Examples

1. Basic Usage

SELECT DISTINCT country
FROM customers;

This query retrieves a list of unique countries from the customers table, eliminating any duplicates.

2. Multiple Columns

SELECT DISTINCT first_name, last_name
FROM employees;

In this example, the DISTINCT keyword ensures that the combination of first_name and last_name is unique in the results, which may still include rows with the same first name but different last names, and vice versa.

3. Combined with WHERE Clause

SELECT DISTINCT category
FROM products
WHERE price > 50;

Here, the DISTINCT keyword is used with a WHERE clause to find unique product categories where the price is greater than 50, enhancing both the specificity and uniqueness of the results.

4. Combined with ORDER BY

SELECT DISTINCT department
FROM employees
ORDER BY department;

This example demonstrates using DISTINCT with ORDER BY to retrieve unique department names and maintain a specific order in the results.

Tips and Best Practices

• Use DISTINCT with care. While DISTINCT removes duplicates, it can impact performance, especially in large datasets.
• Apply to necessary columns. Use DISTINCT only on columns where uniqueness is required to avoid unnecessary overhead.
• Combine with ORDER BY. When necessary, combine DISTINCT with ORDER BY to maintain a specific order in results.
• Be aware of NULLs. DISTINCT treats NULL values as equal, so only one NULL will be returned for each combination of columns, which can affect results if multiple NULLs are present across multiple columns.