内联接外联接全联接join的用法

一. 笛卡尔连接 交叉连接
select title,name from books,publisher;
select title,name from books cross join publisher;

二.相等连接 内连接
-- 常规用法
select title,name from books a,publisher b where a.pubid=b.pubid;
-- 有公共列，使用natural join 或者using
select title,name from books natural join publisher;
select title,name from books join publisher using (pubid);
-- 公共列名称不同，使用on
select title,name from books b join publisher p on b.pubid=p.putid;

三. 不等连接
-- 按照范围值进行匹配，关键是promotion中的范围值不能有重叠
select title,gift from books,promotion where retail between minretail and maxretail;
select title,gift from books join promotion on retail between minretail and maxretail;

四.自连接 根据介绍人ID，在同一表中查找介绍人姓、名
-- 常规
select r.firstname,r.lastname,c.lastname referred
from customers c,customers r
where c.referred=r.customer#；
-- 使用 join
select r.firstname,r.lastname,c.lastname referred
from customers r join customers c
on c.referred=r.customer#;

五.外连接 实质是在orders表不存在对应记录时增加null来显示
-- 把没有下订单的客户也显示出来，而不是仅显示下订单的客户
-- 传统方法
select lastname,firstname,order#
from customers c,orders o
where c.customer# =o.customer#(+)
order by c.customer#
;
--等效
-- 使用join
select lastname,firstname,order#
from customers c left outer join orders o
on c.customer# = o.customer#
order by c.customer#
;
--等效
select lastname,firstname,order#
from orders o right outer join customers c
on c.customer# = o.customer#
order by c.customer#
;

六. 全外连接

-- 包括没下订单的客户，也包括下订单但不在客户表中的订单
select lastname,firstname,order#
from orders o full outer join customers c
on c.customer# = o.customer#
order by c.customer#
;

-- 包括下订单但不在客户表中的订单
select lastname,firstname,order#
from customers c right outer join orders o
on c.customer# = o.customer#
order by c.customer#
;

七.外连接的限制

ORACLE公司建议使用带有from clause outer join的语法来代替使用ORACLE的join连接符(+)。使用join连接符(+)的查询语句具有以下限制，而from clause outer join 的语法没有这些限制：
1、 不能在查询块中使用连接符(+)（同样适用于from clause outer join的语法），只能在where条件中使用。
2、 只适用于表或视图的列。
3、 如果A表和B表的连接条件有多个的话，必须在每个条件中都使用(+)操作符，否则，当做内连接处理。ORACLE并不会对这种情况有报错或建议性提示。
4、 当你指定一个表使用外连接，其它表使用内连接时，(+)不会产生外连接。
5、 不能使用(+)对一个表产生自连接。
6、 （+）连接符只对表或视图的列产生作用，不对任意表达式产生作用，但是表达式中可以包含多个带有(+)连接符的列。
7、 含有（+）的连接条件不能和or逻辑运算符一起使用。
8、 在表达式中，带有(+)的列不能使用in比较条件。
9、 连接条件中，带有(+)的列不能和子查询做比较。
10、 如果A表和B表以A表左连接时，在WHERE条件中表的某个列和一个常数表达式做比较时，B表的这个字段必须使用(+)符号，以保证以A表为基准做左连接，否则左连接失效，使用内连接。
11、 当有2对以上表做复合左连时，只有一个表可以作为左连接的基准表，比如A,B,C3个表在做连接时，不能将(+)用在B表的列上，在以A,B配对做连接条件同时以B,C配对做连接条件。