NOT IN and subquery : NOT IN « Query Select « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Query Select
3. NOT IN

SQL> CREATE TABLE emps (
  2    emp varchar(30)
  3   ,title    varchar(30)
  4  );

Table created.

SQL>
SQL> INSERT INTO emps VALUES ('Tom','Programmer');

1 row created.

SQL> INSERT INTO emps VALUES ('Jack','Tester');

1 row created.

SQL> INSERT INTO emps VALUES ('Mary','Technician');

1 row created.

SQL>
SQL> CREATE TABLE JobLevel (
  2    title     varchar(30)
  3   ,rank    varchar(30)
  4  );

Table created.

SQL>
SQL> INSERT INTO JobLevel VALUES ('Programmer','Level1');

1 row created.

SQL> INSERT INTO JobLevel VALUES ('Tester','Level2');

1 row created.

SQL> INSERT INTO JobLevel VALUES ('Technician','Level3');

1 row created.

SQL>
SQL> CREATE TABLE salary (
  2    rank     varchar(30)
  3   ,payment  DECIMAL(10,2)
  4  );

Table created.

SQL>
SQL> INSERT INTO salary VALUES ('Level1',2000.00);

1 row created.

SQL> INSERT INTO salary VALUES ('Level2',3000.00);

1 row created.

SQL> INSERT INTO salary VALUES ('Level3',5000.00);

1 row created.

SQL> INSERT INTO salary VALUES ('Level4',6000.00);

1 row created.

SQL>
SQL> select * from emps;



EMP
------------------------------
TITLE
------------------------------
Tom
Programmer

Jack
Tester

Mary
Technician

3 rows selected.

SQL> select * from JobLevel;



TITLE
------------------------------
RANK
------------------------------
Programmer
Level1

Tester
Level2

Technician
Level3


3 rows selected.

SQL> select * from salary;

RANK                               PAYMENT
------------------------------  ----------
Level1                                2000
Level2                                3000
Level3                                5000
Level4                                6000

4 rows selected.

SQL>
SQL> SELECT salary.rank FROM salary
  2  WHERE rank NOT IN (SELECT rank FROM JobLevel);

RANK
------------------------------
Level4

1 row selected.

SQL>
SQL> DROP TABLE emps;

Table dropped.

SQL> DROP TABLE JobLevel;

Table dropped.

SQL> DROP TABLE salary;

Table dropped.

SQL>
SQL>