[學習筆記] Oracle集合運算、內連接、外連接、偽列

集合運算

• INTERSECT(交集)，返回兩個查詢共有的記錄。

• UNION ALL(並集重覆)，返回各個查詢的所有記錄，包括重覆記錄。

• UNION(並集不重覆)，返回各個查詢的所有記錄，不包括重覆記錄 。

• MINUS(補集)，返回第一個查詢的記錄減去第二個查詢的記錄之後剩餘的記錄。

-- 交集
select * from stuinfo1 
intersect
select * from stuinfo2;

-- 並集重覆
select * from stuinfo1 
union all
select * from stuinfo2;

-- 並集不重覆
select * from stuinfo1 
union 
select * from stuinfo2;

-- 補集
select * from stuinfo1 
minus
select * from stuinfo2;

內連接

兩張表通過某個欄位進行內關聯，查詢結果是通過該欄位按關係運算符匹配出的數據行。

select a.stuid, a.stuname, a.classno,
       b.classno, b.classname, b.monitorid, b.monitorname
from stuinfo a, class b
where a.classno = b.classno;

外連接

• left join：等價於 left outer join，返回左表中的所有記錄和右表中聯結欄位相等的記錄。

• right join：等價於 right outer join，返回右表中的所有記錄和左表中聯結欄位相等的記錄。

• full join：等價於 full outer join，查詢結果等於左外連接和右外連接的和。

--左外連接（stuinfo1表中數據都保留,stuinfo2不在stuinfo1中存在的欄位為null值）
select a.*, b.stuid, b.stuname
from stuinfo1 a left join stuinfo2 b
on a.stuid = b.stuid;

--左外連接另外一種寫法
select a.*, b.stuid, b.stuname
from stuinfo1 a,stuinfo2 b 
where a.stuid = b.stuid(+);

--右外連接（stuinfo2表中數據都保留,stuinfo1不在stuinfo2中存在的欄位為null值）
select a.*, b.stuid, b.stuname
from stuinfo1 a right join stuinfo2 b
on a.stuid = b.stuid;

--右外連接另外一種寫法
select a.*, b.stuid, b.stuname
from stuinfo1 a,stuinfo2 b 
where a.stuid(+)=b.stuid;

--全外連接（stuinfo1、stuinfo2表中數據都保留,stuinfo1不在stuinfo2存在的學生相關欄位為null值,stuinfo2不在stuinfo1存在的學生相關欄位為null值）
select a.*, b.stuid, b.stuname
from stuinfo1 a full join stuinfo2 b
on a.stuid = b.stuid;

偽列

• ROWID：返回數據在數據文件中的物理地址。ROWID 值可以唯一的標識表中的一行。
• ROWNUM：標識查詢結果集的順序，第一行標識為1，後面依次遞增。

select t.*, t.rowid from stuinfo t;
select t.*, t.rowid from stuinfo t where t.rowid='AAAShjAAEAAAAEFAAD';

select t.*, rownum from stuinfo t;

-- 返回學生信息表中學生年齡最低的前四位同學
select * from (
    select t.*, rownum from stuinfo t order by t.age asc
) where rownum <= 4;