Oracle的连接

连接分为：内连接、外连接、交叉连接
一、内连接——最常用
定义：仅将两个表中满足连接条件的行组合起来作为结果集。
在内连接中，只有在两个表中匹配的行才能在结果集中出现
关键词：INNER JOIN （可以省略）
格式：SELECT 列名表 FROM 表名1 [INNER] JOIN 表名2 ON或WHERE 条件表达式
说明：
（1）列名表中的列名可以出自后面的两个表，但如果两个表中有同名列，应在列名前标明出处，格式为：表名.列名
（2）若连接的两个表名字太长，可以为它们起个别名。 格式为：表名 AS 别名
（3）INNER是默认方式，可以省略
eg:
select *
from   t_institution i
inner join t_teller t
on i.inst_no = t.inst_no
where i.inst_no = "5801"
其中inner可以省略。
等价于早期的连接语法
select *
from t_institution i, t_teller t
where i.inst_no = t.inst_no
and i.inst_no = "5801"
二、外连接

在左外连接和右外连接时都会以一张表为基表，另一张表为外表，基表的内容会全部显示，然后加上两张表匹配的内容。 如果基表的数据在外表没有记录。 那么在相关联的结果集行中列显示为空值（NULL）。

对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项：
       1.（+）操作符只能出现在where子句中，并且不能与outer join语法同时使用。
       2. 当使用（+）操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都包含（+）操作符（即外表的条件都要加），否则连接失败
       3.（+）操作符只适用于列，而不能用在表达式上。
       4.（+）操作符不能与or和in操作符一起使用。
       5.（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。

1、左（外）连接
定义：在内连接的基础上，还包含左表中所有不符合条件的数据行，并在其中的右表列填写NULL
关键字：LEFT JOIN
eg:
select *
from   t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
其中outer可以省略。
注意:
当在内连接查询中加入条件是，无论是将它加入到join子句，还是加入到where子句，其效果是完全一样的，但对于外连接情况就不同了。当把条件加入到 join子句时，SQL Server、Informix会返回外连接表的全部行，然后使用指定的条件返回第二个表的行。如果将条件放到where子句 中，SQL Server将会首先进行连接操作，然后使用where子句对连接后的行进行筛选。下面的两个查询展示了条件放置位子对执行结果的影响：
条件在join子句
select *
from   t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
and i.inst_no = “5801”
结果是：
inst_no     inst_name             inst_no     teller_no   teller_name
5801        天河区                5801        0001        tom
5801        天河区                5801        0002        david
5802        越秀区
5803        白云区
条件在where子句
select *
from   t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
where i.inst_no = “5801”
结果是：
inst_no     inst_name             inst_no     teller_no   teller_name
5801           天河区                    5801          0001               tom
5801           天河区                    5801          0002              david

2、右（外）连接
定义：在内连接的基础上，还包含右表中所有不符合条件的数据行，并在其中的左表列填写NULL
关键字：RIGHT JOIN
3、完全连接
定义：在内连接的基础上，还包含两个表中所有不符合条件的数据行，并在其中的左表、和右表列填写NULL
关键字：FULL JOIN
三、交叉连接
定义：将两个表的所有行进行组合，连接后的行数为两个表的乘积数。（笛卡尔积）
关键词：CROSS JOIN
格式：FROM 表名1 CROSS JOIN 表名2
四, 自身连接
自身连接是指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系（也称作递归关系）中抽取数据。例如人力资源数据库中雇员与老板的关系。
下面例子是在机构表中查找本机构和上级机构的信息。
select s.inst_no superior_inst, s.inst_name sup_inst_name, i.inst_no, i.inst_name
from t_institution i
join t_institution s
on i.superior_inst = s.inst_no
结果是：
superior_inst sup_inst_name         inst_no     inst_name
800                             广州市                5801        天河区
800                             广州市                5802        越秀区
800                             广州市                5803        白云区

 

关于使用（+）的一些注意事项2举例（网上的例子）：
注意事项2：当使用（+）操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都包含（+）操作符（即外表的条件都要加），否则连接失败
   测试用的例子：xtest2是基表，xtest1是外表表结构及数据如下

　　xtest1（外表）  company_code  item_code item_name   cost_class

　　00  item1  name1  f

　　00  item2  name2  m

　　01  item1  name1  m

　　01  item2  name2  f考试大－全国最大教育类网站(www．Examda。com)

　　xtest2（基表）  company_code  item_code t1   t2

　　00  item1  aa  bb

　　00  item2  cc  dd

　　00  item3  ee  ff

　　null  null  null  null

　　要求：将xtest2中的数据全部显出，根据xtest2中的公司号及物料代码去xtest1表里找对应的物料名，但是有一个附加条件，就是xtest1表里必须是cost_class='f'的才能写到最后的结果集中。最后实验成功的sql如下：

　　select a.item_code, b.item_name
  from xtest2 a, xtest1 b
 where a.item_code = b.item_code(+)
   and b.cost_class(+) = 'f'
   and a.company_code = b.company_code(+)

　　以b表是外表，b的条件都必须加+，否则连接失败（查出结果有问题），另外也可以用SQL的表中来写：

select a.item_code, b.item_name
  from xtest2 a
  left join xtest1 b on a.item_code = b.item_code
                    and b.cost_class = 'f'
                    and a.company_code = b.company_code

 

关于使用（+）的一些注意事项3举例（网上的例子）：
注意事项3：（+）操作符只适用于列，而不能用在表达式上。
 select  distinct a.city_id, a.city_name, count(b.msisdn)
  from  region_info a, receive_user b
 where a.city_id = substr(b.area_id, 1, 5) (+)
  and a.province_id = '10'
  and b.end_date < to_char(sysdate, 'yyyymmdd')
 group by a.city_id, a.city_name

 因为oracle左右连接不允许表达式,先生成列，再嵌套

SELECT DISTINCT a.city_id, a.city_name, COUNT(b.msisdn)
  FROM region_info a,
       (SELECT msisdn, end_date, substr(area_id, 1, 5) area_id receive_user) b
 WHERE a.city_id = area_id(+)
   AND a.province_id = '10'
   AND b.end_date < to_char(SYSDATE, 'yyyymmdd')
 GROUP BY a.city_id, a.city_name