JOIN

from vs join

參考資料:(https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause)

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

參考資料:(https://tw.saowen.com/a/dfa016357c6a6c973d5cb9fc90a4e35ec95bba36c8f2d8ab2d2627f6ffe6f115)

[SQL]Join的觀念

參考資料:(https://dotblogs.com.tw/hatelove/2010/01/23/sql-join-concept)

IN vs EXISTS vs JOIN (Oracle)

參考資料:(https://explainextended.com/2009/09/30/in-vs-join-vs-exists-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

參考資料:(https://dotblogs.com.tw/hatelove/2010/01/23/sql-join-concept) 參考資料:(https://blog.wu-boy.com/2009/01/mysqlleft-right-inner-outer-join-%E4%BD%BF%E7%94%A8%E6%96%B9%E6%B3%95/)

四種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

(+) ((不推薦使用

參考資料:(https://matthung0807.blogspot.com/2017/09/oracle-outer-join-operator.html)

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

參考資料: CROSS JOIN 關鍵字 (SQL CROSS JOIN Keyword) - 交叉連接(https://www.fooish.com/sql/cross-join.html)

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

參考資料: Oracle 的 CROSS JOIN 文件(https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqljcrossjoin.html#rrefsqljcrossjoin)

產生兩個表的笛卡爾乘積，和其他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

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

半連接與反連接

參考資料:(http://blog.sina.com.cn/s/blog_7c5a82970101ix2l.html) 參考資料:半連接(https://riptutorial.com/zh-TW/oracle/example/14685/%E5%8D%8A%E9%80%A3%E6%8E%A5) 參考資料:反連接(https://riptutorial.com/zh-TW/oracle/example/14684/%E5%8F%8D%E9%80%A3%E6%8E%A5)

是針對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

參考資料:(https://www.techonthenet.com/oracle/in.php)

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

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

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

參考資料:(http://fecbob.pixnet.net/blog/post/43277902-oracle-sql%E8%AA%9E%E5%8F%A5in%E9%95%B7%E5%BA%A6%E4%B8%8D%E5%BE%97%E8%B6%85%E9%81%8E1000)

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

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

EXISTS

參考資料:(https://www.techonthenet.com/oracle/exists.php)

寫法 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)

參考資料:(https://blog.csdn.net/jssg_tzw/article/details/53509464)

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

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

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

INTERSECT 交集 A和B重複部分

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

PreviousCTENextOracle

Last updated