SQLiteと闘う：第一弾

こんにちは、和美です。

仕事で、Excel関数とSQLiteというデータベースシステムが使えるソフトを使って開発をしています。
SQLは不得意な上に覚えられないので調べたメモ書きです。
以前臨時上司から言われた、「 SQLからは逃げられない 」 という言葉を胸に頑張っています。

【 目次 】

1. 初めに
2. 差集合
3. 書く順番
4. 日付の変換
5. INNER JOINの書き方
6. SQLの整形
7. エラー表示
8. 最後に

1. 初めに

記事の内容にはほとんど関係ありませんが、実行環境はSQLite 3.24.0です。
なんでそんなマイナーなデータベースを使っているのかって？ 和美も文句言いたいです。
職場の方も「 なんでこんな変なデータベースを採用しているんだ？ 」 と首を傾げていました。
「 ExcelとAccessのいい所取り 」 を名乗るなら、AccessのAccess Database Engine（ 俗称：ACEデータベースエンジン ）でいいと思います。

2. 差集合

二つのテーブルのうち、片方にはないレコードを抜き出すのを、数学理論用語で 「 差集合 」 と呼びます。
（ 中学校辺りで習いましたね。A∪（カップ）Bとか ）
普通はデータベースによってEXCEPTかMINUSを使うのですが、LEFT OUTER JOINでも同じ結果が出てくるのは意外でした。
参考リンク：SQLで、複数テーブルから他方に無い(存在しない)レコードを抽出する：ぱーくん plus idea
望み通りの結果は取得できたものの、理由はあまり理解できていません ……。
この辺も参考にしました。
SQL not exists サンプルコード 2テーブルの片方にしかないデータを抽出：ポテパンスタイル
SQLで重複データを扱うサンプルコード集 カウント、集計、最新のみ抽出、重複禁止：ポテパンスタイル

3. 書く順番

SQLを書く順番に毎回悩んでしまいます。
SELECTの後はFROMが最初、順番を並べ替えるORDER BYが最後かな、とは分かるのですが、GROUP BYやWHEREなど、FROMの後に付け足されていく色々なおまけの部分です。
毎回同じ句で絞り込むわけではなく、検索条件を後から書き足していくので、WHEREとGROUP BYはどちらが先に書くべき？と検索する羽目になります。
正しい順番は以下の通り。

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

参考リンク：[SQL] SQL文の書き順 ～PostgreSQL Tutorialを使いながら解説～：Developers.IO
…… HAVINGなんて、使わないので用途すら忘れました。
確認したところ、GROUP BYでグループ化した後の検索条件のようですね。
参考リンク：SQL ServerのHAVING　グループ化の条件を指定する：SQLServer初心者でもスッキリわかる

4.日付の変換

SQLiteでは、日付・時刻をシリアル値かユリウス通日か何かで保存し、使っているシステムの表示はスラッシュ繋ぎというソフト内の謎の不一致で泣きそうになりました。
三年前の今日以降で絞り込む時、丸三日間ほど掛かりました。
…… 結局解決策を見つけたのは、和美ではなく上司です。

strftime('%s' , date(cast(cast(year(date()) AS  int)-3 as text) || '-'ont> || substr("0" || month(date() ), - 2, 2) || '-' || substr("0" || day(date() ), - 2, 2))) asont> year3

冒頭のstrftime('%s'で日付を秒に変換する強硬手段を使わない、もう少し綺麗な書き方はないものでしょうか。
…… 日付や時間を扱うことの多いシステムでSQLiteを利用するのはやめたほうがいいと思う。という主張は見かけましたが。
参考リンク：sqlite3でスラッシュ区切りの日付を扱う：あさはか備忘録
選べるなら和美は講習で使ったMySQLが良いですし、会社としては外部データベースに使う予定のORACLEの方が好みだと思います。

リンク先
OR検索とAND検索を同時に使う：LIG
WHEREで終了日が今日より前（終了済み）か終了日が空白（非使用）に絞り込む時に使いました。

日付と絞り込みの話はまた別の記事も書く予定です。
実はこの記事は、長いvs SQLiteシリーズの第一弾です。

5. INNER JOINの書き方

テーブル結合について、LEFT OUTER JOINになっているから検索結果が間違っていると指摘を受けました。
「 INNERは省略して、結合はWHEREに指定して... 」 と口頭で説明を受けたものの、理解できず。
改めて調べたところ、デフォルトがINNER JOINなので省略可能と知りました。
参考リンク：JOINするときにINNER or LEFT or RIGHT or FULLを省略するとどうなるだっけ？：babydaemons’ blog
ただ、参考リンク先の冒頭に「 他の人が書いたコードを修正する時に分からなくなって調べた 」 と書かれているので、省略するのは保守性に欠けると思います。

テーブル結合についての備忘録 その2：Qiita
INNER JOINとLEFT OUTER JOINの違いが覚えられません ……。

inner join と outer join の違いと覚え方の自分用まとめ(外部表、駆動表もメモ)：ぱーくん plus Idea

6. SQLの整形

お世話になっているリンク：SQLフォーマッターFor WEB：ドットツールズ
和美は入力が面倒なのでインデントが嫌いなのですが、保守性（ 自分が書いたSQLを他の人が変更する可能性 ）を考えるとインデントを入れた方が読みやすいのかな、と思ったので、SQL文が完成したらこちらで整形しています。
参考SQLを見ていると、カンマ整形は前区切りの方が多いようですね。和美は後ろ派です。
AND・ORは前、予約語は大文字、インデントはスペース2派です。でもasは小文字の方が読みやすいような気もします。
なお、上記リンクは CROSS JOINが改行される不具合があります。（ 2021年2月2日現在 ）

予約語を含めて全て小文字派の主張を読みましたが、今の仕事で、シンタックスハイライトが存在しないソフト附属のエディターを強制されています。
参考リンク：分析SQLのコーディングスタイル：クックパッド開発者ブログ

7. エラー表示

ずっとSQLで「テーブルの別名がエラー」と表示されて何だろうと試行錯誤をしていたら、項目の間にカンマがないのが原因でした。
エラーの原因は分かったのですが、一ヶ所しかないFROMでも悩むのに、該当箇所がいくつもある単語ではなく、もう少し長めに引用して特定できるようにしてほしい、と毎回思います。
和美が出すエラーは、項目の間のカンマがないか、項目の最後（次がFROM）なのにカンマがあるかのどちらかが多いです。
…… 前章のブログ執筆者のように、カンマを前区切り派に乗り換えたら良いのでしょうが …… 生理的に受け付けられません。

最後に

SQL関係のメモのうち、一本の記事にならなさそうな内容をまとめてみました。
当面続くvs SQLite関係の第一弾です。（ 現段階で第五弾まであります ）
データベースがマイナーなので知らない方も多いかとは思いますが、自分の備忘録を兼ねて書き続けていきます。