Oracle SQL - Select DISTINCT Keyword

Introduction

Your query results contain duplicate rows.

You can eliminate such rows by adding the keyword DISTINCT immediately after the keyword SELECT.

select DISTINCT job, deptno 
from   emp; 
  

Without the addition of the DISTINCT keyword, this query would produce 14 rows, because the EMPLOYEES table contains 14 rows.

Demo

SQL>
SQL>--  ww w  . j  a  v a2s  .c  om
SQL> drop table emp;

Table dropped.

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL> --without DISTINCT
SQL> select job, deptno
  2  from   emp;

JOB      |    DEPTNO
-------- | ---------
TRAINER  |  00020.00
SALESREP |  00030.00
SALESREP |  00010.00
MANAGER  |  00020.00
SALESREP |  00030.00
MANAGER  |  00010.00
MANAGER  |  00010.00
TRAINER  |  00020.00
DIRECTOR |  00010.00
SALESREP |  00030.00
TRAINER  |  00020.00

JOB      |    DEPTNO
-------- | ---------
ADMIN    |  00030.00
TRAINER  |  00020.00
ADMIN    |  00010.00

14 rows selected.

SQL>
SQL>
SQL> select DISTINCT job, deptno
  2  from   emp;

JOB      |    DEPTNO
-------- | ---------
MANAGER  |  00020.00
ADMIN    |  00030.00
SALESREP |  00030.00
ADMIN    |  00010.00
TRAINER  |  00020.00
SALESREP |  00010.00
MANAGER  |  00010.00
DIRECTOR |  00010.00

8 rows selected.

SQL>

• Previous
• Next