MySQL DISTINCT

MySQL DISTINCT Clause

The DISTINCT clause in MySQL is used to remove duplicate rows from the result set. It ensures that only unique rows are returned based on the specified column(s).

Syntax

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

• column1, column2: The columns you want to retrieve without duplicates.
• table_name: The name of the table to query.

1. Selecting Unique Values from a Single Column

Example

Get unique country names from the customers table:

SELECT DISTINCT country
FROM customers;

Result

+------------+
| country    |
+------------+
| USA        |
| Canada     |
| Australia  |
+------------+

2. Selecting Unique Values from Multiple Columns

When using multiple columns with DISTINCT, the combination of values in those columns must be unique.

Example

Get unique combinations of country and city:

SELECT DISTINCT country, city
FROM customers;

Result

+------------+-----------+
| country    | city      |
+------------+-----------+
| USA        | New York  |
| Canada     | Toronto   |
| Australia  | Sydney    |
+------------+-----------+

3. Using DISTINCT with Aggregate Functions

DISTINCT can also be used inside aggregate functions to perform operations only on unique values.

Example

Find the sum of distinct order amounts:

SELECT SUM(DISTINCT amount) AS total_unique_amount
FROM orders;

Result

+---------------------+
| total_unique_amount |
+---------------------+
| 15000.00            |
+---------------------+

4. Using DISTINCT with LIMIT

You can combine DISTINCT with LIMIT to retrieve a specified number of unique rows.

Example

Get the first 3 unique countries:

SELECT DISTINCT country
FROM customers
LIMIT 3;

5. Practical Example

Sample Table: products

+----+-------------+---------+--------+
| id | product_name| category| price  |
+----+-------------+---------+--------+
| 1  | Laptop      | Tech    | 1500.0 |
| 2  | Smartphone  | Tech    | 800.0  |
| 3  | Laptop      | Tech    | 1500.0 |
| 4  | Monitor     | Display | 200.0  |
| 5  | Tablet      | Tech    | 300.0  |
| 6  | Monitor     | Display | 200.0  |
+----+-------------+---------+--------+

Query: Unique Categories

SELECT DISTINCT category
FROM products;

Result:

+---------+
| category|
+---------+
| Tech    |
| Display |
+---------+

Query: Unique Product Names

SELECT DISTINCT product_name
FROM products;

Result:

+-------------+
| product_name|
+-------------+
| Laptop      |
| Smartphone  |
| Monitor     |
| Tablet      |
+-------------+

6. Common Errors

1. Using DISTINCT on All Columns:
   Using SELECT DISTINCT * may result in duplicates if any column values differ.

   SELECT DISTINCT *
   FROM products;
   

   This returns all unique rows across all columns.

2. Incorrect Placement of DISTINCT:
   Ensure DISTINCT is correctly placed after SELECT but before column names.

   -- Incorrect
   SELECT category DISTINCT FROM products;
   
   -- Correct
   SELECT DISTINCT category FROM products;
   

7. Performance Considerations

• Using DISTINCT can be resource-intensive for large data sets because MySQL must compare rows to identify duplicates.
• Optimize queries with proper indexing to improve performance.

8. Best Practices

1. Avoid Using DISTINCT as a Fix:
   If duplicates occur due to improper joins or logic, fix the query rather than relying on DISTINCT.

2. Index Key Columns:
   Indexing columns used in DISTINCT queries can improve performance significantly.

Let me know if you need further assistance or examples!