Oracle中Left Outer Join和外關聯(+)的區別____Oracle

【原】Oracle中Left Outer Join和外關聯(+)的區別 2008-03-23 16:22:37

 

url: http://space.itpub.net/?uid-6517-action-viewspace-itemid-216974

 

 

 

 

Oracle的left join中on和where的區別 2009-09-28 15:20

URL: http://hi.baidu.com/bfsll/blog/item/3a884e2f0fc905321e3089a7.html

今天遇到一個求某月所有天數的統計結果，如果某日的結果是0也需要顯示出來，即:

日期                  交易次數   交易金額

2009-4-01           1              10

2009-4-02           2              20

2009-4-03           0              0

2009-4-04           5              50

....

 

一開始我用的左串連，用on做為兩表關聯條件，用where作為過濾條件，但是發現0的資料根本不顯示，後來把where關鍵字去掉，把過濾條件都放到on裡，問題解決，網上一搜，找到了答案：

資料庫在通過串連兩張或多張表來返回記錄時，都會產生一張中間的暫存資料表，然後再將這張暫存資料表返回給使用者。

      在使用left jion時，on和where條件的區別如下：

1、 on條件是在產生暫存資料表時使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄。

2、where條件是在暫存資料表產生好後，再對暫存資料表進行過濾的條件。這時已經沒有left join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉。

       假設有兩張表：

表1 tab1：

id size

1 10

2 20

3 30

表2 tab2：

size name

10    AAA

20    BBB

20    CCC

兩條SQL:
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

第一條SQL的過程：

1、中間表
on條件: 
tab1.size = tab2.size

tab1.id    tab1.size    tab2.size     tab2.name

1               10                   10               AAA

2               20                   20               BBB

2               20                   20               CCC

3               30                   (null)           (null)

2、再對中間表過濾
where 條件：
tab2.name=’AAA’

tab1.id       tab1.size        tab2.size     tab2.name

1                  10                  10              AAA

 

第二條SQL的過程：

1、中間表
on條件: 
tab1.size = tab2.size and tab2.name=’AAA’
(條件不為真也會返回左表中的記錄)

tab1.id      tab1.size         tab2.size       tab2.name

1               10                     10                   AAA

2               20                    (null)                (null)

3               30                    (null)                (null)

     其實以上結果的關鍵原因就是left join,right join,full join的特殊性，不管on上的條件是否為真都會返回left或right表中的記錄，full則具有left和right的特性的並集。 而inner jion沒這個特殊性，則條件放在on中和where中，返回的結果集是相同的。

轉自：http://hi.baidu.com/%BA%D3%B1%B1%B6%FE%C9%D9/blog

 

  多錶鏈接 Left join

URL: http://www.cnblogs.com/windamy/articles/585555.html 

 

一個我寫的執行個體：其中多表串連，一共串連了3個表。使用聚集合函式SUM，用到了GROUP BY SELECT  a. [ UserID ] ,b. [ Name ] , sum  (c. [ Money ] + c. [ Bank ] ) as  TotalMoney
FROM  Table1 a(nolock)
  LEFT   JOIN  Table2 b(nolock)  on  a. [ UserID ]   =  b. [ UserID ]  
LEFT   JOIN  Table3 c(nolock)  ON  b. [ UserID ] = c. [ UserID ]  

WHERE   a. [ UserID ]   =  b. [ UserID ]   and  a. [ UserID ]   =  c. [ UserID ]   and  a. [ Time ]   >=   ' 2005-01-01 '   AND  a. [ Time ]   <=   ' 2006-12-31 '  

GROUP   BY  a. [ UserID ] ,b. [ Name ]

ORDER   BY  a. [ Time ]   DESC  

最佳化一下

================================================================================
Left Join 文法：
SELECT  a. [ UserID ] ,b. [ Name ] , sum  (c. [ Money ] + c. [ Bank ] ) as  TotalMoney
FROM  Table1 a(nolock)
LEFT   JOIN  Table3 c(nolock)  ON  a. [ UserID ] = c. [ UserID ] ,  Table2 b(nolock) 

WHERE   a. [ UserID ]   =  b. [ UserID ]   and  a. [ Time ]   >=   ' 2005-01-01 '   AND  a. [ Time ]   <=   ' 2006-12-31 '  

GROUP   BY  a. [ UserID ] ,b. [ Name ]

ORDER   BY  a. [ Time ]   DESC  

  select     *     from
table1    left     join    table2    on    條件1   
left     join    table3    on    條件2   
left     join    table4    on    條件3  
where    條件4

GROUP BY 說明：
group by 

    在select 語句中可以使用group by 子句將行劃分成較小的組，然後，使用聚組函數返回每一個組的匯總資訊，另外，可以使用having子句限制返回的結果集。group by 子句可以將查詢結果分組，並返回行的匯總資訊Oracle 按照group by 子句中指定的運算式的值分組查詢結果。

   在帶有group by 子句的查詢語句中，在select 列表中指定的列要麼是group by 子句中指定的列，要麼包含聚組函數

   select max(sal),job emp group by job;
   (注意max(sal),job的job並非一定要出現，但有意義)

   查詢語句的select 和group by ,having 子句是聚組函數唯一出現的地方，在where 子句中不能使用聚組函數。

  select deptno,sum(sal) from emp where sal>1200 group by deptno having sum(sal)>8500 order by deptno;

  當在gropu by 子句中使用having 子句時，查詢結果中只返回滿足having條件的組。在一個sql語句中可以有where子句和having子句。having 與where 子句類似，均用於設定限定條件