資料庫表串連

Oracle左右全串連總結
--建立測試資料 
create table a(id number); 
create table b(id number); 
insert into a values(1); 
insert into a values(2); 
insert into a values(3); 
insert into b values(1); 
insert into b values(2); 
insert into b values(4); 
commit; 
 
--左: 
--主流資料庫通用的方法 
select * from a left join b on a.id=b.id; 
--Oracle特有的方法 
select * from a, b where a.id=b.id(+); 
 
        ID         ID 
---------- ---------- 
         1          1 
         2          2 
         3  
 
--右： 
--主流資料庫通用的方法 
select * from a right join b on a.id=b.id; 
--Oracle特有的方法 
select * from a, b where a.id(+)=b.id; 
 
        ID         ID 
---------- ---------- 
         1          1 
         2          2 
                    4 
          
          
--內 
--主流資料庫通用的方法  內串連和where關聯串連相同
select * from a join b on a.id=b.id;  (inner可以省略）
--where關聯 
select * from a, b where a.id=b.id; 
 
        ID         ID 
---------- ---------- 
         1          1 
         2          2 
          
          
--全外 
--主流資料庫通用的方法 
select * from a full join b on a.id=b.id; 
--Oracle特有的方法 
select * 
  from a, b 
where a.id = b.id(+) 
union 
select *  
  from a, b  
where a.id(+) = b.id; 
 
        ID         ID 
---------- ---------- 
         1          1 
         2          2 
         3  
                    4 
 
 
--完全，也叫交叉串連或者笛卡爾積 
--主流資料庫通用的方法 
select * from a,b; 
--或者 
select * from a cross join b; 

主要cross join不能添加on條件
  
        ID         ID 
---------- ---------- 
         1          1 
         1          2 
         1          4 
         2          1 
         2          2 
         2          4 
         3          1 
         3          2 
         3          4 
 
 
串連無非是這幾個 
--內串連和where相同 
inner join 
--左向外串連，返回左邊表所有合格 
left join 
--右向外串連，返回右邊表所有合格 
right join 
--完整外部串連，左向外串連和右向外串連的合集 
full join 
--交叉串連，也稱笛卡兒積。返回左表中的每一行與右表中所有行的組合 
cross join 
 
 
--補充： 
--左向外串連，返回左邊表所有合格， 
--注意這裡沒有第二個加號，會直接過濾掉資料，只顯示合格記錄 
select *  
  from a, b 
where a.id = b.id(+) 
   and b.id = 2; 
    
        ID         ID 
---------- ---------- 
         2          2    
          
          
--左向外串連，返回左邊表所有合格 
--注意where上第二個加號，它的作用是修改右邊表記錄的顯示，例如如果b.id(+) = 2，顯示為2，否則顯示null 
select * 
  from a, b 
where a.id = b.id(+) 
   and b.id(+) = 2; 
 
        ID         ID 
---------- ---------- 
         2          2 
         3  
         1    

 

 

注意：on與where的區別：

1. left join 和right join ，inner join 條件不能放在where後面，必須放到on後面。

2. cross join執行笛卡爾積，和使用豆號把表名分開相同，所以要把條件放到where後，不能放到on後面。

 

 

 

具體應用的一個例子

select s.InAreaID,o.stationid,o.RouteID,count(*) as cnt 
from TC_OutList as o right join tc_squdlog  --先這兩個表建立有串連

on o.stationid=tc_squdlog.staID and o.laneid=tc_squdlog.laneid and o.Squadon=tc_squdlog.Squadon  --右串連的篩選條件
, MC_Station as s  --然後與這個表建立內串連
where o.findt='20091001000000' and  o.instaid =s.StationID and s.VerID=o.ratever --內串連的篩選條件
group by o.stationid,s.InAreaID,o.RouteID --分組

 

--或者這樣寫
select s.InAreaID,o.stationid,o.RouteID,count(*) as cnt 
from TC_OutList as o right join tc_squdlog on o.stationid=tc_squdlog.staID and o.laneid=tc_squdlog.laneid and o.Squadon=tc_squdlog.Squadon
join MC_Station as s on  o.instaid =s.StationID and s.VerID=o.ratever
where o.findt='20091001000000'
group by o.stationid,s.InAreaID,o.RouteID