LEFT OUTER JOIN tableName ON joined columns

 

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>

Related examples in the same category

1.	left outer join between employee table and department table
2.	Left outer join demo
3.	A left outer join:the outer join operator appears on the right of the equality operator
4.	LEFT OUTER JOIN vs RIGHT OUTER JOIN
5.	Left join with plus sign

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

Related examples in the same category