LEFT OUTER JOIN tableName ON joined columns : Outer Joins Left Right « Table Joins « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Table Joins
3. Outer Joins Left Right

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>
SQL> SELECT salary.rank
  2  FROM salary LEFT OUTER JOIN JobLevel ON (salary.rank = JobLevel.rank)
  3  WHERE JobLevel.rank IS NULL;



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


1 row selected.

SQL>
SQL>
SQL> DROP TABLE emps;

Table dropped.

SQL> DROP TABLE JobLevel;

Table dropped.

SQL> DROP TABLE salary;

Table dropped.

SQL>
SQL>