Distinct Keyword in SQL
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.
Let’s have a look at the syntax of the distinct keyword-
SELECT DISTINCT col1, col2, col3, …colN
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”-
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-
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-
SELECT DISTINCT Marks
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)
Let’s see an example using it on the table given above-
SELECT COUNT(DISTINCT Marks)
Frequently Asked Questions
- 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,...
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
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.
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.
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.