JOIN

PreviousCTE NextOracle

Last updated 5 years ago

Was this helpful?

JOIN

from vs join

參考資料:()

In vs Exist vs Join 效能比較 (SQL Server)

參考資料:()

[SQL]Join的觀念

參考資料:()

IN vs EXISTS vs JOIN (Oracle)

參考資料:()

語法轉換範例:

IN

select foo
from bar
where bar.stuff in
    (
    select stuff
    from asdf
    )

EXIST

SELECT foo
FROM bar
WHERE EXISTS
    (
    SELECT NULL
    FROM asdf 
    WHERE asdf.stuff = bar.stuff
    )

JOIN/DISTINCT

SELECT b.foo
FROM    
    (
    SELECT DISTINCT stuff
    FROM asdf
    ) a
JOIN bar b
ON b.stuff = a.stuff

結論: (2009/09/30)

IN看似效率很差，其實Oracle 有對IN做很好的優化，不管被查詢的欄位是否有被建立索引，都很好。

EXISTS與IN的優化完全相同，但是IN更具有可讀性和簡潔性。

JOIN

四種JOIN

join = inner join

left join = left outer join

right join = right outer join

full join

範例圖

下面整個搜尋結果 -- full join

倒數第二筆去掉 -- left join

倒數兩筆都去掉 -- inner join

最後一筆去掉 -- right join

full join 也等於

left join union right join

(+) ((不推薦使用

Oracle PL/SQL的(+)符號代表OUTER JOIN的意思。不過Oracle官方直接建議還是直接使用OUTER JOIN語法，不建議使用舊的(+)語法。此外在Java程式JDBC使用舊的(+)語法會導致ResultSet部分欄位的值為空，所以還是不要使用吧。

select *
from student s
left outer join student_course sc on s.STUD_NO = sc.STUD_NO 
;
select *
from student s, student_course sc
where s.STUD_NO = sc.STUD_NO(+)
;

(+)有使用上的限制，詳細請去看操考資料。

Natural Join

同 inner join，差別是不用寫on 會根據相同欄位自動連接

Cross join

交叉連接為兩個資料表間的笛卡兒乘積 (Cartesian product)，兩個資料表在結合時，不指定任何條件，即將兩個資料表中所有的可能排列組合出來，以下例而言 CROSS JOIN 出來的結果資料列數為 3×5=15 筆，因此，當有 WHERE、ON、USING 條件時不建議使用。

產生兩個表的笛卡爾乘積，和其他JOIN不同，不允許指定on語句。但可以在SELECT中指定WHERE語句。

相同

SELECT * FROM CITIES CROSS JOIN FLIGHTS
SELECT * FROM CITIES, FLIGHTS

相同

SELECT * FROM CITIES CROSS JOIN FLIGHTS
    WHERE CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
SELECT * FROM CITIES INNER JOIN FLIGHTS
    ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT

相同

SELECT * FROM CITIES LEFT OUTER JOIN
    (FLIGHTS CROSS JOIN COUNTRIES)
        ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
            WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'

SELECT * FROM CITIES LEFT OUTER JOIN
    FLIGHTS INNER JOIN COUNTRIES ON 1=1
        ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
            WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'

SELECT * FROM CITIES LEFT OUTER JOIN
    (SELECT * FROM FLIGHTS, COUNTRIES) S
        ON CITIES.AIRPORT = S.ORIG_AIRPORT
            WHERE S.COUNTRY_ISO_CODE = 'US'

連接:內連接、外連接、反連接、半連接

內連接其實就是上面的 inner join

外連接也就是上面的 outer join

那麼反連接 和半連接 又是什麼呢?

半連接與反連接

是針對IN, EXISTS, NOT IN, NOT EXISTS 的變形，前兩者為半連接，後兩者為反連接。

子查詢在FROM裡面，叫做 IN-LINE VIEW，在WHERE子句中叫做NESTED SUBQUERY(嵌套子查詢)。 IN, EXISTS, NOT IN, NOT EXISTS 都屬於嵌套子查詢。

IN, EXISTS 展開變成 SEMI-JOIN。 NOT IN, NOT EXISTS 是轉換成 ANTI-JOIN。

半連接的重點在於:對於外表的某筆記錄，在內表找到一個匹配的紀錄就返回外表)

從Oracle 9i開始，IN 和EXISTS 已經沒有區別，執行計畫是一樣的。

NOT EXISTS 與 NOT IN 的區別在於處理NULL

NOT IN 查看子結果中有沒有NULL，若有，return false。 NOT EXISTS 不關心NULL，只關心紀錄數，如果有紀錄，返回false。

NOT IN可能在匹配列上，引起效能問題，原因是索引失敗

IN 和EXISTS

IN

寫法 expression IN (value1, value2, ... valueN) 用來

判斷某個欄位的值，有沒有在某個陣列中。

有個說法是IN 後面接的陣列，裡面的value數量不能超過1000，實際去google "oracle IN 限制" 也可以查到相關的討論，不過根據下面的連結

因為以前SQL的長度不能超過32K，因為輸入參數只能是VARCHAR2，但是 11G 以後，可以用 CLOB 作為數入參數，所以取消了這個限制。

所以倘若Oracle版本在11G以前的，的確是有1000個的限制，但11G之後的可能有待嘗試。

EXISTS

寫法 where EXISTS ( subquery ); 用來

判斷某個子查詢有沒有返回值，倘若有一個就終止，return true。倘若無，return false。

拿來兩個表查詢

select val1
from t1
where exists
    (
        select 1
        from t2
        where t2.val1 = t1.val1
    )

效能比較

有索引的欄位

沒有索引的欄位

set操作符(UNION, UNION ALL, INTERSECT, MINUS)

Oracle Database 的集合操作屬於SELECT 的高級用法，用數學的觀念來看，就是聯集、交集、差集。

UNION 聯集(去除重複) A + B - (A和B重複部分)

UNION ALL 聯集(保留重複) A + B

INTERSECT 交集 A和B重複部分

MINUS 差集 (上方比下方多出來的部分) A - B *MINUS 是 Oracle的，MS SQL 是用 except

PreviousCTE NextOracle

Last updated 5 years ago

Was this helpful?