Khóa học miễn phí MySQL – Distinct Clause nhận dự án làm có lương

MySQL – Distinct Clause

Table of content


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 −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Hyderabad NULL
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Hyderabad 2000.00
4 Chaitali 25 Mumbai NULL
5 Hardik 27 Vishakapatnam 8500.00
6 Komal 22 Vishakapatnam 4500.00
7 Muffy 24 Indore 10000.00

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.

ADDRESS
Hyderabad
Delhi
Hyderabad
Mumbai
Vishakapatnam
Vishakapatnam
Indore

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.

ADDRESS
Hyderabad
Delhi
Mumbai
Vishakapatnam
Indore

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.

COUNT(DISTINCT ADDRESS)
5

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 −

SALARY
NULL
2000.00

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.

ADDRESS SALARY
Delhi 1500.00
Hyderabad NULL
Hyderabad 2000.00
Indore 10000.00
Mumbai NULL
Vishakapatnam 4500.00
Vishakapatnam 8500.00

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 −

SALARY
NULL
1500.00
2000.00
4500.00
8500.00
10000.00

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 −

To fetch distinct records from a MySQL table through PHP program, we need to execute SELECT statement with DISTINCT clause using the mysqli function query() as follows −

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

To fetch distinct records from a MySQL table through Node.js program, we need to execute SELECT statement DISTINCT clause using the query() function of the mysql2 library as follows −

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

To fetch distinct records from a MySQL table through Java program, we need to execute SELECT statement DISTINCT clause using the JDBC function executeUpdate() as follows −

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

To fetch distinct records from a MySQL table through Python program, we need to execute SELECT statement DISTINCT clause using the execute() function of the MySQL Connector/Python as follows −

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();

Output

The output obtained is as follows −

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)
  });
});             

Output

The output produced is as follows −

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();
    }
  }
}                           

Output

The output obtained is as shown below −

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()                                        

Output

Following is the output of the above code −

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

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc