Oracle中join left，join right，inner join，(+) 等

博客分类：

Oracle

建表create table TEST1
create table TEST1
(
t_id VARCHAR2(21),
t_name VARCHAR2(21),
t_mangerid VARCHAR2(21)
)

create table TEST2
(
t_id VARCHAR2(21),
salary NUMBER
)

test1 暂且叫员工表吧，test2薪水表

在oracle中外链接为 a left/right join b on a.xx = b.xx其中join后面也可以添加outer也可以省略，相当于a.xx = b.xx (+)(此时为左链接)，写法的历史与优劣本文暂不讨论，只针对简单的左右链接，外链接，全链接等，对应的关键字如下：

  CROSS JOIN        笛卡尔乘积（所有可能的行对）
INNER JOIN        仅对满足连接条件的CROSS中的列
LEFT OUTER JOIN        一个表满足条件的行，和另一个表的所有行
RIGHT OUTER JOIN        与LEFT相同，但两个表的角色互换
FULL OUTER JOIN        LEFT OUTER 和 RIGHT OUTER中所有行的超集

链接定义如下：

连接类型        定义
内连接        只连接匹配的行
左外连接        包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
右外连接        包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行
全外连接        包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。
(H)(theta)连接        使用等值以外的条件来匹配左、右两个表中的行
交叉连接        生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

现对以上每种链接做一个查询，在数据库中插入如下数据：

TEST1中

1 001 dyf 003

2 002 xwc 003
3 003 mm
TEST2中

        1 001 7000
        2 002 5000
        3 009 10000

inner join内链接
       select a.t_name, b.salary
           from test1 a
         full join test2 b
              on a.t_id = b.t_id

      结果如下 1 dyf 7000
                   2 xwc 5000
                   3        10000
                   4 mm
5.     自身链接

     select a.t_id,a.t_name, b.t_name
        from test1 a
      join test1 b
         on a.t_mangerid = b.t_id

     查询员工管理者的姓名

     结果如下 1 002 xwc mm
                  2 001 dyf mm
6.     交叉无限制链接

      交叉连接用于对两个源表进行纯关系代数的乘运算。它不使用连接条件来限制结果集合，而是将分别来自两个数据源中的行以所有可能的方式进行组合。数据集合中一的每个行都要与数据集合二中的每一个行分别组成一个新的行。例如，如果第一个数据源中有5个行，而第二个数据源中有4个行，那么在它们之间进行交叉连接就会产生20个行。人们将这种类型的结果集称为笛卡尔乘积。
大多数交叉连接都是由于错误操作而造成的；但是它们却非常适合向数据库中填充例子数据，或者预先创建一些空行以便为程序执行期间所要填充的数据保留空间。
    select a.t_id,a.t_name,b.t_id,b.salary
      from test1 a
         cross join test2 b

         select a.t_name, b.salary
            from test1 a
         inner join test2 b
              on a.t_id = b.t_id

此语句与下面语句相同

select a.t_name, b.salary
from test1 a, test2 b

where a.t_id = b.t_id

查询的是员工的薪水，只有两个表中匹配才会出现查询出结果，结果如下

1 dyf 7000
2 xwc 5000

2. left join on左外连接

         select a.t_name, b.salary
           from test1 a
         left join test2 b
              on a.t_id = b.t_id

与

select a.t_name, b.salary
from test1 a, test2 b

where a.t_id = b.t_id(+)相同

查询员工的薪水，不管新水表中是否有此员工的记录都会把此员工显示出来，结果如下

        1 dyf 7000
        2 xwc 5000
        3 mm

3. right join on右外连接

       select a.t_name, b.salary
           from test1 a
         right join test2 b
              on a.t_id = b.t_id

查询员工的薪水，不管员工表中是否存在薪水表中对应的员工都会把薪水表数据选出来，结果如下

         1 dyf 7000
         2 xwc 5000
         3  10000
4.     full join on 全链接

left join和（+）的区别和联系

Oracle 建议你用在From语句后用Outer Join语法而不是Oracle的Join操作符(+).而且(+)是要受下面的规则限制的，但Outer Join语法则不受的～～

1）你不可以在查询块中使用(+) 当它同时包含 join的from语句中
2）(+)只是在where语句中,并且只能对应一个表或视图的一行字段
3）如果A和B做联接时有多个条件，那么(+)必须完善所有的匹配条件，
如果没有 ,oracle不会警告你～只是结果自然不同的
4）不可以在作出一个表外查询另张表内查询的(+)联接操作～～
5）不可以用（+）外联接到自己当然Self Join是可以的
6）含（+）的Where后的注意
OR不可用
IN不可用
子查询不可用

以下给些个例子：

SQL code

SQL>desc part

Name Null? Type
----------------------------------------- -------- -----------------
PART_ID NOTNULLVARCHAR2(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 NOTNULLVARCHAR2(4)
SUPPLIER_NAME NOTNULLVARCHAR2(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
2from part p, supplier s
3where 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
2from part p, supplier s
3where 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
2from part p, supplier s
3where p.supplier_id = s.supplier_id (+)
4union
5select p.part_id, s.supplier_name
6from part p, supplier s
7where 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
2from part p fullouterjoin supplier s
3on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1 Supplier#1
P2 Supplier#2
P4
P3
Supplier#3

另外的EG：

SQL code
SQL>select*from testa;

ID NAME ADDR AGE
---------- ------ ------ ----------
3 电子南京 23
5 物理北京 23
6 物理河北 25
1 电脑上海 23
2 电子北京 22
4 物理芜湖 22
7 艺术上海 21
8 歌剧苏州 16
9 沈亮上海 22

已选择9行。

SQL>select*from testb;

ID ADDR
---------- ----------
7 上海
1 北京
3 上海
5 合肥
--匹配完全
select testa.id,testa.addr from testa,testb where testa.id(+)=testb.id and
testa.addr(+)=testb.addr;

ID ADDR
---------- ------
7 上海

现在的Outer Join方法是Oracle9i时开始引用的 ANSI标准的联接语法，现在的则比较直观简单
通过上边Full Join的实现方式不难看到ANSI的联接语法要比用含Union的(+)的执行效率更高～～