What is the MySQL DISTINCT Clause?

The DISTINCT clause in MySQL is used to retrieve unique values from a column or a combination of columns, eliminating duplicate rows from the result set.

Syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name;
  • column1, column2, ...: Specify the columns from which to retrieve unique combinations of data.
  • table_name: The name of the table to query.

Examples of Using DISTINCT in MySQL

1. Retrieve Unique Values from a Single Column

To fetch unique values from a single column, use DISTINCT with that column name.

Query:

SELECT DISTINCT city FROM customers;

Example Output:

+-----------+
| city      |
+-----------+
| New York  |
| Los Angeles|
| Chicago   |
| Houston   |
+-----------+

2. Retrieve Unique Combinations of Multiple Columns

When using DISTINCT on multiple columns, it considers the combination of the specified columns to determine uniqueness.

Query:

SELECT DISTINCT city, state FROM customers;

Example Output:

+-----------+-------+
| city      | state |
+-----------+-------+
| New York  | NY    |
| Los Angeles| CA   |
| Chicago   | IL    |
| Houston   | TX    |
+-----------+-------+

3. Count Unique Values

To count the number of unique values, use DISTINCT inside the COUNT() function.

Query:

SELECT COUNT(DISTINCT city) AS unique_cities FROM customers;

Example Output:

+---------------+
| unique_cities |
+---------------+
| 4             |
+---------------+

4. Combining DISTINCT with WHERE Clause

You can filter rows using the WHERE clause before applying DISTINCT.

Query:

SELECT DISTINCT city FROM customers WHERE state = 'CA';

Example Output:

+-------------+
| city        |
+-------------+
| Los Angeles |
| San Diego   |
+-------------+

Using DISTINCT in MySQL Workbench

  • Open MySQL Workbench.
  • Connect to your database server.
  • Write a query with the DISTINCT clause in the SQL editor.
  • Execute the query and view the unique results in the result grid.

Best Practices

Use DISTINCT with Purpose

Use DISTINCT with Purpose
Apply DISTINCT only when necessary, as it can impact performance by requiring MySQL to sort and filter the data.

Combine with WHERE or LIMIT
Filter the dataset with WHERE or reduce the result size using LIMIT to optimize queries.

 

Verify Requirements
If your data model enforces uniqueness at the database level (e.g., unique constraints), avoid redundant use of DISTINCT.

Index Key Columns
Adding indexes to the columns involved in DISTINCT queries can significantly improve performance.

Common Issues and Troubleshooting

  • Slow Performance:
    Queries with DISTINCT can be slow on large datasets. Use indexing and limit the data with a WHERE clause.

  • Unexpected Results:
    Ensure you include all necessary columns in the DISTINCT clause to avoid unintended duplicates.

[WpProQuiz 5]