Oracle SQL - MINUS Set Operator Example

  1. HOME
  2. Oracle SQL
  3. Select
  4. Set Operators

Introduction

The following script tells which locations host course offerings without having a department? 

select o.location from offerings o
MINUS
select d.location from departments d;

Alternative Solution Without Using the MINUS Operator 

select DISTINCT o.location
from   offerings o
where  o.location not in
      (select d.location
       from   departments d)

MINUS operator automatically removes duplicate rows.

Demo

SQL>
SQL>-- from   w  ww. j a v  a 2  s.  c  o m
SQL> drop table departments;

Table dropped.

SQL>
SQL> create table departments(
  2  deptno    NUMBER(2)     primary key,
  3  dname     VARCHAR2(10)  not null unique check (dname = upper(dname)),
  4  location  VARCHAR2(8)   not null        check (location = upper(location)),
  5  mgr       NUMBER(4)) ;
SQL>
SQL> insert into departments values (10,'ACCOUNTING','NEW YORK',7007);
SQL> insert into departments values (20,'TRAINING',  'DALLAS',  7004);
SQL> insert into departments values (30,'SALES',     'CHICAGO', 7006);
SQL> insert into departments values (40,'HR',        'BOSTON',  7009);
SQL>
SQL> drop table offerings;

Table dropped.

SQL> create table offerings(
  2  course     VARCHAR2(6)  not null,
  3  begindate  DATE         not null,
  4  trainer    NUMBER(4)            ,
  5  location   VARCHAR2(8)) ;
SQL>
SQL> insert into offerings values ('SQL',date '1999-04-12',7902,'DALLAS' );
SQL> insert into offerings values ('JSON',date '1999-08-10',7004,'CHICAGO');
SQL> insert into offerings values ('SQL',date '1999-10-04',7001,'SEATTLE');
SQL> insert into offerings values ('SQL',date '1999-12-13',7001,'DALLAS' );
SQL> insert into offerings values ('JAVA',date '1999-12-13',7004,'SEATTLE');
SQL> insert into offerings values ('XML',date '2000-02-03',7001,'DALLAS' );
SQL> insert into offerings values ('JAVA',date '2000-02-01',7011,'DALLAS' );
SQL> insert into offerings values ('PLS',date '2000-09-11',7008,'DALLAS' );
SQL> insert into offerings values ('XML',date '2000-09-18',NULL,'SEATTLE');
SQL> insert into offerings values ('JSON',date '2000-09-27',7902,'DALLAS' );
SQL> insert into offerings values ('ERM',date '2001-01-15',NULL, NULL    );
SQL> insert into offerings values ('PRO',date '2001-02-19',NULL,'DALLAS' );
SQL> insert into offerings values ('RSD',date '2001-02-24',7008,'CHICAGO');
SQL>
SQL>
SQL> select o.location from offerings o
  2  MINUS
  3  select d.location from departments d;

LOCATION
--------
SEATTLE
 [N/A]

SQL>
SQL>
SQL>
SQL> select DISTINCT o.location
  2  from   offerings o
  3  where  o.location not in
  4        (select d.location
  5         from   departments d);

LOCATION
--------
SEATTLE

SQL>
SQL>
SQL>

Related Topic