Distinct Keyword in SQL

Introduction

While handling the databases, sometimes we might encounter data with various duplicate records, which might cause discrepancies like excess memory usage. While carrying various operations, we may want to eliminate these entries and fetch only the unique records. But to do this by checking all the entries and then removing the duplicate entries one by one will be very inefficient, especially if there are many duplicate entries. 

So to efficiently fetch only the unique records from the database, we use the DISTINCT keyword, which is used along with the SELECT keyword, which is like a command to the database to select columns from the table but unique entries.

Syntax

Let’s have a look at the syntax of the distinct keyword-

SELECT DISTINCT col1, col2, col3, …colN

FROM table_name

WHERE [condition];

Example

Let's see an example of using the DISTINCT keyword to fetch unique records from a database. 

Consider We have the following table named “Students”-

Roll No.

Name 

Age

Marks

UID

1

Vivek Dixit

21

89

9845678

2

Apoorv Singh

20

91

3148965

3

Ayush Singh

21

88

7995165

4

Prashant Yadav

19

91

3164646

5

Gorakh Nath

20

91

3154668

Using only SELECT keyword

Now, we want to fetch the marks obtained by all the students. To do this, we will use the SELECT keyword, which will show the marks-

Query-

SELECT Marks

FROM Students;

Output-

Marks

89

91

88

91

91

Since we have few students who had equal marks, duplicate entries are in the result set.  

Using the DISTINCT keyword

Now let's use the DISTINCT keyword along with the select keyword, then check the output-

Query-

SELECT DISTINCT Marks

FROM Students;

Output-

Marks

89

91

88

We get all the unique marks obtained by the students. And all the duplicate entries have been removed from the result set.

Using the COUNT keyword

There is one more scenario when we might look for the total number of unique entries in a column rather than the unique ones themselves. In that case, we can use the count keyword. It returns the total number of entries in the result set obtained using the distinct and select keywords.

The syntax for this is as follows-

SELECT COUNT(DISTINCT col1, col2, col3, …colN)

FROM table_name

WHERE [condition];

Let’s see an example using it on the table given above-

QUERY-

SELECT COUNT(DISTINCT Marks)

FROM Students;

OUTPUT-

COUNT(DISTINCT Marks)
3

Frequently Asked Questions

  1. Why is the SELECT keyword used in SQL?
    The SELECT keyword is used for selecting data from a database. It returns a result set which is a table containing the selected data.
    The syntax for the SELECT statement is as follows-

SELECT col1, col2, col3,...

FROM name_of_table;

Also, we can use an asterisk(*) to select all the columns at once.

Then the command will be similar to the one given below-

SELECT * FROM name_of_table;

2. Why do we use the DISTINCT keyword in SQL queries?

The DISTINCT keyword is used to eliminate duplicate entries from the result set. It only returns the unique entries in it. It is used along with the SELECT keyword, which selects the column to be fetched from the table.

The syntax to use the DISTINCT keyword is as follows-

SELECT DISTINCT col1, col2, col3, …colN

FROM table_name

WHERE [condition];

3. What are SQL commands, and what are the types of SQL commands? Also, give some examples of each?

SQL commands are instructions that are used to communicate with the database. They allow us to instruct the database to carry out various tasks.

They are of 5 types- 

DDL- Data definition language is a set of commands used to change the table's structure. For example- CREATE, ALTER or DROP commands fall in this category.

DML- Data manipulation languages are a set of commands used to manipulate or modify the database. For example, INSERT, UPDATE, or DELETE commands fall in this category.

DCL- Data Control languages are the commands used to manage the authority of the database users. For example, grant and revoke commands fall under this category. 

TCL- Transaction control language commands are used along with the DML commands. For example, we have commands like COMMIT and ROLLBACK in this category.

DQL- Data query language is used for fetching the data from the database. SELECT is an example of DQL.

Key Takeaways

In this blog, we learned about the DISTINCT keyword's syntax and then saw examples. It is used along with the SELECT keyword to eliminate duplicate records from the result and return only the unique entries in the column. 

You can visit here to learn more about the various topics of DBMS and to take your interview preparation to higher levels. You can practice various problems on Codestudio by Coding ninjas.

Also, you can check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Was this article helpful ?
1 upvote

Comments

No comments yet

Be the first to share what you think