SQLめも

MySQL 5で動作確認。Postgresもいけるらしい

以下のようなSQLをプリペアドステートメントで使いたい、でも場合によっては検索条件は全部使わない。

SELECT * FROM TBL_A WHERE (hoge = ? AND fuga = ? AND piyo = ? ) 

プログラム中で文字列継ぎ接ぎしてSQLを組み立てることをせずに、使わない条件を無視させることができる。

SELECT * FROM TBL_A WHERE (hoge = ? AND fuga = ? AND piyo = ? ) IS NOT FALSE

WHERE句を()で囲みIS NOT FALSEを付ける。検索に使わない条件には、NULLをセットする。

piyoを検索に使わないときは、piyoの比較対象にNULLをセットする。すると、piyo = NULL は unknown と評価される。

piyo以外の条件がtrueとなった場合

 (true AND unknown) IS NOT FALSE

となり、(true AND unknown) = unknown であるから、unknown IS NOT FALSE となり全体はtrueとなる。

piyo以外の条件がfalseとなった場合

 (false AND unknown) IS NOT FALSE

となり、(false AND unknown) = false であるから、false IS NOT FALSE となり全体はfalseとなる。

プログラミング言語において一般的に真偽値はtrue、falseの２つの値を取る。SQLの場合true、false、unknownという3つの値がある。unknownはある値とNULLを比較演算した場合に発生する。例えば NULL = 1 や NULL > 1 は unknown である（MySQL等ではunknownはNULLで代用されているようだ）。

true false unknownから成る論理系を３値論理という。

unknownとの論理演算

unknownとのand演算とor演算の結果を覚えるのは面倒くさいが、unkwownを仮にtrueとfalseにそれぞれ置き換えた場合にどうなるかを考えると理解しやすい。

参考：3値論理 —— 神のいない論理

1対多でリレーションでは、ある1側のレコードに対して関連する多の側のレコードが0件であるケースがありえる。関連する多テーブルのレコードが0件であったり、少なくとも1件存在する１テーブルのデータを取得するのにEXISTS句が役立つ。

1側（modelsテーブル）

多側（gradesテーブル）

grades（多）にSがある models（１）のレコードを取る

SELECT * FROM models WHERE EXISTS (SELECT 1 FROM grades WHERE models.id = grades.model_id AND grades.value = 'S');

grades（多）にRが無い models（１）のレコードを取る

SELECT * FROM models WHERE NOT EXISTS (SELECT 1 FROM grades WHERE models.id = grades.model_id AND grades.value = 'R');

自己相関サブクエリを利用すると重複レコードが削除できる。

例えばvalueとuser_idについて重複しているレコードを削除するＳＱＬは以下のようになる。ただしユニークなidカラムがあること。

DELETE FROM hoge_table t1 
WHERE EXISTS (
    SELECT 1 
    FROM hoge_table t2 
    WHERE t1.user_id = t2.user_id 
    AND t1.value = t2.value 
    AND t1.id < t2.id
); 

ただしMySQL 5.0はinsert update deleteで操作対象と同一のテーブルを参照するサブクエリを使えないため、上記のSQLはエラーとなる。テンポラリテーブルを使って頑張ろう。

また、このSQLではuser_idとvalueに複合インデックスが無いとレコード数×レコード数のマッチングを行うため、とても時間がかかるので注意

各ユーザが最後に追加したレコードをまとめて取得する。ctimeをレコードを追加した時刻を格納したカラムとする

SELECT t1.id, t1.user_id, t1.value, t1.ctime 
FROM hoge_table t1
INNER JOIN (
    SELECT user_id, MAX(ctime) FROM hoge_table GROUP BY user_id
) AS t2 
ON t1.ctime = t2.ctime 
AND t1.user_id = t2.user_id;

インラインビューでテーブルを自己結合させるのがポイント。一度のSQLで各ユーザの最後のステータスを取得するといったことができる。（例：各部門が最後に発売した商品一覧を取得する）

上記のテーブルでnumの値1,2,3に対して、そのどれかに存在するchrを取得する場合（和集合）は

SELECT chr FROM t WHERE num IN (1,2,3) GROUP BY (chr);

上記のテーブルでnumの値が1,2,3それぞれの場合で、すべてに存在するchrを取得する場合（積集合）は

SELECT chr FROM t WHERE num IN (1,2,3) GROUP BY (chr) HAVING COUNT(*) = 3;

1,2,3という3つの要素に対してすべてに存在すると言う事は、レコードが3つあるということ。