Convert subqueries to JOINs : Introduction « Table Joins « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Table Joins
3. Introduction

SQL>
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 payment FROM salary WHERE rank =
  2    (SELECT rank FROM JobLevel WHERE title =
  3      (SELECT title FROM emps WHERE emp = 'Jack'));



   PAYMENT
----------
      3000

1 row selected.

SQL>
SQL>
SQL>
SQL> DROP TABLE emps;

Table dropped.

SQL> DROP TABLE JobLevel;

Table dropped.

SQL> DROP TABLE salary;

Table dropped.

SQL>