日期:2014-05-17 浏览次数:20727 次
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 SQL> select p.part_id, s.supplier_name 2 from part p, supplier s 3 where p.supplier_id = s.supplier_id (+); PART SUPPLIER_NAME ---- -------------------- P1 Supplier#1 P2 Supplier#2 P3 P4 --(+)是单向的 SQL> select p.part_id, s.supplier_name 2 from part p, supplier s 3 where p.supplier_id (+) = s.supplier_id (+); where p.supplier_id (+) = s.supplier_id (+) * ERROR at line 3: ORA-01468: a predicate may reference only one outer-joined table --实现Full Join的方法 SQL> select p.part_id, s.supplier_name 2 from part p, supplier s 3 where p.supplier_id = s.supplier_id (+) 4 union 5 select p.part_id, s.supplier_name 6 from part p, supplier s 7 where p.supplier_id (+) = s.supplier_id; PART SUPPLIER_NAME ---- -------------------- P1 Supplier#1 P2 Supplier#2 P3 P4 Supplier#3 --现在的语法 SQL> select p.part_id, s.supplier_name 2 from part p full outer join supplier s 3 on p.supplier_id = s.supplier_id; PART SUPPLIER_NAME ---- -------------------- P1 Supplier#1 P2 Supplier#2 P4 P3 Supplier#3