DISTINCT in DBMS

January 31, 2023

DISTINCT

On this page we will discuss about GROUP BY in DBMS. Group by clause in SQL used to arrange logically related data into groups with help of some functions. It is an important concept of DBMS.So, lets just discuss it in detail.

distinct in dbms

DISTINCT IN DBMS

  • Inside table columns may contain many duplicate values and sometimes we require to list only unique values, this is done by using DISTINCT clause along with a select statement
  • DISTINCT statement is used to return only unique values present in a column or combination of columns
  • The DISTINCT clause is only for display purpose and will not affect the original database table
DBMS desktop gif
DBMS mobile adv gif

What is Database

Participation Constraint

DBMS Architecture

Data Models

Cardinality

DISTINCT Syntax

SELECT DISTINCT column1, column2,
FROM table name;
Consider a sample table EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7839

 KING PRESIDENT – 17-NOV-81 5000 – 10

7698

 BLAKE MANAGER 7839 01-MAY-81 2850 – 30

7782

 CLARK MANAGER 7839 09-JUN-81 2450 –

10
7566 JONES MANAGER 7839 02-APR-81 2975 –

20
7788 SCOTT ANALYST 7566 19-APR-87 3000 –

20
7902 FORD ANALYST 7566 03-DEC-81 3000 –

20
7369 SMITH CLERK 7902 17-DEC-80 800 –

20

DISTINCT on a single column 

select DISTINCT job from emp;
Output 
4 rows selected.
JOB
ANALYST
CLERK
MANAGER
PRESIDENT
The above query Returns unique values present in the job column i.e.  even if a value is appeared for more than once but it is displayed only for one time.

DISTINCT on more than one column 

Whenever DISTINCT is applied on the select statement which contains more than one column then the combination of values of all the columns is considered as a single value even if any of the column value is different in that row it is considered as a DISTINCT value

Example 

select DISTINCT job,sal
from emp;
o/p 
6 rows selected.
JOB SAL
MANAGER 2850
CLERK 800
MANAGER 2975
PRESIDENT 5000
ANALYST 3000
MANAGER 2450
The above query Returns 6 values because each value is considered as a key combination of two values and [ANALYST, 3000] combination would appear twice and hence it is eliminated during display.

Another example for DISTINCT 

select DISTINCT ename, job
from emp;
Output 
6 rows selected
ENAME JOB
FORD ANALYST
CLARK MANAGER
JONES MANAGER
SMITH CLERK
KING PRESIDENT
SCOTT ANALYST
BLAKE MANAGER
The above query results in all the seven rows in the table because even if job column contains duplicate values name column contain only unique values hence the key combination both ename and job is considered as one entity and all values become unique

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get Prime
DBMS Tutorial banner

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Get Prime
Course List
Course List

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout
Checkout