DISTINCT in DBMS

DISTINCT in DBMS

 

In this article, we will learn about 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
distinct

DISTINCT Syntax

SELECT DISTINCT column1, column2,
FROM table name;

Consider a sample table EMP

EMPNO

ENAMEJOBMGRHIREDATESALCOMMDEPTNO

7839

KINGPRESIDENT17-NOV-81500010

7698

BLAKEMANAGER783901-MAY-81285030

7782

CLARKMANAGER783909-JUN-812450

10

7566JONESMANAGER783902-APR-812975

20

7788SCOTTANALYST756619-APR-873000

20

7902FORDANALYST756603-DEC-813000

20

7369SMITHCLERK790217-DEC-80800

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.
JOBSAL
MANAGER2850
CLERK800
MANAGER2975
PRESIDENT5000
ANALYST3000
MANAGER2450

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
ENAMEJOB
FORDANALYST
CLARKMANAGER
JONESMANAGER
SMITHCLERK
KINGPRESIDENT
SCOTTANALYST
BLAKEMANAGER

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