SQL - DISTINCT Keyword

---

---

The SQL DISTINCT Keyword

The SQL DISTINCT keyword is used in conjunction with the SELECT statement to fetch unique records from a table.

We use DISTINCT keyword with the SELECT statetment when there is a need to avoid duplicate values present in any specific columns/tables. When we use DISTINCT keyword, SELECT statement returns only the unique records available in the table.

The SQL DISTINCT Keyword can be associated with SELECT statement to fetch unique records from single or multiple columns/tables.

Syntax

The basic syntax of SQL DISTINCT keyword is as follows −

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

Where, column1, column2, etc. are the columns we want to retrieve the unique or distinct values from; and table_name represents the name of the table containing the data.

DISTINCT Keyword on Single Columns

We can use the DISTINCT keyword on a single column to retrieve all unique values in that column, i.e. with duplicates removed. This is often used to get a summary of the distinct values in a particular column or to eliminate redundant data.

Example

Assume we have created a table with name CUSTOMERS in MySQL database using CREATE TABLE statement as shown below −

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

Following query inserts values into this table using the INSERT statement −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

The table obtained is as shown below −

First, let us retrieve the SALARY values from the CUSTOMERS table using the SELECT query −

SELECT SALARY FROM CUSTOMERS ORDER BY SALARY;

This would produce the following result. Here, you can observe that the salary value 2000 is appearing twice −

Now, let us use the DISTINCT keyword with the above SELECT query and then see the result −

SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

Output

This would produce the following result where we do not have any duplicate entry −

DISTINCT Keyword on Multiple Columns

We can also use the DISTINCT keyword on multiple columns to retrieve all unique combinations of values across those columns. This is often used to get a summary of distinct values in multiple columns, or to eliminate redundant data.

Example

In the following query, we are retrieving a list of all unique combinations of customer's age and salary using the DISTINCT keyword −

SELECT DISTINCT AGE, SALARY FROM CUSTOMERS ORDER BY AGE;

Output

Though the AGE column have the value "25" in two records, each combination of "25" with it's specific 'salary' is unique, so both rows are included in the result set −

DISTINCT Keyword with COUNT() Function

The COUNT() function is used to get the number of records retuned by the SELECT query. We need to pass an expression to this function so that the SELECT query returns the number of records that satisfy the specified expression.

If we pass the DISTINCT keyword to the COUNT() function as an expression, it returns the number of unique values in a column of a table.

Syntax

Following is the syntax for using the DISTINCT keyword with COUNT() function −

SELECT COUNT(DISTINCT column_name) 
FROM table_name WHERE condition;

Where, column_name is the name of the column for which we want to count the unique values; and table_name is the name of the table that contains the data.

Example

In the following query, we are retrieving the count of distinct age of the customers −

SELECT COUNT(DISTINCT AGE) as UniqueAge  FROM CUSTOMERS;

Output

Following is the result produced −

DISTINCT Keyword with NULL Values

In SQL, when there are NULL values in the column, DISTINCT treats them as unique values and includes them in the result set.

Example

First of all let us update two records of the CUSTOMERS table and modify their salary values to NULL

UPDATE CUSTOMERS SET SALARY = NULL WHERE ID IN(6,4);

The resultant CUSTOMERS table would be −

Now, we are retrieving the distinct salary of the customers using the following query −

SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

Output

Following is the output of the above query −