AppDividend
Latest Code Tutorials

SQL Distinct Keyword Example | Distinct Statement in SQL

0

SQL distinct is an inbuilt statement that is used for removing duplicate records from a column at the time of displaying the resulting sets from any table that is present in a database. If you want to remove duplicate items from the final result set then SQL Distinct is the answer to your problem. It filters out duplicate items and returns unique values from the table.

The distinct keyword is used with the select statement for fetching unique values from a table. There also exists some situation where there are multiple duplicate records. So, it makes more sense in retrieving unique values rather than retrieving duplicate records.

SQL Distinct Example

SQL Select DISTINCT statement is used to return the distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

#Syntax

Select distinct column1, column2, …., column n 
from table_name 
where[condition];

#Parameters

  1. Distinct is a keyword that is used for retrieving unique records.
  2. Columns are the name of the columns.
  3. Table_name is the name of the condition.
  4. Where is the condition which is used for retrieving records based on some condition?

#Note

  1. When only one expression is provided in a distinct clause, the query will generate unique values for only that expression.
  2. When more than one expression is listed using a distinct clause, then the unique combinations for the expressions will be listed.
  3. The distinct clause does not ignore NULL values, so if there is any NULL value in the table, then that NULL value will be displayed in the resulting set representing it as a unique.

Let’s clear this by looking in an example.

Example: (For Single columns)

Consider Table: (CUSTOMERS)

CUST_CODECUST_NAMECITYGRADEAMOUNT
101SHUBHKOLKATA110000
201SOURAVKOLKATA110000
301KISHANPATNA220000
401DILEEPALLAHABAD230000
501SHRUTIPATNA340000
601SHOUVIKJALANDHAR420000
701ROHITPATNA240000

 

#Query: (Without distinct keyword)

select city from customers;

#Output

SQL Distinct Keyword Tutorial

#Explanation

In this, the query has generated all the city names from the customers duplicate without removing the duplicates.

#Query: (Using distinct keyword)

select distinct city from customers;

#Output

Using distinct keyword

#Explanation

In this, the query has generated the city names, which are unique in the customers’ table.

Example: (For multiple Columns)

Consider Table: (CUSTOMERS)

CUST_CODECUST_NAMECITYGRADEAMOUNT
101SHUBHKOLKATA110000
201SOURAVKOLKATA110000
301KISHANPATNA220000
401DILEEPALLAHABAD230000
501SHRUTIPATNA340000
601SHOUVIKJALANDHAR420000
701ROHITPATNA240000

 

#QUERY: (Without Distinct Keyword)

select city, amount from customers order by amount;

#Output

For multiple Columns in SQL

#Explanation

In this query, all city along with the amount is displayed in the resulting set without any removal of duplicate values.

#QUERY: (With Distinct Keyword)

Select distinct city, amount 
from customers order by amount;

#Output

With Distinct Keyword in SQL

#Explanation

In this query, the combinations of unique values from the city and amount columns are displayed. You can see from the above output that city Patna and Allahabad were listed at once whose amount was the same.

Example: (With NULL VALUES)

Consider Table: (CUSTOMERS)

CUST_CODECUST_NAMECITYGRADEAMOUNT
101SHUBHKOLKATA110000
201SOURAVKOLKATA110000
301KISHANPATNA220000
401DILEEPALLAHABAD230000
501SHRUTIPATNA340000
601SHOUVIKJALANDHAR420000
701ROHITPATNA240000
801ROUNAKNULL510000

 

#Query

select distinct city from customers;

#Output

Distinct Statement in SQL Example

#Explanation

NULL values are also displayed in the city name as a distinct clause does not ignore it.

EXAMPLE: (Using Where Condition)

Consider Table: (CUSTOMERS)

CUST_CODECUST_NAMECITYGRADEAMOUNT
101SHUBHKOLKATA110000
201SOURAVKOLKATA110000
301KISHANPATNA220000
401DILEEPALLAHABAD230000
501SHRUTIPATNA340000
601SHOUVIKJALANDHAR420000
701ROHITPATNA240000
801ROUNAKNULL510000

 

#Query

select distinct city, grade from customers where amount=10000;

#Output

 

Using Where Condition

#Explanation

In the above query city and grades are displayed whose amount is equal to 10000. Compare the output table with the original table from there you can see that Kolkata along with the grade 1 and amount with 10000, was displayed once which was present twice in the customer’s table.

EXAMPLE: (Group BY VS Distinct)

Consider Table: (CUSTOMERS)

CUST_CODECUST_NAMECITYGRADEAMOUNT
101SHUBHKOLKATA110000
201SOURAVKOLKATA110000
301KISHANPATNA220000
401DILEEPALLAHABAD230000
501SHRUTIPATNA340000
601SHOUVIKJALANDHAR420000
701ROHITPATNA240000
801ROUNAKNULL510000

 

#QUERY

select city, grade, amount 
from customers 
group by city, grade, amount 
order by city, grade, amount;
AND
select distinct city, grade, amount 
from customers 
order by city, grade, amount;

#OUTPUT

 

Group BY VS Distinct

#Explanation

In the above queries, you can see that the resulting sets of both group by and distinct clause combinations are the same.

Compare the output table from the Original table to spot the difference. You will notice that KOLKATA under the city column whose grade was 1 under the column grade and whose amount was 10000 under the column amount was displayed once.

We have also used order by clause in the above query. DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc.

Finally, SQL Distinct Example is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.