ORACLE外连接分享

关注一下oracle 在查询条件中采用+的方法分享如下：    //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就是两边表内容都全显示。

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

 //使用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

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

 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.

下面是几个容易让人误认为不一样的东西，其实不然。

inner join = join

left outer join = left join

right outer join = right join