EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial Distinct Keyword in SQL
 

Distinct Keyword in SQL

Priya Pedamkar
Article byPriya Pedamkar

Updated March 21, 2023

Distinct Keyword in SQL

 

 

Introduction to Distinct Keyword in SQL

Distinct command in SQL is used along with the Select command in order to retrieve only distinct or dissimilar values from a table. It is mainly used as a method to filter duplicate records and fetch only unique records from a given table, where there is a possibility of fields having multiple duplicate records. The Distinct keyword in SQL is used in the Select statement as ‘Select Distinct * from table_name’.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax with Explanation

Let’s look at the distinct keyword syntax with an example:

Let’s have an employee table with three columns: empId, empname, and city, as shown below:

employee table

Select DISTINCT(column_name) from table_name;

In our example above, we can see that the city is the column that has repetitive values, so we will put the city in place of column_name and employee in place of table_name. On running, it will return the unique city names, which are Mumbai, Delhi, Lucknow. If we remove a distinct keyword, it will retrieve four values instead of three.

Parameters used for Distinct Keyword in SQL

Let’s now look at the various parameters present in a distinct keyword. Below is the syntax for a distinct keyword.

Syntax:

Select DISTINCT(expressions) from tables [where conditions];
  • Expressions: In this, we provide the column names or calculations that we want.
  • Tables: We provide the table names from which we want the records. One thing to note is that there should be at least one table name after from clause.
  • Conditions: This is purely optional; we provide conditions when we want the data to first satisfy a particular condition for the records to get selected.
Note: In SQL, a distinct keyword also retrieves the null value, which means it considers null also as a distinct value. So if we have null values, then the result set for a distinct keyword will also include a null value.

How to Use Distinct Keyword in SQL?

As we have already discussed the parameters. Let’s now learn where to use distinct keyword with the help of examples.

Let’s create a table CUSTOMER using DDL statements(data definition language) and then populate them using DML( Data manipulation language).

DDL (creating table):

CREATE TABLE customer ( customer_id int NOT NULL, name char(50) NOT NULL, city varchar2, state varchar2);

This will create a table having four columns customer_id, name, city, and state. Now we will use DML statements to enter data in the table.

Insert Statements to enter data:

INSERT INTO customer (customer_id, name, city, state) VALUES (25, 'Suresh','Jamshedpur','Jharkhand');
INSERT INTO customer (customer_id, name, city, state) VALUES (27, 'Ramesh', 'Jamshedpur','Jharkhand');
INSERT INTO customer (customer_id, name, city, state) VALUES (30, 'Ravi', 'Karnool', 'Andhra Pradesh');
INSERT INTO customer (customer_id, name, city, state) VALUES (31, 'Neha', 'Delhi', 'Delhi');
INSERT INTO customer (customer_id, name, city, state) VALUES (32, 'Sivan', 'Kolkata', 'West Bengal');
INSERT INTO customer (customer_id, name, city, state) VALUES (35, 'Niraj', 'Mumbai', 'Maharashtra');

On executing the above statements, we get the below customer table.

customer table

Now let’s execute some queries using distinct queries to learn how to use distinct keyword.

1. First, we will find unique values in a column.

Query:

select DISTINCT state from customer order by state;

We will get 5 values on executing the query as we have only five distinct states as Jharkhand is repeated twice. Since we have used ORDER BY so the result set will be sorted in ascending order. Below is the result set we should get on executing the query.

Order By

2. Secondly, we will unique values from multiple columns.

Query:

select DISTINCT city, state from customer order by city, state;

The above query will return each unique city and state combination. In the above case, distinct applies to each field which is written after the distinct keyword. So we will have five pairs of city and state as there Jamshedpur city, which has been repeated twice. So we will have Jamshedpur along with Jharkhand once. The city will be ordered in ascending order. The result set on executing the query is shown below.

Distinct Keyword in SQL - Order By

3. We will now see how the distinct keyword handles null values.

First of all, we will update a field in the state column as NULL and then use a distinct keyword to obtain the result set.

The update query to set NULL value in one of the fields of the customer table.

Query:

update customer set state=”” where customer_id = 35;

This will insert a NULL value in the last field of the state column. The table will be updated as below.

Distinct Keyword in SQL - customer table

Now, let’s execute a distinct keyword using a select query.

Query:

select DISTINCT state from customer order by state;

On executing the above query, we will set five values as the distinct keyword considers NULL also a unique value. Jharkhand being repeated twice will only have one value in the result set. Since we have used the ORDER BY clause so the result set will be sorted in ascending order. Below is the result set we should see on executing the above query.

Distinct Keyword in SQL - Order By

Conclusion

To conclude this article, we can say that a distinct keyword is a very powerful and useful keyword that is used in SELECT statements based on different conditions depending on business requirements to retrieve UNIQUE/DISTINCT values from a column or columns.

Recommended Articles

We hope that this EDUCBA information on “Distinct Keyword in SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Foreign Key in SQL
  2. Transactions in SQL
  3. Wildcard in SQL
  4. this Keyword in Java

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW