Mastering Oracle SQL

Outer Joins

Sometimes while performing a join between two tables, you need to return all the rows from one table even when there are no corresponding rows in the other table. Consider the following two tables, SUPPLIER and PART:

            SELECT * FROM SUPPLIER;

SUPPLIER_ID NAME
----------- ------------------------------
        101 Pacific Disks, Inc.
        102 Silicon Valley MicroChips
        103 Blue River Electronics

SELECT * FROM PART;

PART_NBR NAME               SUPPLIER_ID STATUS INVENTORY_QTY UNIT_COST RESUPPLY_DATE
-------- ------------------ ----------- ------ ------------- --------- -------------
HD211    20 GB Hard Disk            101 ACTIVE             5      2000 12-DEC-00
P3000    3000 MHz Processor         102 ACTIVE            12       600 03-NOV-00

If you want to list all the suppliers and all the parts supplied by them, it is natural to use the following query:

            SELECT S.SUPPLIER_ID, S.NAME SUPPLIER_NAME, P.PART_NBR, P.NAME PART_NAME
            FROM SUPPLIER S, PART P
            WHERE S.SUPPLIER_ID = P.SUPPLIER_ID;

SUPPLIER_ID SUPPLIER_NAME                  PART_NBR   PART_NAME
----------- ------------------------------ ---------- -------------------
        101 Pacific Disks, Inc.            HD211      20 GB Hard Disk
        102 Silicon Valley MicroChips      P3000      3000 MHz Processor

Note that even though we have three suppliers, this query lists only two of them, because the third supplier (Blue River Electronics) doesn’t currently supply any part. When Oracle performs the join between SUPPLIER table and PART table, it matches the SUPPLIER_ID from these two tables (as specified by the join condition). Since SUPPLIER_ID 103 doesn’t have ...