SQL入门之1 select 联接

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

[@more@]

-- 笛卡尔连接 交叉连接
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#
;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271063/viewspace-908198/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271063/viewspace-908198/