---
tags: 學習筆記, DB, 效能
---
SQL 進階
===============
## IN、EXISTS(外大內小=IN，外小內大=EXISTS)
- [IN 與 EXISTS 差異](https://eeluck.pixnet.net/blog/post/27559378-in%E5%92%8Cexists%E7%9A%84%E5%B7%AE%E7%95%B0)
- EXISTS 的原理:
exists 做為 where 條件時，是先對 where 前的主查詢詢進行查詢，然後用主查詢的結果一個一個的代入 exists 的查詢進行判斷，如果為真則輸出當前這一條主查詢的結果，否則不輸出(LOOP 判斷外表)
- IN 的原理
根據命令句逐行比對(內外表 HASH 連結)
```sql
SELECT *
FROM Table1
WHERE (c1,c2) IN (SELECT c1,c2 FROM Table2);
SELECT Name
FROM Table1
WHERE EXISTS(
SELECT *
FROM Table2
WHERE Table2.sn = Table1.sn AND Name = 'Wheels'
);
```
## IS NULL 與 INDEX
ORACLE 的索引是利用 B-Tree 建立，
所以當 SQL 查詢的條件是指定使用 ISNULL 或 IS NOT NULL，OracleOptimizer 將無法使用索引。
處理方法：
- 可以將 NULL 值都補成其他沒有意義的值，如 0 或是 -1。
- 直接分析各種 NULL 的需求利用 IF-ELSE 特化該類查詢[參考](http://sharedderrick.blogspot.com/2018/08/)
```sql
WHERE (@SalesOrderID IS NULL OR sd.SalesOrderID = @SalesOrderID)
```
```sql
IF @SalesOrderID IS NULL THEN
SELECT * FROM sd;
ELSE
SELECT *
FROM sd
WHERE @SalesOrderID = sd.SalesOrderId;
END IF;
```
## JOIN或分次查詢
多數情況 SELECT 資料可能會直接對 TABLE 做 SELECT，但有些時候也許先查再做 JOIN 可能會更有效率。
情境：一對多(很多)的表格，篩選條件主要在第一張表(主表?)
例子：記錄各家分店的營業日(是否放假)，可能一百家分店，紀錄整年的營業日資料。
- 主表(分店):100筆
- 副表(營業日紀錄):100*365筆
```sql
SELECT *
FROM TB_MAIN TM
INNER JOIN TB_SUB TS
ON TM.PK = TS.FK
WHERE TM.CONDITION1 = i_condition1
AND TM.CONDITION2 = i_condition2;
```
```sql
v_key_list ????_LIST; -- 自訂型別，多筆
SELECT PK INTO v_key_list
FROM TB_MAIN
WHERE CONDITION1 = i_condition1
AND CONDITION2 = i_condition2;
SELECT *
FROM TB_MAIN TM
INNER JOIN TB_SUB TS
ON TM.PK = TS.FK
WHERE TM.PK IN(v_key_list);
```
```sql
v_key NUMBER; -- 如果確定只會篩選單筆也可以(感覺可能比較有效)
SELECT PK INTO v_key
FROM TB_MAIN
WHERE CONDITION1 = i_condition1
AND CONDITION2 = i_condition2;
SELECT *
FROM TB_MAIN TM
INNER JOIN TB_SUB TS
ON TM.PK = TS.FK
WHERE TM.PK = v_key;
```
```sql
v_key NUMBER; -- 如果確定只會篩選單筆也可以(感覺可能比較有效)
SELECT PK INTO v_key
FROM TB_MAIN
WHERE CONDITION1 = i_condition1
AND CONDITION2 = i_condition2;
SELECT *
FROM TB_SUB
WHERE FK = v_key; -- 也有可能完全不需要主表資料，只要副表
```
## 索引最左匹配
DB 可以建立複合索引如下
```sql
CREATE TABLE `TEST_INDEX_SEQUENCE` (
`ID` INT(11) NOT NULL AUTO_INCREMENT,
`INDEX_A` VARCHAR(255) DEFAULT NULL,
`INDEX_B` VARCHAR(255) DEFAULT NULL,
`INDEX_C` VARCHAR(255) DEFAULT NULL,
`FIELD_D` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `COMPLEX_INDEX` (`INDEX_A`,`INDEX_B`,`INDEX_C`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;
```
其中索引為 INDEX_A, INDEX_B, INDEX_C。
實際查詢時可正確使用索引的查詢條件為
- INDEX_A
- INDEX_A、INDEX_B
- INDEX_A、INDEX_C
- INDEX_A、INDEX_B、INDEX_C
### [Blink-mind 示意](https://awehook.github.io/react-mindmap/)
```JSON
{
"rootTopicKey": "13259eb5-a35f-491a-adcc-a17ef92b9bbd",
"editorRootTopicKey": "13259eb5-a35f-491a-adcc-a17ef92b9bbd",
"focusKey": "d8fa2d77-f38b-473f-9e04-4c4608669751",
"extData": {
"TOPIC_REFERENCE": {
"reference": {}
},
"TAGS": {
"tags": {
"Matz's Heavy Metal Album": {
"name": "Matz's Heavy Metal Album",
"style": "{\"backgroundColor\":\"grey\",\"color\":\"black\"}",
"topicKeys": [
"cb906aad-92b4-4dc8-9dd9-a95bf6f91ac7"
]
},
"Seriously The Best Book You Will Ever Read": {
"name": "Seriously The Best Book You Will Ever Read",
"style": "{\"backgroundColor\":\"grey\",\"color\":\"black\"}",
"topicKeys": [
"d8fa2d77-f38b-473f-9e04-4c4608669751"
]
},
"Great Charity": {
"name": "Great Charity",
"style": "{\"backgroundColor\":\"grey\",\"color\":\"black\"}",
"topicKeys": [
"d72460ca-59d4-45fa-bd49-068da1b8d918"
]
},
"Teach Kids To Code": {
"name": "Teach Kids To Code",
"style": "{\"backgroundColor\":\"grey\",\"color\":\"black\"}",
"topicKeys": [
"387a8691-9155-43f0-a615-20f3aea3a7e3"
]
},
"The New Pickaxe Book": {
"name": "The New Pickaxe Book",
"style": "{\"backgroundColor\":\"grey\",\"color\":\"black\"}",
"topicKeys": [
"fe9c27ae-c3f6-4ddc-8a40-974b86612fad"
]
},
"Tom Clancy's Unfinished Work": {
"name": "Tom Clancy's Unfinished Work",
"style": "{\"backgroundColor\":\"grey\",\"color\":\"black\"}",
"topicKeys": [
"0403f71c-e611-447a-a2f8-77c6f1594646"
]
},
"I'm Rich But NEED Money For My Book": {
"name": "I'm Rich But NEED Money For My Book",
"style": "{\"backgroundColor\":\"grey\",\"color\":\"black\"}",
"topicKeys": [
"a3f48ba0-e181-4610-a099-45aed7417c36"
]
},
"New Album From Your Favorite Band": {
"name": "New Album From Your Favorite Band",
"style": "{\"backgroundColor\":\"grey\",\"color\":\"black\"}",
"topicKeys": [
"7f23c6a6-3418-4d3c-977a-7775e04b01e0"
]
},
"Feel Like The Best Person Alive Charity": {
"name": "Feel Like The Best Person Alive Charity",
"style": "{\"backgroundColor\":\"grey\",\"color\":\"black\"}",
"topicKeys": [
"0221746a-dd15-4f3b-8872-a24b7c5c146c"
]
},
"The Best Album Ever, When It Is Done": {
"name": "The Best Album Ever, When It Is Done",
"style": "{\"backgroundColor\":\"grey\",\"color\":\"black\"}",
"topicKeys": [
"6d593140-34c6-4246-87e4-2323cdda868f"
]
}
}
}
},
"topics": [
{
"key": "7fcc35dd-1391-4d24-aa38-b9914aa2a572",
"parentKey": "13259eb5-a35f-491a-adcc-a17ef92b9bbd",
"subKeys": [
"7f23c6a6-3418-4d3c-977a-7775e04b01e0",
"0403f71c-e611-447a-a2f8-77c6f1594646",
"d8fa2d77-f38b-473f-9e04-4c4608669751"
],
"collapse": false,
"style": null,
"blocks": [
{
"type": "CONTENT",
"data": "CATEGORY_ID：3"
}
]
},
{
"key": "387a8691-9155-43f0-a615-20f3aea3a7e3",
"parentKey": "e6de8807-8504-47a9-9c2e-c89a01a5c1a5",
"subKeys": [],
"collapse": false,
"style": null,
"blocks": [
{
"type": "CONTENT",
"data": "ID：7"
}
]
},
{
"key": "d72460ca-59d4-45fa-bd49-068da1b8d918",
"parentKey": "e6de8807-8504-47a9-9c2e-c89a01a5c1a5",
"subKeys": [],
"collapse": false,
"style": null,
"blocks": [
{
"type": "CONTENT",
"data": "ID：8"
}
]
},
{
"key": "d8fa2d77-f38b-473f-9e04-4c4608669751",
"parentKey": "7fcc35dd-1391-4d24-aa38-b9914aa2a572",
"subKeys": [],
"collapse": false,
"style": null,
"blocks": [
{
"type": "CONTENT",
"data": "ID：9"
}
]
},
{
"key": "0403f71c-e611-447a-a2f8-77c6f1594646",
"parentKey": "7fcc35dd-1391-4d24-aa38-b9914aa2a572",
"subKeys": [],
"collapse": false,
"style": null,
"blocks": [
{
"type": "CONTENT",
"data": "ID：5"
}
]
},
{
"key": "a3f48ba0-e181-4610-a099-45aed7417c36",
"parentKey": "e6de8807-8504-47a9-9c2e-c89a01a5c1a5",
"subKeys": [],
"collapse": false,
"style": null,
"blocks": [
{
"type": "CONTENT",
"data": "ID：4"
}
]
},
{
"key": "e6de8807-8504-47a9-9c2e-c89a01a5c1a5",
"parentKey": "13259eb5-a35f-491a-adcc-a17ef92b9bbd",
"subKeys": [
"0221746a-dd15-4f3b-8872-a24b7c5c146c",
"a3f48ba0-e181-4610-a099-45aed7417c36",
"387a8691-9155-43f0-a615-20f3aea3a7e3",
"d72460ca-59d4-45fa-bd49-068da1b8d918"
],
"collapse": false,
"style": null,
"blocks": [
{
"type": "CONTENT",
"data": "CATEGORY_ID：2"
}
]
},
{
"key": "cb906aad-92b4-4dc8-9dd9-a95bf6f91ac7",
"parentKey": "b1b52e12-f58e-4e57-9d64-38f88716a9ac",
"subKeys": [],
"collapse": false,
"style": null,
"blocks": [
{
"type": "CONTENT",
"data": "ID：10"
}
]
},
{
"key": "7f23c6a6-3418-4d3c-977a-7775e04b01e0",
"parentKey": "7fcc35dd-1391-4d24-aa38-b9914aa2a572",
"subKeys": [],
"collapse": false,
"style": null,
"blocks": [
{
"type": "CONTENT",
"data": "ID：3"
}
]
},
{
"key": "0221746a-dd15-4f3b-8872-a24b7c5c146c",
"parentKey": "e6de8807-8504-47a9-9c2e-c89a01a5c1a5",
"subKeys": [],
"collapse": false,
"style": null,
"blocks": [
{
"type": "CONTENT",
"data": "ID：2"
}
]
},
{
"key": "6d593140-34c6-4246-87e4-2323cdda868f",
"parentKey": "b1b52e12-f58e-4e57-9d64-38f88716a9ac",
"subKeys": [],
"collapse": false,
"style": null,
"blocks": [
{
"type": "CONTENT",
"data": "ID：1"
}
]
},
{
"key": "fe9c27ae-c3f6-4ddc-8a40-974b86612fad",
"parentKey": "b1b52e12-f58e-4e57-9d64-38f88716a9ac",
"subKeys": [],
"collapse": false,
"style": null,
"blocks": [
{
"type": "CONTENT",
"data": "ID：6"
}
]
},
{
"key": "b1b52e12-f58e-4e57-9d64-38f88716a9ac",
"parentKey": "13259eb5-a35f-491a-adcc-a17ef92b9bbd",
"subKeys": [
"6d593140-34c6-4246-87e4-2323cdda868f",
"fe9c27ae-c3f6-4ddc-8a40-974b86612fad",
"cb906aad-92b4-4dc8-9dd9-a95bf6f91ac7"
],
"collapse": false,
"style": null,
"blocks": [
{
"type": "CONTENT",
"data": "CATEGORY_ID：1"
}
]
},
{
"key": "13259eb5-a35f-491a-adcc-a17ef92b9bbd",
"parentKey": null,
"subKeys": [
"b1b52e12-f58e-4e57-9d64-38f88716a9ac",
"e6de8807-8504-47a9-9c2e-c89a01a5c1a5",
"7fcc35dd-1391-4d24-aa38-b9914aa2a572"
],
"collapse": false,
"style": null,
"blocks": [
{
"type": "CONTENT",
"data": "PROJECTS"
}
]
}
],
"config": {
"readOnly": false,
"allowUndo": true,
"layoutDir": 0,
"theme": {
"name": "default",
"randomColor": true,
"background": "rgb(57,60,65)",
"highlightColor": "#50C9CE",
"marginH": 60,
"marginV": 20,
"contentStyle": {
"lineHeight": "1.5",
"fontSize": "16px"
},
"linkStyle": {
"lineRadius": 5,
"lineType": "curve",
"lineWidth": "3px"
},
"rootTopic": {
"contentStyle": {
"fontSize": "36px"
},
"subLinkStyle": {
"lineType": "curve",
"lineWidth": "3px"
}
},
"primaryTopic": {
"contentStyle": {
"fontSize": "24px"
},
"subLinkStyle": {
"lineType": "curve",
"lineWidth": "3px"
}
},
"normalTopic": {
"subLinkStyle": {
"lineType": "curve",
"lineWidth": "3px"
}
}
}
},
"formatVersion": null
}
```
Sign in with Wallet
Connect another wallet