AppDividend
Latest Code Tutorials

SQL Distinct Keyword Tutorial | Distinct Statement in SQL Example

0

SQL Distinct Keyword Tutorial | Distinct Statement in SQL Example is today’s topic. The SQL distinct keyword 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. 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 of retrieving duplicate records.

SQL Distinct Keyword Tutorial

The 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 which 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 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_CODE CUST_NAME CITY GRADE AMOUNT
101 SHUBH KOLKATA 1 10000
201 SOURAV KOLKATA 1 10000
301 KISHAN PATNA 2 20000
401 DILEEP ALLAHABAD 2 30000
501 SHRUTI PATNA 3 40000
601 SHOUVIK JALANDHAR 4 20000
701 ROHIT PATNA 2 40000

 

#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_CODE CUST_NAME CITY GRADE AMOUNT
101 SHUBH KOLKATA 1 10000
201 SOURAV KOLKATA 1 10000
301 KISHAN PATNA 2 20000
401 DILEEP ALLAHABAD 2 30000
501 SHRUTI PATNA 3 40000
601 SHOUVIK JALANDHAR 4 20000
701 ROHIT PATNA 2 40000

 

#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 city and amount columns are displayed. You can see from the above output that city Patna and Allahabad was listed at once whose amount was the same.

Example: (With NULL VALUES)

Consider Table: (CUSTOMERS)

CUST_CODE CUST_NAME CITY GRADE AMOUNT
101 SHUBH KOLKATA 1 10000
201 SOURAV KOLKATA 1 10000
301 KISHAN PATNA 2 20000
401 DILEEP ALLAHABAD 2 30000
501 SHRUTI PATNA 3 40000
601 SHOUVIK JALANDHAR 4 20000
701 ROHIT PATNA 2 40000
801 ROUNAK NULL 5 10000

 

#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_CODE CUST_NAME CITY GRADE AMOUNT
101 SHUBH KOLKATA 1 10000
201 SOURAV KOLKATA 1 10000
301 KISHAN PATNA 2 20000
401 DILEEP ALLAHABAD 2 30000
501 SHRUTI PATNA 3 40000
601 SHOUVIK JALANDHAR 4 20000
701 ROHIT PATNA 2 40000
801 ROUNAK NULL 5 10000

 

#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_CODE CUST_NAME CITY GRADE AMOUNT
101 SHUBH KOLKATA 1 10000
201 SOURAV KOLKATA 1 10000
301 KISHAN PATNA 2 20000
401 DILEEP ALLAHABAD 2 30000
501 SHRUTI PATNA 3 40000
601 SHOUVIK JALANDHAR 4 20000
701 ROHIT PATNA 2 40000
801 ROUNAK NULL 5 10000

 

#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 Keyword Tutorial | Distinct Statement in SQL 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.