Distinct Keyword in SQL | SQL Select Distinct

SQL, which goes by the full form Structured Query Language, is a programming and a query language used to transact and query a database. We will discuss the DISTINCT keyword this time.

We use the DISTINCT keyword when we need non-repetitive data in return for a query.

In simple words, we can think of a DISTINCT keyword like the set, i.e., like a set deletes all the repetitive data, distinct keywords remove the repetitions in the data.

Why do we use the DISTINCT keyword?

The DISTINCT keyword is used to get distinct or different values from the query as output. We mostly use the DISTINCT keyword with the SELECT statement to obtain unique data.

Distinct keyword is used whenever we need to find data that is not shared among all the other data stored in the database.

Also, the columns contain many instances of duplicate data to remove these; we use distinct keywords in our SQL query.

Demo Database

We are using the DataFlair_Employee data, which contains five columns :

• Name of Employee (name_emp)
• Post of the employee (post_emp)
• Email (email)
• Age (age)
• Salary (salary)

First, let us inspect the contents of our database using the SELECT query.

Syntax: 

SELECT * FROM tableName ;

Query: 

SELECT * FROM dataflair_employee ;

This is what our DataFlair_Employee database looks like. As we can observe, we have multiple columns with repetitive data present.

To avoid this, we can use the DISTINCT keyword while writing the Query so that we can get the output of all the unique values.

DISTINCT Keyword in SQL

We use the DISTINCT keyword in our SELECT query to get the unique output of the query transaction we do on our database.
Syntax: 

SELECT DISTINCT column1, column2, … FROM table_name;

Parameters or Arguments in DISTINCT keyword of SQL

While writing a SQL query with the DISTINCT keyword, we use various parameters and arguments which are as follows:

• Column Name: We enter the name of the column whose unique data we need to fetch.
• Conditional: We input the condition on whose satisfaction we need the output of unique values.
• Table Name: We enter the name of the table from which we need to fetch the unique records
• WHERE clause: We use where clause to get data satisfying specific conditions within the records.

Type 1: Finding a unique value in a column

Let us find out all the unique posts available in DataFlair by executing a SELECT query with the DISTINCT keyword. The syntax and query are as follows:

Syntax:

SELECT DISTINCT column1 FROM table_name;

Query: 

SELECT DISTINCT post_emp  FROM DataFlair_Employee ;

Here we can see all the available Employee posts available in DataFlair.

Type 2: Use the DISTINCT keyword to count the unique entries in a column

Let us count the unique names of employees working in DataFlair. We can do this by executing a Select query and passing the column name whose count of unique elements we need to find. The Syntax and query are as follows:

Syntax: 

SELECT COUNT(DISTINCT columnName1) FROM tableName;

Query: 

SELECT COUNT(DISTINCT name_emp) FROM DataFlair_Employee;

We can see the count of unique names in our database after executing the above-stated query.

Type 3: Counting all the unique entries of data in the database

Let us count the total number of positions on which the employees in DataFlair are working. To obtain the count, we need to execute the Count keyword on the Select query with a Distinct keyword.

Syntax: 

SELECT Count(DISTINCT columnName) FROM tableName ;

Query:

SELECT Count(DISTINCT post_emp) FROM dataflair_employee ;

Here, we can see the count of all unique Employee posts in DataFlair.

Type 4: Handling NULL values by the DISTINCT keyword

Let us first insert some NULL values in our database, and view our database again.

Syntax: 

SELECT * FROM table_name;

Query: 

SELECT * FROM DataFlair_Employee ;

 

We can see we have two columns in which we have a null value, now let us run them individually
Select Distinct query on both the columns.

Example 1

Syntax: 

SELECT DISTINCT column1 FROM table_name;

Query: 

SELECT DISTINCT post_emp FROM DataFlair_Employee ;

We observe that our null value is treated as a different value by the SQL query.

Example 2

Syntax:

SELECT DISTINCT column1 FROM table_name;

Query:

SELECT DISTINCT age FROM DataFlair_Employee ;

A similar case can be observed in the second example as well.

Summary

We have discussed what the DISTINCT keyword is and where do we use it. We have also studied the syntax and example of the DISTINCT keyword in various case scenarios.

DISTINCT keyword allows us to fetch the unique data and also maintain its count using the COUNT keyword used in SQL queries.

We can use this to preprocess data or to get a peek into the data and understand the significant data points as we are considering only the unique values.