2013年8月27日 星期二

資料表連結(Table Join)概念

在資料庫設計上會採用正規化(Normalization)作法,以避免資料重複性維持資料一致性,而將相關聯的資料分散至數個資料表中,當需使用資料時,只需透過單一查詢動作,可透過連結(關鍵字為JOIN)的功能,將不同資料表(或資料來源)的資料連結成單一資料集,以建立所需的結果集。

以下以AB兩個資料表為例,其中A資料表的資料為1~4B資料表為3~5,為範例資料。
A
B
AB配對
N
1
2
3
4
N
3
4
5
A.N
B.N
1

2

3
3
4
4

5

分別以內部連結(Inner Join )、外部連結(OUTER JOIN)及交叉連結(CROSS JOIN)進行運算,結果整理如下表。
當使用外部連結(Outer Join)時需設定保留表資料表(Preserved table),在此定義A資料表為左側保留資料表,資料表B為右側保留資料表。


INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
CROSS JOIN
說明
內部結合。
傳回兩個資料表中相符(匹配)的記錄
外部結合。
左側為保留資料表。
外部結合。
右側為保留資料表。
外部結合。
左、右側同為保留資料表。
交叉連結。
兩個資料集直接相乘,得到兩者所有可能組合
圖示
A.N
B.N
1

2

3
3
4
4

5
A.N
B.N
1

2

3
3
4
4

5
A.N
B.N
1

2

3
3
4
4

5
A.N
B.N
1

2

3
3
4
4

5
                                                                               
A.N
B.N
1
3
1
4
1
5
2
3
2
4
2
5

筆數
2

4(A為主)

3(B為主)
5
12(4*3)

1.       內部連結(INNER JOIN
把兩個資料表連結在一起,傳回兩個資料表中相符(匹配)的記錄,即兩者的交集
INNER JOIN為最常使用的結合方式,以兩個資料表為範例,需皆符合兩個資料表要求的資料才予以顯示。

以下列出兩種常見的方法,第一種是使用JOIN (INNER JOIN)連結,將欲結合的資料表置於JOIN後方,並搭配ON以指定結合條件,當具有多個條件時,後續則再以邏輯運算子(ANDOR)以指定。
第二種則為傳統方式在FROM敍述中以逗號(,)作區隔來指定複數個資料表,並以WHERE敍述指定資料表的結合條件,將兩種方法SQL撰寫如下。

SQL
說明
使用JOIN
SELECT A.N, B.N
FROM A
INNER JOIN B
ON A.N = B.N
可將INNER JOIN簡化成JOIN
不使用JOIN
SELECT A.N, B.N
FROM A, B
WHERE A.N = B.N
常用寫法(傳統)
FROM敍述中以逗號(,)作區隔來指定複數的資料表的方式,但遺漏WHERE條件式時則轉變成另外的交叉連結(CROSS JOIN)資料表的結合條件均是以WHERE敍述來指定,所以會和資料的搜尋條件混在一起,降低原始碼可讀性。

資料表連結並不是只可採用等於(=)一種方式,許多進階用法都是採用非等式(: >>=<<=)的作法,應用非等式資料表連結方法可創造出額外的資料組合(空間),即笛卡兒積(Cartesian Products),拓展整個資料空間提供更多的可能應用。

SELF JOIN(自我連結)
自我連結是內部連結的另一種型式。自我連結中,資料表連結的對象均為資料表本身。常應用於組織圖取得最後一筆資料時使用

2.       外部連結OUTER JOIN
前一部份是探討內部連結只將傳回兩個資料表中相符(匹配)的記錄(交集),而利用外部連結(Outer Join)時則根據關鍵字(LEFTRIGHTFULL)所指保留資料表(Preserved table)為基礎,所有保留資料表()上的資料均將傳回,而不管資料列能否配對,而連結表格中無法滿足配對的資料欄位則填入NULL
外部連結一共有下列3種方法。
(1). LEFT OUTER JOIN
將左側(Left Outer Join左側)資料全部傳回,右側相符(匹配)的資料傳回,無法符合的部分則傳回NULL
(2). RIGHT OUTER JOIN
將右側(Right Outer Join右側)資料全部傳回,左側相符(匹配)的資料傳回,無法符合的部分則傳回NULL
(3). FULL OUTER JOIN
即兩個資料表的聯集,任何無法與另外資料表匹配的部分傳回NULL值。

先前資料進行以下三種測試,SQL語法及結果如下表所示:
OUTER JOIN
SQL
結果
說明
LEFT
SELECT A.N, B.N
FROM A
    LEFT OUTER JOIN B
       ON A.N = B.N
A.N
B.N
1
2
3
3
4
4
5
以左側為保留資料表即A表格。將保有A表格中4筆資料
RIGHT
SELECT A.N, B.N
FROM A
    RIGHT OUTER JOIN B
       ON A.N = B.N
A.N
B.N
1
2
3
3
4
4
5
以右側為保留資料表即B表格。將保有B表格中3筆資料
FULL
SELECT A.N, B.N
FROM A
     FULL OUTER JOIN B
        ON A.N = B.N
A.N
B.N
1
2
3
3
4
4
5
以左、右側均為保留資料表

當使用外部連結時,雖ORACLEMSSQL(2000前版本)有其特殊語法支援外部結合功能,但在此強烈建議使用標準ANSI語法。
MSSQL 2000()以前版本支援『*=』、『=*』為連接結合條件,在欲輸出所有資料的資料表上需加註『*』符號代表保留資料表(Preserved table),再次強調勿用。
ORACLE則是在結合欄位名稱後面加上『(+)』符號,以本範例資料表AB為例,若需輸出資料表A所有資料,則在兩者結合資料表B所有欄位名稱後加上『(+)』符號。實際運用時對於『*』、『(+)』兩個符號是置於等號的左側或右側,整理如下。
資料庫
條件
說明
MSSQL
A.Column =* B.Column
*』符號代表全部
ORACLE
A.Column = B.Column(+)
等式兩邊,不足的那側需加上(+)
由於『*』在許多文件規則上代表全部,保留資料表那側需以『*』符號標示;結合條件所使用的等號『=』,在等式兩邊,其中保留資料表那側將保有全部資料,另一側的結合資料表則可能缺少部分對應資料,將不足部分需額外加上NULL值以補足,因此ORACLE中使用『(+)』代表補足不等式。雖然ORACLE目前版本仍支援『(+)』語法,但在此仍建議採用ANSI語法,除降低維護及轉移上的成本,另外,採用『(+)』是不允許連結(Join)多個資料表。(此段文章僅是提供Porting至新版參考使用)


3.       交叉連結(CROSS JOIN
交叉連結(CROSS JOIN)是將兩個資料集直接相乘,得到兩個資料集間所有可能的組合,再由此組合資料集進行運算。
常利用CROSS JOIN將產生兩個資料集間所有可能的組合的特性,如應用於產生整個月所有產品可能的組合,以製作完整月報表,或者於舊版本之資料轉置(Column To Row)上使用。

前述範例資料以CROSS JOIN產生結果為例,表格A資料為1~4,共四筆,表格B3~5,共三筆,則將產生12(4*3)筆資料,結果略。

SQL
說明
CROSS JOIN
SELECT A.N, B.N
FROM A
CROSS JOIN B
CROSS JOIN關鍵字
WHERE條件式
(Join Condition)
SELECT A.N, B.N
FROM A, B
沒有WHERE條件式(Join Condition)
首先,可以直接使用CROSS JOIN關鍵字指示進行交叉連結;另外,也可採用常用查詢語法中但省略WHERE條件式的作法(更精準說是缺Join Conditon),雖此方法相當簡便,但具有一些風險,SQL中沒有任何WHERE條件式時,即代表CROSS JOIN;正常狀況下採用CROSS JOIN之機率不高,大部分狀況通常為遺漏,此種非預期的CROSS JOIN將致使資料乘開,而造成資料庫負載上重大影響。


以下將利用CROSS JOIN產生乘積的特性,達成一般程式語言以雙FOR迴圈產生九九乘法表。

SQL
說明
SQL SERVER 2005

ORACLE 11rR2
(類似)
WITH Tally(No) AS
(
SELECT 2 No
UNION ALL
SELECT No+1 No
FROM Tally
WHERE No<9
)
SELECT CAST(B.No AS VARCHAR)
  + ' * '
  + CAST(A.No AS VARCHAR)
  + ' = '
  + CAST(A.No * B.No AS VARCHAR)
  Result
FROM Tally A
CROSS JOIN Tally B
CROSS JOIN關鍵字
ORACLE 9i
WITH Tally
AS
(
SELECT LEVEL+1 N
FROM DUAL
CONNECT BY LEVEL<=8
)
SELECT A.No || ' * ' || B.No
    || ' = ' || A.No * B.No Reault
FROM Tally A, Tally B
沒有WHERE條件式
以上將應用一般資料表運算式(Common Table ExpressionCTE)產生資料數值序列暫存表格TallyORACLE還可使用CONNECT BY產生相同資料結果,雖然ORACLE也有採用CTE製作暫存表格,11g R2之前的版本並無支援遞迴(Recursive)功能,因此,使用CONNECT BY產生資料再以CTE暫存。

Result
2 * 2 = 4
2 * 3 = 6
2 * 4 = 8
2 * 5 = 10
9 * 7 = 63
9 * 8 = 72
9 * 9 = 81

最後,勿輕易針對各資料來源分別下查詢指令,再以應用程式(Application)連結各資料,通常此種作法會嚴重拖累資料庫效能,再者,資料庫均提供相當強大的資料連結功能,還請妥善利用。

沒有留言:

張貼留言