---
# System prepended metadata

title: SQL 進階
tags: [' DB', 學習筆記, ' 效能']

---

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

![](https://i.imgur.com/MQgRkdo.png)

### [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
}
```