2009年3月17日 星期二

關聯式代數 - 非原始運算子與 SQL 的對應關係

關聯式代數 - 非原始運算子與 SQL 的對應關係

非原始運算子:以下的三個運算子可由上述五個運算子推演出來,因此稱為「非原始運算子」,但是由於這幾個運算子的存在增加了理論推演的便利性,因此也就存在下來了。
6. Join:將兩個 Table 合併後,篩選出符合條件的資料,並挑選所需要的欄位。
    從原始運算子的推演:
    1. 將 Table T1 與 Table T2 做 Cartesian Product 運算,結果為 Table R1。
    2. 對 Table R1 作 Select 運算就可以得到結果了。

    將 Table T1 與 Table T2 以 EMP.DEPID 與 DEP.DEPID 做 Join 運算的結果如下,Table 中的資料為假設值。

    Table ( EMP )
    EMPIDEMPNAMEDEPID
    1趙一a
    2錢二a
    3孫三b
    4李四b

    Table ( DEP )
    DEPIDDEPNAME
    a技術部
    b業務部

    Table ( EMP ) 與 Table ( DEP ) 以 EMP.DEPID 與 DEP.DEPID 做 Join 運算的結果
    EMP.EMPIDEMP.EMPNAMEEMP.DEPIDDEP.DEPIDDEP.DEPNAME
    1趙一aa技術部
    2錢二aa技術部
    3孫三bb業務部
    4李四bb業務部

    對應的 SQL 語法:
    代碼:
    Select *
        From EMP, DEP
        Where EMP.DEPID = DEP.DEPID

7. Intersection:從兩個 Table 中取出交集的資料。
    從原始運算子的推演:以下推演 Table T1 以 Table T2 取代結果是一樣的。
    1. 將 Table T1 與 Table T2 做 Difference 運算,結果為 Table R1。
    2. 將 Table T1 與 Table R1 做 Difference 運算。

    將 Table T1 與 Table T2 做 Intersection 運算的結果如下,Table 中的資料為假設值。

    Table ( T1 )
    C1C2
    c1ac2a
    c1bc2b

    Table ( T2 )
    C1C2
    c1ac2a
    c1cc2c

    Table ( T1 ) 與 Table ( T2 ) 做 Difference 運算的結果
    C1C2
    c1ac2a

    對應的 SQL 語法一:適用於提供 Temporary Table 的資料庫,以兩句 Select 語法完成。
    代碼:
    Select *
        Into #R1
        From T1
        Where not exists (
            Select *
            From T2
            Where T1.C1 = T2.C1 and T1.C2 = T2.C2)

    Select *
        From T1
        Where not exists (
            Select *
                From #R1
                Where T1.C1 = #R1.C1 and T1.C2 = #R1.C2)

    對應 SQL 語法二:適用於提供將 Select 的結果可以直接再使用的資料庫,其實就是將上述兩句 Select 語法結合成一句 Select 語法。
    代碼:
    Select *
        From T1
        Where not exists (
            Select *
                From (
                    Select *
                        From T1 T3
                        Where not exists (
                            Select *
                                From T2
                                Where T3.C1 = T2.C1 and T3.C2 = T2.C2)
                    ) R1
        Where T1.C1 = R1.C1 and T1.C2 = R1.C2)

    對應 SQL 語法三:上述兩種語法都是依據理論上的推演法直接取得答案,實務上的做法就簡單多了。
    代碼:
    Select T1.C1, T1.C2
        From T1, T2
        Where T1.C1 = T2.C1 and T1.C2 = T2.C2

8. Divide:兩個 Table 間做除法運算,假設 Table T1 為被除表,Table T2 為除表,有一個大前提是 T1 一定要比 T2 的欄位多出一個以上,同時兩個 Table 間有相同屬性的欄位,運算的結果是 T1 必須包含所有的 T2 值。看不懂在說什麼,這是正常的,參考以下範例就可以瞭解了。
    從原始運算子的推演:步驟很繁複就不在此說明了。

    將 Table EMPSKILL 與 Table SKILL 做 Divide 運算的結果如下,希望從員工資料中取得符合所有技能的員工資料,Table 中的資料為假設值。

    Table ( EMPSKILL )
    EMPNAMESKILL
    趙一PowerBuilder
    趙一Sybase
    趙一Java
    錢二PowerBuilder
    錢二Oracle
    錢二ASP

    Table ( SKILL )
    SKILL
    PowerBuilder
    Sybase

    Table ( EMPSKILL ) 與 Table ( SKILL ) 以 SKILL 為基準找出符合所有條件的 EMPNAME 做 Divide 運算的結果
    EMPNAME
    趙一

    對應的 SQL 語法:
    代碼:
    Select Distinct EMPNAME
        From EMPSKILL T1
        Where not exists (
            Select *
                From SKILL T2
                Where not exists (
                    Select *
                        From EMPSKILL T3
                        Where T3.EMPNAME = T1.EMPNAME and T3.SKILL = T2.SKILL ) )

這就是關聯式代數八大運算子與 SQL 的對應方式,其中最特別是 Divide 運算子,要如何善加利用就看實際上的需要了。

本則專題由飛達客工作室提供