MySQL - Distinct Clause

MySQL - The DISTINCT Clause

Understanding the MySQL DISTINCT Clause

The DISTINCT clause in MySQL is used with a SELECT statement to eliminate duplicate rows from a result set. It ensures that every row returned is unique based on the column(s) specified. This is fundamental for tasks like generating unique lists of user locations, product categories, or any other data where you only need to see each value once.

Syntax

The basic syntax for using the DISTINCT clause is straightforward:

SELECT DISTINCT column1, column2, ...
FROM table_name
WHERE [conditions];

Where:

• column1, column2, ... are the columns from which you want to retrieve unique values.
• table_name is the table you are querying.
• WHERE [conditions] is an optional clause to filter rows before the DISTINCT operation is applied.

Example: Getting Unique Addresses

First, let’s set up a sample CUSTOMERS table for our examples. Note the modern data types and constraints.

CREATE TABLE CUSTOMERS (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(255),
    SALARY DECIMAL(10, 2),
    JOIN_DATE DATE NOT NULL
);

-- Inserting sample data
INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY, JOIN_DATE) VALUES
('Ramesh', 32, 'Hyderabad', 2000.00, '2022-01-15'),
('Khilan', 25, 'Delhi', 1500.00, '2022-03-22'),
('Kaushik', 23, 'Hyderabad', 2000.00, '2023-04-10'),
('Chaitali', 25, 'Mumbai', 6500.00, '2021-11-05'),
('Hardik', 27, 'Vishakapatnam', 8500.00, '2023-01-30'),
('Komal', 22, 'Vishakapatnam', 4500.00, '2022-08-19'),
('Muffy', 24, 'Indore', 10000.00, '2023-05-01'),
('Priya', 25, 'Delhi', NULL, '2023-06-12');

If we select all addresses, we will see duplicates:

SELECT ADDRESS FROM CUSTOMERS;

Now, let’s use DISTINCT to get a unique list of all customer addresses.

SELECT DISTINCT ADDRESS FROM CUSTOMERS;

Output

The result set now contains only the unique addresses, with duplicates removed.

ADDRESS
Hyderabad
Delhi
Mumbai
Vishakapatnam
Indore

Using DISTINCT with Aggregate Functions (COUNT)

You can combine DISTINCT with aggregate functions like COUNT(), SUM(), or AVG() to perform calculations on unique values only. A common use case is counting the number of unique items.

Example: Counting Unique Locations

SELECT COUNT(DISTINCT ADDRESS) AS UniqueLocations FROM CUSTOMERS;

Output

This query returns the count of unique addresses.

UniqueLocations
5

Applying DISTINCT to Multiple Columns

When you use DISTINCT with multiple columns, it returns unique combinations of those columns. It does not find unique values for each column independently. This is a common point of confusion for beginners.

Example: Unique Combinations of Address and Age

SELECT DISTINCT ADDRESS, AGE FROM CUSTOMERS ORDER BY ADDRESS, AGE;

Output Analysis

Notice that ‘Delhi’ and ‘Hyderabad’ appear multiple times. This is because the combination of ADDRESS and AGE is unique for each row. For example, (‘Delhi’, 25) is a unique combination.

ADDRESS	AGE
Delhi	25
Hyderabad	23
Hyderabad	32
Indore	24
Mumbai	25
Vishakapatnam	22
Vishakapatnam	27

How DISTINCT Handles NULL Values

In the context of the DISTINCT clause, all NULL values are treated as a single group. If a column contains multiple NULLs, DISTINCT will return only one NULL in the result set.

Example: Distinct Salaries Including NULL

SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

Output

The result includes NULL as one of the distinct values.

SALARY
NULL
1500.00
2000.00
4500.00
6500.00
8500.00
10000.00

Performance Considerations and Alternatives

While DISTINCT is very convenient, it can be resource-intensive on large tables because the database must sort the data to find and eliminate duplicates. For many use cases, GROUP BY can achieve the same result and is sometimes more performant, especially if an index exists on the grouped column(s).

-- This query is functionally equivalent to SELECT DISTINCT ADDRESS
SELECT ADDRESS FROM CUSTOMERS GROUP BY ADDRESS;

Best Practice: For simple unique value retrieval, DISTINCT is perfectly readable and acceptable. For more complex aggregations or when performance is critical on large datasets, consider whether GROUP BY with an appropriate index offers a better solution. Always analyze your query performance using EXPLAIN.

Implementing DISTINCT in Client Applications (Modern Examples)

Here’s how to execute a DISTINCT query in modern application code, emphasizing security and best practices like connection management and parameterized queries.

Node.js (async/await)
Python (mysql-connector-python)
Java (JDBC with try-with-resources)
PHP (PDO)



This example uses the `mysql2/promise` library for modern asynchronous code, a connection pool for efficient database connections, and a try/catch/finally block for robust error handling.

```javascript
// main.js
const mysql = require('mysql2/promise');

async function getUniqueCustomerCities() {
  let connection;
  try {
    // Use a connection pool for better performance and resource management
    const pool = mysql.createPool({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'TUTORIALS',
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0
    });

    connection = await pool.getConnection();
    console.log('Successfully connected to the database.');

    const sql = "SELECT DISTINCT ADDRESS FROM CUSTOMERS WHERE ADDRESS IS NOT NULL ORDER BY ADDRESS;";
    const [rows] = await connection.execute(sql);

    console.log('Unique Customer Cities:');
    rows.forEach(row => {
      console.log(`- ${row.ADDRESS}`);
    });

    return rows;
  } catch (error) {
    console.error('Database query failed:', error);
  } finally {
    if (connection) connection.release(); // Release the connection back to the pool
  }
}

getUniqueCustomerCities();

This Python example uses a with statement (context manager) to ensure the database connection and cursor are automatically closed. It also uses a try/except block for error handling.

main.py

import mysql.connector
from mysql.connector import errorcode

def get_unique_customer_cities():
    try:
        # Connection details should ideally be stored in a config file
        connection = mysql.connector.connect(
            host='localhost',
            user='root',
            password='password',
            database='TUTORIALS'
        )

        # Using a with statement ensures the cursor is closed automatically
        with connection.cursor(dictionary=True) as cursor:
            query = "SELECT DISTINCT ADDRESS FROM CUSTOMERS WHERE ADDRESS IS NOT NULL ORDER BY ADDRESS;"
            cursor.execute(query)

            results = cursor.fetchall()
            print("Unique Customer Cities:")
            for row in results:
                print(f"- {row['ADDRESS']}")

    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("Something is wrong with your user name or password")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database does not exist")
        else:
            print(err)
    finally:
        if 'connection' in locals() and connection.is_connected():
            connection.close()
            print("MySQL connection is closed")

if __name__ == "__main__":
    get_unique_customer_cities()

This modern Java example uses a try-with-resources statement, which automatically closes the Connection, PreparedStatement, and ResultSet. This prevents resource leaks and simplifies the code.

DistinctExample.java

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DistinctExample {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASS = "password";

    public static void main(String[] args) {
        String sql = "SELECT DISTINCT ADDRESS FROM CUSTOMERS WHERE ADDRESS IS NOT NULL ORDER BY ADDRESS";
        List<String> cities = new ArrayList<>();

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            System.out.println("Successfully connected to the database.");

            while (rs.next()) {
                cities.add(rs.getString("ADDRESS"));
            }

        } catch (SQLException e) {
            System.err.println("Database operation failed: " + e.getMessage());
            e.printStackTrace();
        }

        System.out.println("Unique Customer Cities:");
        cities.forEach(city -> System.out.println("- " + city));
    }
}

This PHP example uses PDO (PHP Data Objects), which is the modern, recommended way to interact with databases in PHP. It supports prepared statements to prevent SQL injection and provides a consistent interface for different database systems. Error handling is managed via exceptions.

main.php

<?php
$host = 'localhost';
$db   = 'TUTORIALS';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // Fetch results as associative arrays
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Successfully connected to the database.\n";

    $sql = "SELECT DISTINCT ADDRESS FROM CUSTOMERS WHERE ADDRESS IS NOT NULL ORDER BY ADDRESS";
    $stmt = $pdo->query($sql);

    echo "Unique Customer Cities:\n";
    while ($row = $stmt->fetch()) {
        echo "- " . $row['ADDRESS'] . "\n";
    }

} catch (\PDOException $e) {
    // In a real application, you would log this error, not display it to the user.
    error_log($e->getMessage());
    // http_response_code(500);
    die("Database connection failed: " . $e->getMessage());
}

?>