some “JOIN” test

1.prepare the data material
SQL> desc part
Name                                      Null?                     Type
—————————————– ——– —————–
PART_ID                                   NOT NULL    VARCHAR2(4)
SUPPLIER_ID                                                   VARCHAR2(4)

SQL> select * from part;
PART SUPP
—- —-
P1   S1
P2   S2
P3
P4

SQL> desc supplier
Name                                      Null?    Type
—————————————– ——– —————–
SUPPLIER_ID                               NOT NULL VARCHAR2(4)
SUPPLIER_NAME                             NOT NULL VARCHAR2(20)

SQL> select * from supplier;
SUPP SUPPLIER_NAME
—- ——————–
S1   Supplier#1
S2   Supplier#2
S3   Supplier#3

P3,P4对应没有数据,执行如下的sql

SQL>select p.part_id,s.supplier_name from part p,supplier s where p.supplier_id=s.supplier_id

PART            SUPPLIER_NAME
—-                  ——————–
p1                    Supplier#1
p2                   Supplier#2

//it’s easy to understand the inner join,get the rows which values exsit in both of the table

//The join shown above is an inner join, which results in just the rows that have corresponding rows in both tables

//of course,the sql could be written as below(inner join)
SQL> select p.part_id,s.supplier_name from part p join supplier s on p.supplier_id=s.supplier_id;

PART SUPPLIER_NAME
—- ——————–
p1   Supplier#1
p2   Supplier#2

//there will be the same result no matter which object was based on as join object
SQL> select p.part_id,s.supplier_name from supplier s join part p on p.supplier_id=s.supplier_id;

PART          SUPPLIER_NAME
—-            ——————–
p1                Supplier#1
p2               Supplier#2

2.outer join
//If we want all parts to be listed in the result set, irrespective of whether they are supplied by any supplier or not,
then we need to perform an outer join

//left outer join, based on table in left side,if the column of right side don’t have value,take the left side as bench mark

and appeared as null
SQL>select p.part_id,s.supplier_name from part p,supplier s where p.supplier_id=s.supplier_id(+)

PART          SUPPLIER_NAME
—-                ——————–
p1                Supplier#1
p2               Supplier#2
p4
p3

//The outer join above lists all of the parts. For the parts that don’t have a corresponding supplier,
null values are displayed for the SUPPLIER_NAME column. However, not all the suppliers are displayed.
Since supplier S3 doesn’t supply any parts, it gets excluded from the result set of the above outer join

//If we want all the suppliers listed in the result set,
irrespective of whether they supply any part or not, we need to perform an outer join like the following

SQL>select p.part_id,s.supplier_name from part p,supplier s where p.supplier_id(+)=s.supplier_id

PART    SUPPLIER_NAME
—-         ——————–
p1            Supplier#1
p2            Supplier#2
                 Supplier#3

 

-EOF-