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#
;