Mega Code Archive
LEFT OUTER JOIN tableName ON joined columns
<![CDATA[
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 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> DROP TABLE emps;
Table dropped.
SQL> DROP TABLE JobLevel;
Table dropped.
SQL> DROP TABLE salary;
Table dropped.
SQL>
]]>