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

### 範例圖

![](/files/-Lmbdtk-AiRXpRX3KaCj)

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

![](/files/-Lmbdtk76B6GQJpT7flk)

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

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

INTERSECT 交集 A和B重複部分

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://brianwu.gitbook.io/brian/zi-liao-ku/sql/join.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.