ORACLE外连接小结~

好几次想用到外连接的时候都忘了具体的用法是怎样的，比如说（+）该加在等号的哪一端，或者LFET OUTER JOIN该用在整条语句中的哪个部分。今天正好又碰到一个相关的问题，借此机会总结一下，以后也方便查询，不用每次都去百度了。

view plaincopy to clipboardprint?
//table1和table2为两个测试表 随便插入几条数据  
SQL> select * from table1;  
 
        ID NAME  
---------- --------------------  
         1 wh  
         2 wp  
         3 wq  
 
SQL> select * from table2;  
 
        ID NAME  
---------- --------------------  
         4 wr  
         1 wh  
 
//正常查询  
SQL> select a.name,b.name  
  2  from table1 a,table2 b  
  3  where a.id=b.id;  
 
NAME                 NAME  
-------------------- --------------------  
wh                   wh  
//显示出table1中的所有记录 table2中无相应记录则置NULL  
SQL> select a.name,b.name  
  2  from table1 a,table2 b  
  3  where a.id=b.id(+);  
 
NAME                 NAME  
-------------------- --------------------  
wh                   wh  
wq  
wp  
//显示出table2中的所有记录 table1中无相应记录则置NULL  
SQL> select a.name,b.name  
  2  from table1 a,table2 b  
  3  where a.id(+)=b.id;  
 
NAME                 NAME  
-------------------- --------------------  
wh                   wh  
                     wr  
//呵呵,这里本想尝试一下全外连接,不过使用(+)好像不行  
SQL> select a.name,b.name  
  2  from table1 a,table2 b  
  3  where a.id(+)=b.id(+);  
where a.id(+)=b.id(+)  
             *  
ERROR at line 3:  
ORA-01468: a predicate may reference only one outer-joined table  
 
//左外连接  
SQL> select a.name,b.name  
  2  from table1 a  
  3  left outer join table2 b  
  4  on a.id=b.id;  
 
NAME                 NAME  
-------------------- --------------------  
wh                   wh  
wq  
wp  
//右外连接  
SQL> select a.name,b.name  
  2  from table1 a  
  3  right outer join table2 b  
  4  on a.id=b.id;  
 
NAME                 NAME  
-------------------- --------------------  
wh                   wh  
                     wr  
//右外连接   
SQL> select a.name,b.name  
  2  from table2 b  
  3  right outer join table1 a  
  4  on a.id=b.id;  
 
NAME                 NAME  
-------------------- --------------------  
wh                   wh  
wq  
wp  
//左外连接  
SQL> select a.name,b.name  
  2  from table2 b  
  3  left outer join table1 a  
  4  on a.id=b.id;  
 
NAME                 NAME  
-------------------- --------------------  
wh                   wh  
                     wr  
//试一下FULL OUTER JOIN  
SQL> select a.name,b.name  
  2  from table2 b  
  3  full outer join table1 a  
  4  on a.id=b.id;  
 
NAME                 NAME  
-------------------- --------------------  
wh                   wh  
                     wr  
wq  
wp  
 
SQL> select a.name,b.name  
  2  from table1 a  
  3  full outer join table2 b  
  4  on a.id=b.id;  
 
NAME                 NAME  
-------------------- --------------------  
wh                   wh  
wq  
wp  
                     wr 
//table1和table2为两个测试表 随便插入几条数据
SQL> select * from table1;

        ID NAME
---------- --------------------
         1 wh
         2 wp
         3 wq

SQL> select * from table2;

        ID NAME
---------- --------------------
         4 wr
         1 wh

//正常查询
SQL> select a.name,b.name
  2  from table1 a,table2 b
  3  where a.id=b.id;

NAME                 NAME
-------------------- --------------------
wh                   wh
//显示出table1中的所有记录 table2中无相应记录则置NULL
SQL> select a.name,b.name
  2  from table1 a,table2 b
  3  where a.id=b.id(+);

NAME                 NAME
-------------------- --------------------
wh                   wh
wq
wp
//显示出table2中的所有记录 table1中无相应记录则置NULL
SQL> select a.name,b.name
  2  from table1 a,table2 b
  3  where a.id(+)=b.id;

NAME                 NAME
-------------------- --------------------
wh                   wh
                     wr
//呵呵,这里本想尝试一下全外连接,不过使用(+)好像不行
SQL> select a.name,b.name
  2  from table1 a,table2 b
  3  where a.id(+)=b.id(+);
where a.id(+)=b.id(+)
             *
ERROR at line 3:
ORA-01468: a predicate may reference only one outer-joined table

//左外连接
SQL> select a.name,b.name
  2  from table1 a
  3  left outer join table2 b
  4  on a.id=b.id;

NAME                 NAME
-------------------- --------------------
wh                   wh
wq
wp
//右外连接
SQL> select a.name,b.name
  2  from table1 a
  3  right outer join table2 b
  4  on a.id=b.id;

NAME                 NAME
-------------------- --------------------
wh                   wh
                     wr
//右外连接
SQL> select a.name,b.name
  2  from table2 b
  3  right outer join table1 a
  4  on a.id=b.id;

NAME                 NAME
-------------------- --------------------
wh                   wh
wq
wp
//左外连接
SQL> select a.name,b.name
  2  from table2 b
  3  left outer join table1 a
  4  on a.id=b.id;

NAME                 NAME
-------------------- --------------------
wh                   wh
                     wr
//试一下FULL OUTER JOIN
SQL> select a.name,b.name
  2  from table2 b
  3  full outer join table1 a
  4  on a.id=b.id;

NAME                 NAME
-------------------- --------------------
wh                   wh
                     wr
wq
wp

SQL> select a.name,b.name
  2  from table1 a
  3  full outer join table2 b
  4  on a.id=b.id;

NAME                 NAME
-------------------- --------------------
wh                   wh
wq
wp
                     wr
 

小结一下

左连接就是左边的表全有值，右边表的值可以为空（+）

右连接是左边表值可以为空（+），右边表的值全有

至于使用OUTER JOIN的方法就稍微直观一些了，LEFT就是显示左边的表的全部内容，RIGHT就是显示右边的表的全部内容，FULL就是两边表内容都全显示。

既然说到这了已经，那么再来看一下自然连接。

view plaincopy to clipboardprint?
//使用using子句指定两个表相关联的列  
 
//注:被using 子句所引用的列,在sql 语句中的任何地方不能使用表名或者别名作为前缀  
 
SQL> select a.name,b.name  
  2  from table1 a  
  3  join table2 b  
  4  using(id);  
 
NAME                 NAME  
-------------------- --------------------  
wh                   wh  
 
SQL> select name  
  2  from table1  
  3  natural join table2;  
 
NAME  
--------------------  
wh  
 
//这里好像不让指定别名  
 
SQL> select a.name,b.name  
  2  from table1 a  
  3  natural join table2 b;  
select a.name,b.name  
              *  
ERROR at line 1:  
ORA-25155: column used in NATURAL join cannot have qualifier 
//使用using子句指定两个表相关联的列

//注:被using 子句所引用的列,在sql 语句中的任何地方不能使用表名或者别名作为前缀

SQL> select a.name,b.name
  2  from table1 a
  3  join table2 b
  4  using(id);

NAME                 NAME
-------------------- --------------------
wh                   wh

SQL> select name
  2  from table1
  3  natural join table2;

NAME
--------------------
wh

//这里好像不让指定别名

SQL> select a.name,b.name
  2  from table1 a
  3  natural join table2 b;
select a.name,b.name
              *
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
 

最后，来看一下交叉连接，也就是笛卡尔积。

view plaincopy to clipboardprint?
SQL> select a.name,b.name  
  2  from table1 a  
  3  cross join table2 b;  
 
NAME                 NAME  
-------------------- --------------------  
wh                   wr  
wp                   wr  
wq                   wr  
wh                   wh  
wp                   wh  
wq                   wh  
 
6 rows selected.