Oracle SQL - Correlated Subquery with EXISTS Operator

  1. HOME
  2. Oracle SQL
  3. Subquery
  4. Correlated Subqueries

Introduction

Correlated subqueries often occur in combination with the EXISTS operator.

The following query returns all course offerings without registrations. 

select o.*
from   offerings o
where  not exists
      (select r.*
       from   registrations r
       where  r.course    = o.course
       and    r.begindate = o.begindate);

The EXISTS operator is not interested in the actual rows resulting from the subquery.

This operator checks for only the existence of subquery results.

If the subquery returns at least one resulting row, the EXISTS operator evaluates to TRUE.

If the subquery returns no rows at all, the result is FALSE.

Demo

SQL>
SQL>-- from  w  w  w. j a  va 2 s  .c  o  m
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> drop table registrations;

Table dropped.

SQL> create table registrations(
  2  attendee    NUMBER(4)   not null,
  3  course    VARCHAR2(6) ,
  4  begindate   DATE      not null,
  5  evaluation  NUMBER(1)   check (evaluation in (1,2,3,4,5))) ;
SQL>
SQL> insert into registrations values (7002,'SQL',date '1999-04-12',4   );
SQL> insert into registrations values (7934,'SQL',date '1999-04-12',5   );
SQL> insert into registrations values (7006,'SQL',date '1999-04-12',4   );
SQL> insert into registrations values (7011,'SQL',date '1999-04-12',2   );
SQL> insert into registrations values (7008,'SQL',date '1999-10-04',NULL);
SQL> insert into registrations values (7009,'SQL',date '1999-10-04',3   );
SQL> insert into registrations values (7902,'SQL',date '1999-10-04',4   );
SQL> insert into registrations values (7902,'SQL',date '1999-12-13',NULL);
SQL> insert into registrations values (7006,'SQL',date '1999-12-13',NULL);
SQL> insert into registrations values (7003,'JSON',date '1999-08-10',4   );
SQL> insert into registrations values (7012,'JSON',date '1999-08-10',4   );
SQL> insert into registrations values (7902,'JSON',date '1999-08-10',5   );
SQL> insert into registrations values (7010,'JSON',date '2000-09-27',5   );
SQL> insert into registrations values (7002,'JAVA',date '1999-12-13',2   );
SQL> insert into registrations values (7007,'JAVA',date '1999-12-13',NULL   );
SQL> insert into registrations values (7011,'JAVA',date '1999-12-13',5   );
SQL> insert into registrations values (7008,'JAVA',date '1999-12-13',5   );
SQL> insert into registrations values (7009,'JAVA',date '1999-12-13',4   );
SQL> insert into registrations values (7004,'JAVA',date '2000-02-01',3   );
SQL> insert into registrations values (7008,'JAVA',date '2000-02-01',4   );
SQL> insert into registrations values (7006,'JAVA',date '2000-02-01',5   );
SQL> insert into registrations values (7012,'XML',date '2000-02-03',4   );
SQL> insert into registrations values (7002,'XML',date '2000-02-03',5   );
SQL> insert into registrations values (7004,'PLS',date '2000-09-11',NULL);
SQL> insert into registrations values (7002,'PLS',date '2000-09-11',NULL);
SQL> insert into registrations values (7011,'PLS',date '2000-09-11',NULL);
SQL>
SQL>
SQL> select o.*
  2  from   offerings o
  3  where  not exists
  4        (select r.*
  5         from   registrations r
  6         where  r.course    = o.course
  7         and    r.begindate = o.begindate);

COURSE | BEGINDATE  |   TRAINER | LOCATION
------ | ---------- | --------- | --------
XML    | 18-09-2000 |  [N/A]    | SEATTLE
PRO    | 19-02-2001 |  [N/A]    | DALLAS
ERM    | 15-01-2001 |  [N/A]    |  [N/A]
RSD    | 24-02-2001 |  07008.00 | CHICAGO

SQL>

Related Topic