達人に学ぶSQL徹底指南書をまとめてみた

# NULLに関して

### はじめに

- 3値理論
    - SQLでは値として「true」「false」「unknown」がある
    - プログラミング言語などは通常2値理論「true」「false」である
- NULLは値ではない
    - 「unknown」は値、「null」は値ではない
        
        ```
        unknown =unknown →true
        NULL = NULL →unknown
        ```
        
    - NULLの判断は「= null」ではなく「IS NULL」を使用している
    - SQLではNULLは値ではなく、述語もうまく動かない
    - 無理にNULLを使用すると「unknown」になる
    - 「unknown」が論理演算に紛れ込むと直感とは反する動きをする
        
        ```
        --以下の式は全部unknown に評価される。
        1 = NULL
        2 > NULL
        3 < NULL
        4 <> NULL
        NULL = NULL
        ```
        
    - 段階的なステップに分けてSQLを追うことが有効

### 真理表

3値論理の真理表（NOT）

| x | NOT x |
| --- | --- |
| t | f |
| u | u |
| f | t |

3値論理の真理表（AND）

| AND | t | u | f |
| --- | --- | --- | --- |
| t | t | u | f |
| u | u | u | f |
| f | f | f | f |

3値論理の真理表（OR）

| OR | t | u | f |
| --- | --- | --- | --- |
| t | t | t | t |
| u | t | u | u |
| f | t | u | f |
- ANDの場合： **false** ＞ **unknown** ＞ **true**
- ORの場合： **true** ＞ **unknown** ＞ **false**

※強い方が弱い方を飲み込む

## 実践編

### 非中立が成立しない

- ジョンは20歳か、20歳ではないか、どちらかである
    - 現実世界では「true」になる
    - SQLの世界では「unknown」になる
    
    ```sql
    -- ジョンの年齢がnullの場合
    -- 1
    select *
    from students
    where age = null
    or age <> null
    ```
    
    ```sql
    -- 2
    select *
    from students
    where unknown
    or unknown
    ```
    
    ```sql
    -- 2
    select *
    from students
    where unknown
    ```
    
    - 結果はunknownになってしまう
    
    ```sql
    -- 正解
    select *
    from students
    where age = 20
    and age <> 20
    or age IS NULL
    ```
    

### CASE式とNULL

- NULLを値として扱ってはいけない
    
    ```sql
    case col_1 
    	when 1 then 〇
    	when null then ×
    end
    ```
    
    - 結果はunknownになる。
    - when null は col_1 = null と同じなので
    
    ```sql
    -- 正解
    case
    	when col_1 = 1 then 〇
    	when col_1 is null then ×
    end
    ```
    

### NOT IN と NOT EXISTは同義ではない

- NOT IN
    
    ```sql
    select *
    from class
    where age NOT IN (23, 21, NULL)
    ```
    
    ```sql
    select *
    from class
    where NOT ((age = 23) OR (age = 21) OR (age = NULL))
    ```
    
    ```sql
    select *
    from class
    where (age <> 23) AND (age <> 21) AND unknown
    ```
    
    ```sql
    select *
    from class
    where unknown
    ```
    
    - なにもとれない
- NOT EXISTS
    
    ```sql
    select *
    from class_A
    where NOT EXISTS ( select * from class_B where A.age = B.age and B.city = '東京')
    ```
    
    ```sql
    select *
    from class_A
    where NOT EXISTS ( select * from class_B where A.age = null and B.city = '東京')
    ```
    
    ```sql
    select *
    from class_A
    where NOT EXISTS ( select * from class_B where unknown and B.city = '東京')
    ```
    
    ```sql
    select *
    from class_A
    where NOT EXISTS ( select * from class_B where unknown)
    ```
    
    ```sql
    select *
    from class_A
    where true
    ```
    
    - 値が取れる

### 限定述語

- ALL
    
    ```sql
    select *
    from class
    where age < ALL (23, 21, NULL)
    ```
    
    ```sql
    select *
    from class
    where (age < 23) AND (age < 21) AND unknown
    ```
    
    ```sql
    select *
    from class
    where unknown
    ```
    
- MIN
    
    ```sql
    select *
    from class_A
    where age < ( select MIN(age) from class_B where B.city = '東京')
    ```
    
    - これならOK
    - ALLの場合：彼は東京の誰よりも若い
    - MINの場合：彼は東京在住の最も若い生徒よりも若い

### 集約関数（COUNT()以外）

```sql
-- 集約関数内にnullがまぎれるとunknownになってしまう
select *
from class_A
where age < ( select AVG(age) from class_B where B.city = '東京')
```

# HAVING句に関して

### 連番IDに歯抜けがあるかを確認

```sql
select '歯抜けあり' as gap
from ~
HAVING COUNT(*) <> MAX(seq)
```

- HAVING句は単独で使用できる
- テーブル全体を一つの集合体とみなし、その集合自身の性質を調べる

### 平均の出し方

- AVGでほんとにいいの？？（外れ値に影響を受ける）

- 最頻値を求める（集団の中で最も数が多い）

```sql
-- ALL述語の利用
select income, count(*) 
from A
group by income
HAVING COUNT(*) >= ALL (select COUNT(*) from A group by income)
```

```sql
-- 極地関数の利用
select income, count(*) 
from A
group by income
HAVING COUNT(*) >= select MAX(cnt) 
															from (select COUNT(*) as cnt from A group by income) TMP
```

- メジアンを求める（中央値）
    
    ```sql
    SELECT AVG(DISTINCT income)
    FROM (SELECT T1.income
    				FROM Graduates T1, Graduates T2
    				GROUP BY T1.income
    				--S1の条件
    				HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1
    				ELSE 0 END) >= COUNT(*) / 2
    				--S2の条件
    				AND SUM(CASE WHEN T2.income <= T1.income THEN 1
    				ELSE 0 END) >= COUNT(*) / 2
    ) TMP;
    ```
    
    ### NULLを含まない集合を探す
    
    - COUNT(*)
        - NULLを数える（全行を数える）
    - COUNT(列名)
        - NULLを数えない
    
    - 提出日にNULLを含まない学部を選択する
        
        ```sql
        select 学部
        from A
        group by 学部
        HAVING COUNT(*) = COUNT(提出日) 
        ```
        
        ```sql
        -- caseでも書ける（sum case）
        select 学部
        from A
        group by 学部
        HAVING COUNT(*) = SUM ( CASE WHEN 学部 IS NOT NULL THEN 1
        															ELSE 0 END);
        ```
        
    - COUNT()まとめ
        
        ```sql
        -- colの値が一意である
        count (distinct col) = count(col)
        
        -- colにNULLが存在しない
        count(*) = count(col)
        
        -- colは歯抜けのない連番(開始値は1)
        count(*) = max(col)
        
        -- colは歯抜けのない連番(開始値は任意の整数)
        count(*) = max(col) - min(col) + 1
        
        -- colが1つだけの値を持つか、またはNULLである
        min(col) = max(col)
        
        -- すべてのcol_xの符号が同じである
        min(col) * max(col) > 0
        
        -- 最大値の符号が正で最小値の符号が負
        min(col) * max(col) < 0
        
        -- colは少なくとも1つのゼロを含む
        min(abs(col)) = 0
        
        -- colの最大値と最小値が指定した定数から同じ幅の距離がある
        min(col - 定数) = - max(col - 定数)
        ```
        
        # 処理速度
        
        ```sql
        -- SELECT文の実行順序
        -- この順序でSQLを書く
        
        FROM句
        ↓
        JOIN句
        ↓
        WHERE句
        ↓
        GROUP BY句
        ↓
        HAVING句
        ↓
        SELECT句
        ↓
        ORDER BY句
        ↓
        LIMIT句
        ```
        
        ## INとEXISTSの違い
        
        - EXISTSの方が早い
            - 結合キーにインデックスが利用できる
            - 一行でも合致する行があれば、検索を打ち切る
        
        ## ソートを回避する
        
        - ソートが発生するもの
            - GROUP BY
            - ORDER BY
            - 集約関数（SUM, COUNT, AVG, MAX, MIN）
                - 引数の列にインデックスが存在する場合は、実表への検索を対処できる（count(*)は実テーブル。count(id)はインデックス）
            - DISTINCT
                - ２つのテーブルからの重複を検知する場合は、EXISTSを使用しよう
            - 集合演算子（UNION, INTERSECT, EXCEPT）
                - 上記は重複排除の為、ソートを行う
                - 重複して良い場合は、UNION ALLを使用しよう（ソートが発生しない）
            - OLAP関数（RANK, ROW_NUMBER等）
            
            ### WHERE句でかけるものはHAVING句で書かない
            
            - GROUP BYはソートを行う
            - HAVINGは集約後の条件なので、インデックスが聞かない場合が多い
            
            ### GROUP BYやORDER BYでインデックスを使う
            
            - インデックスのソートは高速になる
            - ユニーク・インデックスの場合はソート自体もスキップできるかも。
        
        ## インデックスの基本
        
        ### インデックスを利用する時は条件式の左辺は裸
        
        ```latex
        WHERE column * 1.1 > 10
        ```
        
        ではなく
        
        ```
        WHERE column > 10 / 1.1
        ```
        
        にする
        
        ### IS NULLをつかった場合はインデックスが無効
        
        インデックスにはNULLは存在しないため(posgreはきく)
        
        ### 非定型をつかっているとインデックスが無効
        
        <>とか!=とかはインデックスが無効
        
        ### ORをつかっているとインデックスが無効
        
        ### 後方一致、または中間一致のLIKEを用いているとインデックスが無効
        
        前方一致のみ有効です。
        
        ### ビューで集約関数や集合演算が入っていると速度低下の原因になりがち
        
        集約関数、`AVG,SUM,COUNT,MAX,MIN`
        
        集合演算子、`UNION,INTERSECT,EXCEPT`