Oracle SQL - Subquery Using the IN Operator

  1. HOME
  2. Oracle SQL
  3. Subquery
  4. Subquery Using the IN Operator

Introduction

The subquery in the following code displays all 13 registrations we have for course category 'BLD'. 

select r.attendee, r.course, r.begindate
from   registrations r
where  r.course in (select c.code
                   from   courses c
                   where  c.category='BLD');

Demo

SQL>
SQL>--  w ww.ja v  a  2  s  .c  om
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> drop table courses;

Table dropped.

SQL> create table courses(
  2  code        VARCHAR2(6)  primary key,
  3  description VARCHAR2(30) not null,
  4  category    CHAR(3)      not null,
  5  duration    NUMBER(2)    not null) ;
SQL> insert into courses values('SQL','Introduction to SQL',         'GEN',4);
SQL> insert into courses values('JSON','Oracle for application users','GEN',1);
SQL> insert into courses values('JAVA','Java for Oracle developers',  'BLD',4);
SQL> insert into courses values('PLS','Introduction to PL/SQL',      'BLD',1);
SQL> insert into courses values('XML','XML for Oracle developers',   'BLD',2);
SQL> insert into courses values('ERM','Data modeling with ERM',      'DSG',3);
SQL> insert into courses values('PMT','Process modeling techniques', 'DSG',1);
SQL> insert into courses values('RSD','Relational system design',    'DEF',2);
SQL> insert into courses values('PRO','Prototyping',                 'DSG',5);
SQL> insert into courses values('GEN','System generation',           'DSG',4);
SQL>
SQL>
SQL> select r.attendee, r.course, r.begindate
  2  from   registrations r
  3  where  r.course in (select c.code
  4                     from   courses c
  5                     where  c.category='BLD');

 ATTENDEE | COURSE | BEGINDATE
--------- | ------ | ----------
 07002.00 | JAVA   | 13-12-1999
 07007.00 | JAVA   | 13-12-1999
 07011.00 | JAVA   | 13-12-1999
 07008.00 | JAVA   | 13-12-1999
 07009.00 | JAVA   | 13-12-1999
 07004.00 | JAVA   | 01-02-2000
 07008.00 | JAVA   | 01-02-2000
 07006.00 | JAVA   | 01-02-2000
 07012.00 | XML    | 03-02-2000
 07002.00 | XML    | 03-02-2000
 07004.00 | PLS    | 11-09-2000

 ATTENDEE | COURSE | BEGINDATE
--------- | ------ | ----------
 07002.00 | PLS    | 11-09-2000
 07011.00 | PLS    | 11-09-2000

13 rows selected.

SQL>

Quiz