# 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**

### 範例圖

![](https://460672175-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-Lmbdh9OrhI1G9B94HRH%2F-LmbdiNcoUTvtBYgYlVw%2F-Lmbdtk-AiRXpRX3KaCj%2Fjoin.jpg?generation=1566180876592514\&alt=media)

下面整個搜尋結果 -- 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 的高級用法，用數學的觀念來看，就是聯集、交集、差集。

![](https://460672175-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-Lmbdh9OrhI1G9B94HRH%2F-LmbdiNcoUTvtBYgYlVw%2F-Lmbdtk76B6GQJpT7flk%2F%E9%9B%86%E5%90%88.jpg?generation=1566180876550719\&alt=media)

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

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

INTERSECT 交集 A和B重複部分

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