oracle怎么實現左聯，右聯與外聯?9i以前寫法 9i以后10g 11g的寫法

左聯：
select a.id,a.name,b.address from a,b
where a.id=b.id(+)
右聯:
select a.id,a.name,b.address from a,b
where a.id(+)=b.id
外聯
SELECT a.id,a.name,b.address
FROM a,b
WHERE a.id = b.id(+)
UNION

SELECT b.id,'' name,b.address
FROM b
WHERE NOT EXISTS (
SELECT * FROM a
WHERE a.id = b.id);
在9i以上，已經開始支持SQL99標準，所以，以上語句可以寫成：
默認內部聯結：
select a.id,a.name,b.address,c.subject
from (a inner join b on a.id=b.id)
inner join c on b.name = c.name
where other_clause
左聯
select a.id,a.name,b.address
from a left outer join b on a.id=b.id
where other_clause
右聯
select a.id,a.name,b.address
from a right outer join b on a.id=b.id
where other_clause
外聯
select a.id,a.name,b.address
from a full outer join b on a.id=b.id
where other_clause
or
select a.id,a.name,b.address
from a full outer join b using (id)
where other_clause

posted on 2009-07-08 10:19 MichaelLee 閱讀(2175) 評論(0)  編輯  收藏 所屬分類: oracle