はじめに
この記事は自分が仕事をしていく中でSQLに関して苦戦した内容になります。皆さんの中にも同じ状況になるかもしれないと思ったので、備忘録もかねて書いてみました。
曲者のNULL1（NULLと空欄は別物）
例えば、以下のSQLでとあるテーブルを以下のように作ったとします。
SELECT
ID,
VALUE
FROM
(
VALUES (1,NULL),(2,''),(3,'X')
) AS VALUES_T(ID,VALUE)
1 NULL
2
3 X
このとき、NULLと空欄は別物となりますので、以下のSQLを実行しても空欄の行は取得できてもNULLの行は取得できません。
SELECT
ID,
VALUE
FROM
(
VALUES (1,NULL),(2,''),(3,'X')
) AS VALUES_T(ID,VALUE)
WHERE VALUE = ''
2
そのため、NULLを空欄として扱うには事前にISNULLで空欄に変換する必要があります。
SELECT
ID,
VALUE
FROM
(
VALUES (1,NULL),(2,''),(3,'X')
) AS VALUES_T(ID,VALUE)
WHERE ISNULL(VALUE,'') = ''
1 NULL
2
曲者のNULL2（NULL値と別の値の比較は必ずfalse）
例えば、以下のようにXを取得しようとした場合には正しく表示されます。
SELECT
ID,
VALUE
FROM
(
VALUES (1,NULL),(2,''),(3,'X')
) AS VALUES_T(ID,VALUE)
WHERE VALUE = 'X'
3 X
逆に、Xを含まないものを表示しようとするとNULLの行は表示されないという問題がおきます。
SELECT
ID,
VALUE
FROM
(
VALUES (1,NULL),(2,''),(3,'X')
) AS VALUES_T(ID,VALUE)
WHERE VALUE != 'X'
2
これは、VALUEがNULLだと別の値との比較式が必ずFALSEになるためで、NULLも含めて判定するには先ほどと同じようにISNULLで空欄に変換するか VALUE IS NULL を追加する必要があります。
SELECT
ID,
VALUE
FROM
(
VALUES (1,NULL),(2,''),(3,'X')
) AS VALUES_T(ID,VALUE)
WHERE ISNULL(VALUE,'') != 'X'
SELECT
ID,
VALUE
FROM
(
VALUES (1,NULL),(2,''),(3,'X')
) AS VALUES_T(ID,VALUE)
WHERE VALUE != 'X' OR VALUE IS NULL
1 NULL
2
APPLY ってなんだ？
例えば、以下のようにあるテーブルに同じ値を連結するSQLを見たことがあると思います。
SELECT
ID1,
ID2,
NO
FROM ( VALUES (1,2),(3,4),(5,6) ) AS VALUES_T(ID1,ID2)
OUTER APPLY
(
SELECT 1 AS NO
) AS APPLY_T
1 2 1
3 4 1
5 6 1
このAPPLYというのは「SELECT 1 AS NO」といったクエリを各行ごとに実行して、その結果をそのまま行に連結するものとなります。そのため、APPLYの中身が2行になれば、その分行数も二つになります。
SELECT
ID1,
ID2,
NO
FROM
( VALUES (1,2),(3,4),(5,6) ) AS VALUES_T(ID1,ID2)
OUTER APPLY
(
SELECT NO
FROM
( VALUES (1),(2) ) AS APPLY_IN_T(NO)
) AS APPLY_T
1 2 1
1 2 2
3 4 1
3 4 2
5 6 1
5 6 2
ここで各行ごとにという言葉を強調したのは、APPLYの中で連結する対象のテーブル（今だとFROMの対象テーブル）の項目が利用できるためで、実際以下のようにするとID1とID2を一つのNOというカラムにまとめることができます。
SELECT
NO
FROM ( VALUES (1,2),(3,4),(5,6) ) AS VALUES_T(ID1,ID2)
OUTER APPLY
(
SELECT NO
FROM ( VALUES (ID1),(ID2) ) AS APPLY_IN_T(NO)
) AS APPLY_T
1
2
3
4
5
6
あるいは、WHEREで利用することも可能です。
SELECT
ID1,
ID2,
NO
FROM ( VALUES (1,2),(3,4),(5,6) ) AS VALUES_T(ID1,ID2)
OUTER APPLY
(
SELECT NO
FROM ( VALUES (1),(2) ) AS APPLY_IN_T(NO)
WHERE NO = ID1
) AS APPLY_T
1 2 1 → NO = 1,2 のうち、ID1 = 1 と一致するものが 1 だけなので 1 行
3 4 NULL → NO = 1,2 のうち、ID1 = 3 と一致するものがないため 0 行
5 6 NULL → NO = 1,2 のうち、ID1 = 5 と一致するものがないため 0 行
ちなみに、OUTER APPLY なので APPLY の中が 0 行でも NULL と出てきますが、一方で CROSS APPLY を使うと 0 行の場合、その行自体が消えます。（LEFT JOIN と INNER JOIN の関係と似ています）
SELECT
ID1,
ID2,
NO
FROM ( VALUES (1,2),(3,4),(5,6) ) AS VALUES_T(ID1,ID2)
CROSS APPLY
(
SELECT NO
FROM ( VALUES (1),(2) ) AS APPLY_IN_T(NO)
WHERE NO = ID1
) AS APPLY_T
1 2 1
VIEW、WITH ってなんだ？
例えば、以下のように同じようなSQL（ VALUES (1,2),(3,4),(5,6) ）が書いてあることがあります。
SELECT
VALUES1_T.ID1 AS VALUES1_ID1,
VALUES1_T.ID2 AS VALUES1_ID2,
VALUES2_T.ID1 AS VALUES2_ID1,
VALUES2_T.ID2 AS VALUES2_ID2
FROM ( VALUES (1,2),(3,4),(5,6) ) AS VALUES1_T(ID1,ID2)
LEFT JOIN ( VALUES (1,2),(3,4),(5,6) ) AS VALUES2_T(ID1,ID2)
ON VALUES2_T.ID1 = VALUES1_T.ID1
1 2 1 2
3 4 3 4
5 6 5 6
同じSQLであれば、あらかじめ VIEW というものを作成して、それを一つのテーブルとして利用することができます。例えば、以下のように VW_VALUES を作ったとします。
CREATE VIEW VW_VALUES AS
SELECT *
FROM ( VALUES (1,2),(3,4),(5,6) ) AS VALUES_T(ID1,ID2)
SELECT *
FROM VW_VALUES
1 2
3 4
5 6
これを利用すると、先ほどの SQL は VIEW を用いて簡潔に書くことができます。
SELECT
VALUES1_T.ID1 AS VALUES1_ID1,
VALUES1_T.ID2 AS VALUES1_ID2,
VALUES2_T.ID1 AS VALUES2_ID1,
VALUES2_T.ID2 AS VALUES2_ID2
FROM VW_VALUES AS VALUES1_T
LEFT JOIN VW_VALUES AS VALUES2_T
ON VALUES2_T.ID1 = VALUES1_T.ID1
1 2 1 2
3 4 3 4
5 6 5 6
しかし、簡単な構文でVIEWを作るのも面倒に感じるかもしれないので、そのときは WITH というもので一時的なテーブルを作成することもできます（※ただし、メモリに保存するため大量の行を出すような SQL だとメモリを圧迫して動作が遅くなることなることがあるため注意）。
WITH WI_VALUES AS
(
SELECT *
FROM ( VALUES (1,2),(3,4),(5,6) ) AS VALUES_T(ID1,ID2)
)
SELECT
VALUES1_T.ID1 AS VALUES1_ID1,
VALUES1_T.ID2 AS VALUES1_ID2,
VALUES2_T.ID1 AS VALUES2_ID1,
VALUES2_T.ID2 AS VALUES2_ID2
FROM WI_VALUES AS VALUES1_T
LEFT JOIN WI_VALUES AS VALUES2_T
ON VALUES2_T.ID1 = VALUES1_T.ID1
1 2 1 2
3 4 3 4
5 6 5 6
ちなみに、WITH は他にも一つの値から次の値を連続して作成することもできまして、以下のようにすると今年のカレンダーを作ることもできます（再帰的SQLとも呼ばれるもので、ここでOPTIONは上限がデフォルトだと100までしか出せないため設定しています）。
WITH WI_CALENDAR AS
(
SELECT CAST( CONCAT(DATEPART(YEAR,GETDATE()),'/01/01') AS DATE ) AS THIS_YEAR_DATE
UNION ALL
SELECT DATEADD(DAY,1,THIS_YEAR_DATE) AS THIS_YEAR_DATE
FROM WI_CALENDAR
WHERE THIS_YEAR_DATE < DATEADD(DAY,-1,CAST( CONCAT(DATEPART(YEAR,DATEADD(YEAR,1,GETDATE())),'/01/01') AS DATE ))
)
SELECT
THIS_YEAR_DATE,
(CASE DATEPART(WEEKDAY,THIS_YEAR_DATE)
WHEN 1 THEN '日曜日'
WHEN 2 THEN '月曜日'
WHEN 3 THEN '火曜日'
WHEN 4 THEN '水曜日'
WHEN 5 THEN '木曜日'
WHEN 6 THEN '金曜日'
WHEN 7 THEN '土曜日'
END) AS WEEK_DAY
FROM WI_CALENDAR
OPTION (MAXRECURSION 500)