MySQL - Distinct Clause

MySQL DISTINCT clause

The DISTINCT clause in MySQL is used with a SELECT statement to return the distinct values (unique values) from a single or multiple of columns in a table. It ignores all the duplicates values present in the particular column(s) and returns only the distinct values.

We can use this clause in various scenarios, such as identifying unique customer names, unique customer id's, etc. It can be combined with other clauses such as WHERE, ORDER BY, and GROUP BY to filter the data further.

Syntax

Following is the syntax of the DISTINCT clause in MySQL −

SELECT DISTINCT column1, column2, ..., columnN
FROM table_name
WHERE conditions // optional

Where,

• (column1, column2,...,columnN) are the columns from which we want the distinct (unique) values.

• table_name is the name of the table from which we want to select data.

• WHERE conditions is optional. These are used to filter the data.

Example

Firstly, let us create a create a table named CUSTOMERS using the following INSERT query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

The following INSERT INTO statement adds 7 records into the above-created table −

INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES
(1, 'Ramesh', 32, 'Hyderabad', NULL),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Hyderabad', 2000.00),
(4, 'Chaital', 25, 'Mumbai', NULL),
(5, 'Hardik', 27, 'Vishakapatnam', 8500.00),
(6, 'Komal', 22, 'Vishakapatnam', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

Execute the below query to display all the inserted records in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

Now, let us retrieve the ADDRESS column from CUSTOMERS table without using the DISTINCT clause.

SELECT ADDRESS FROM CUSTOMERS;

Duplicate values are not ignored in the ADDRESS column.

Here, we are using the DISTINCT clause on the ADDRESS column −

SELECT DISTINCT ADDRESS FROM CUSTOMERS;

Output

As we can see in the output below, duplicate values are ignored in the ADDRESS column.

DISTINCT Clause with COUNT() Function

The MySQL count() function allows us to count the number of distinct values present in one or more columns of a table. Let us understand with the example below

Example

In the following query, we are using the MySQL COUNT() function to count the DISTINCT records in ADDRESS column of CUSTOMERS table −

SELECT COUNT(DISTINCT ADDRESS) FROM CUSTOMERS;

Output

There are 5 distinct records present in the ADDRESS column.

Example

In this query, we are retrieving unique SALARY records from the CUSTOMERS table where the ADDRESS is "Hyderabad".

SELECT DISTINCT SALARY FROM CUSTOMERS WHERE ADDRESS = "HYDERABAD";

Output

The output for the program above is produced as given below −

DISTINCT on Multiple Columns

We can use the MySQL DISTINCT keyword on multiple columns of a table to return all the unique combinations of values across those columns, i.e. removing redundant records in a table.

Example

In the following query, we are retrieving the distinct combinations of ADDRESS and SALARY columns from the CUSTOMERS table and orders the result set by the ADDRESS column in ascending order.

SELECT DISTINCT ADDRESS, SALARY FROM CUSTOMERS ORDER BY ADDRESS;

Output

As we can see in the output below, the duplicate values "Hyderabad" and "Vishakapatnam" appears twice in the result set because each combination of Hyderabad and Vishakapatnam with SALARY is unique.

DISTINCT with NULL values

If there are NULL values present in a specific column, the MySQL DISTINCT will treat them as unique values and includes them in the result set.

Example

Here, we are returning the distinct salary of the customers using the following query −

SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

Output

On executing the given program, the output is displayed as follows −

Distinct Clause Using a Client Program

In addition to fetch distinct records from a table with a MySQL query, you can also use a client program to perform the DISTINCT operation.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT DISTINCT EXPRESSION FROM TABLE_NAME [WHERE CONDITION]";
$mysqli->query($sql);

sql = "SELECT DISTINCT column1, column2, ..., columnN FROM table_name"; 
con.query(sql);

String sql = "SELECT DISTINCT column1, column2, ..., columnN 
FROM table_name WHERE conditions // optional";
statement.executeQuery(sql);

distinct_clause_query = "SELECT DISTINCT column1, column2, ...FROM table_name"
cursorObj.execute(distinct_clause_query);

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.
');
$sql = 'SELECT DISTINCT tutorial_title FROM tutorials_tbl where tutorial_id > 2';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      printf("Title: %s ",  
         $row["tutorial_title"], "\n");               
   }
} else {
   printf('No record found.
');
}
mysqli_free_result($result);
$mysqli->close();

Title: JAVA Tutorial Title: Learn PHP Title: Learn MySQL        

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,'Ramesh', 32, 'Hyderabad', NULL),(2,'Khilan', 25, 'Delhi', 1500.00),(3,'kaushik', 23, 'Hyderabad', 2000.00),(4,'Chaital', 25, 'Mumbai', NULL),(5,'Hardik', 27, 'Vishakapatnam', 8500.00),(6, 'Komal',22, 'Vishakapatnam', 4500.00),(7, 'Muffy',24, 'Indore', 10000.00);"
  con.query(sql);

  //Using DISTINCT operator
  sql = "SELECT DISTINCT ADDRESS FROM CUSTOMERS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});             

Connected!
--------------------------
[
  { ADDRESS: 'Hyderabad' },
  { ADDRESS: 'Delhi' },
  { ADDRESS: 'Mumbai' },
  { ADDRESS: 'Vishakapatnam' },
  { ADDRESS: 'Indore' }
]       

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class DistinctClause {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT DISTINCT ADDRESS FROM CUSTOMERS";
            rs = st.executeQuery(sql);
            System.out.println("Distinct records: ");
            while(rs.next()){
              String Address = rs.getString("Address");
              System.out.println("Address: " + Address);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}                           

Distinct records: 
Address: Ahmedabad
Address: Delhi
Address: Kota
Address: Mumbai
Address: Bhopal
Address: MP
Address: Indore        

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
distinct_clause_query = """
SELECT DISTINCT ADDRESS
FROM customers
"""
cursorObj.execute(distinct_clause_query)
# Fetch all the distinct addresses
distinct_addresses = cursorObj.fetchall()
# Printing the distinct addresses
for address in distinct_addresses:
    print(f"Address: {address[0]}")
cursorObj.close()
connection.close()                                        

Address: Hyderabad
Address: Kerala
Address: Mumbai
Address: Vishakapatnam
Address: Delhi