「SQLアンチパターン」の気づき（クエリ1）

## はじめに

こんにちは。小川です。
以前から気になっていた「SQLアンチパターン」という本を読みました。
とても良い本で今まで気づかずにやっていたことがSQLアンチパターンなんだと知ることができました。
勉強になったので、学んだことを言語化して量が多いのでいくつかの記事に分けて紹介していこうと思います。
今回は第Ⅲ部のクエリのアンチパターンについて記載します。
この記事は[「SQLアンチパターン」の気づき（DB物理設計2）](https://qiita.com/pesysyon/items/6a5b114c3eac5c0e3c62)の続きになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3780649/edfcea05-fe0b-f2b8-c4c9-0881241eae1e.png)

## 13章.フィア・オブ・ジ・アンノウン（恐怖のunknown）

#### アンチパターン
NULLを一般値として使う、または一般値をNULLとして使うアンチパターンです。

1. 式でNULLを扱う
    以下のクエリでhoursがNULLの場合、NULLは0にはならないのでNULL+10の結果としてNULLが返されます。
    ``` sql
    SELECT hours + 10 FROM Bugs;
    ```

1. NULLを許容する列の検索
    以下のクエリではBugsテーブルからassigned_to列に123を持つ行のみ返します。
    assigned_to列に他の値またはNULLを持つ行は返されない。
    
    ``` sql
    SELECT * FROM Bugs WHERE assigned_to = 123;
    ```

    以下のクエリではBugsテーブルからassigned_to列に123を持たない行のみ返します。
    ``` sql
    SELECT * FROM Bugs WHERE NOT assigned_to = 123;
    ```
    しかし、このクエリもassigned_to列にNULLが割り当てられた列を返さない。

    WHERE句の条件式はTRUEの場合のみ該当の行を返すので、NULLを用いた比較ではTRUE、もしくはFALSEではなく不明（unknown）を返すため、以下のクエリのようにNULLを検索しても行を取得することはできない。
    ``` sql
    SELECT * FROM Bugs WHERE assigned_to = NULL;
    ```

1. プリペアードステートメントでNULLを使用する
    プリペアードステートメントでパラメータ化したSQLでNULLを使用することは困難。
    以下のクエリでパラメータに整数値を使用した場合該当の結果を取得するので、NULLを使用することができない。
    ``` sql
    SELECT * FROM Bugs WHERE assigned_to = ?;
    ```

#### 解決策：NULLを一意な値として使う

1. スカラー式でのNULL
    NULLの問題のほとんどは真偽値で使用する場合にNULLはどのような挙動をするか知らないことです。
    以下の式では予想した結果と実際の結果での差異です。
    
    | 式 | 予想した結果 | 実際の結果 |
    |:-----------|------------|------------|
    | NULL = 0 | TRUE | NULL |
    | NULL = 12345 | FALSE | NULL |
    | NULL <> 12345 | TRUE | NULL |
    | NULL + 12345 | 12345 | NULL |
    | NULL = NULL | TRUE | NULL |
    | NULL <> NULL | TRUE | NULL |

1. 論理式でのNULL
    上記に続き論理式でも間違えやすくなっています。
    以下の式では予想した結果と実際の結果での差異です。
    | 式 | 予想した結果 | 実際の結果 |
    |:-----------|------------|------------|
    | NULL AND TRUE | FALSE | NULL |
    | NULL AND FALSE | FALSE | FALSE |
    | NULL OR FALSE | FALSE | NULL |
    | NULL OR TRUE | TRUE | TRUE |
    | NOT(NULL) | TRUE | TRUE |

1. NULLの検索
    NULLを検索するときは等式(=)では取得できないためIS NULLを使う必要があります。
    ``` sql
    SELECT * FROM Bugs WHERE assigned_to IS NULL;
    SELECT * FROM Bugs WHERE assigned_to IS NOT NULL;
    ```

1. NOT NULL制約を宣言する
    NULLがアプリケーションのポリシーに反する場合や、その列においてNULLが意味をなさない場合には、列にNOT NULL制約を宣言するのが良い。

## 14章.アンビギュアスグループ（曖昧なグループ）

#### アンチパターン
アンビギュアスグループとは、グループ内で最大値（または最小値）を持つ行を取得する際、その行のグループ化されていない列を取得する際に発生するアンチパターンです。

``` sql
--例）実行可能
SELECT  
 product_id,
 Max(date_reported) AS latest  
FROM 
 Bugs
INNER JOIN 
 BugsProducts 
USING (bug_id)
GROUP BY 
 product_id

-- 例）bug_idを追加すると、クエリエラー
SELECT  
 product_id,
 Max(date_reported) AS latest,
 Bugs.bug_id
FROM 
 Bugs
INNER JOIN 
 BugsProducts 
USING (bug_id)
GROUP BY 
 product_id
```

#### 単一値の原則(Single-Value Rule)

単一値の原則とは、「SELECT句で列挙した列について、行グループごとに単一の値でなければならない」という規則です。

```sql
SELECT  
 product_id,
 Max(date_reported) AS latest,
 Bugs.bug_id
FROM 
 Bugs
INNER JOIN 
 BugsProducts 
USING (bug_id)
GROUP BY 
 product_id
```

この例では、追加したbug_id列が問題となり、product_idとbug_idは一対多の関係にあります。そのため、product_idをGROUP BY句で集約した際、対応する複数のbug_idの全ての値を表すことができません。

#### 解決策：曖昧でない列を使用する

1. 関数従属属性のある列のみにクエリを実行する
    ```sql
    SELECT  
     product_id,
     Max(date_reported) AS latest
    FROM 
     Bugs
    INNER JOIN 
     BugsProducts 
    USING (bug_id)
    GROUP BY 
     product_id
    ```
    このクエリは、製品ごとの最新の日付を返します。最新のバグに対応するbug_idは返されませんが、それで十分な場合もあります。

1. 相関サブクエリを使用する
    ```sql
    SELECT  
     bp1.product_id,
     b1.date_reported AS latest,
     b1.bug_id
    FROM 
     Bugs b1
    INNER JOIN 
     BugsProducts bp1 
    USING (bug_id)
    WHERE NOT EXISTS
        (
            SELECT
             *
            FROM
             Bugs b2
            INNER JOIN 
             BugsProducts bp2
            ON
             b2.bug_id = bp2.bug_id
            WHERE
                 bp1.product_id = bp2.product_id
            --date_reportedが最新でないデータを抽出し、NOT EXISTで除外
             AND b1.date_reported < b2.date_reported 
        )     
    ```
    読みやすく、コードの記述も簡単な解決策です。
    ただし、相関サブクエリが外部クエリの各行に対してそれぞれ実行されるため、最善のパフォーマンスは得られない。

1. 導出テーブルを使用する
    ```sql
    SELECT  
     m.product_id,
     m.latest,
     b1.bug_id
    FROM 
     Bugs b1
    INNER JOIN 
     BugsProducts bp1 
    ON
     b1.bug_id = bp1.bug_id
    INNER JOIN
     (
        --導出テーブル
        SELECT
          bp2.product_id,
          MAX(b2.date_reported) AS latest
        FROM 
         Bugs b2
        INNER JOIN 
         BugsProducts bp2 
        ON
         b2.bug_id = bp2.bug_id
        GROUP BY
         bp2.product_id
     ) AS m
    ON
        bp1.product_id = m.product_id
    AND b1.date_reported = m.latest   
    ```

    導出テーブルによる解決策は、相関サブクエリに対する、よりパフォーマンスの高い選択肢として使用されます。

1. 他の列に対しても集約関数を使用する
    ```sql
    SELECT  
     product_id,
     Max(date_reported) AS latest,  
     Max(Bugs.bug_id)
    FROM 
     Bugs
    INNER JOIN 
     BugsProducts 
    ON
     Bugs.bug_id = BugsProducts.bug_id
    GROUP BY 
     product_id
    ``` 
    date_reportedの最大値とbug_idの最大値が必ず一致することが確約されている場合のみ、上記のクエリにより目的の値を取得できます。

## まとめ
今回は第Ⅲ部のクエリのアンチパターンについて一部まとめてみました。
この記事はこの本を読んで、私がこういう理解をしましたということを言語化してみました。
とても勉強になる本なので皆様もぜひ読んでみてください。

以上、小川でした。