SQLite の if not exist ライクな関数の実装

実装が必要。

if not exists(select * from ErrorConfig where Type='RetryWaitSeconds')
begin
  insert into ErrorConfig(Type,Value1)
  values('RetryWaitSeconds','3')
end

のみ使用します。

insert into ErrorConfig(Type,Value1)
select 'RetryWaitSeconds','3'
where not exists(select * from ErrorConfig where Type='RetryWaitSeconds')

SQLiteではSPがサポートされていないため

追記：NOT INはsqlite3ではうまく動作しません。

sqlite3でSQLに慣れてきた頃、どうしてもわからない問題にぶつかり、Googleで調べても答えが見つかりませんでした。たまたま遠回しに解決できたものの、とりあえず原理がよくわからないし、今のところ気力も限られているので、とりあえず記録しておいて、引き続き勉強することにします。

データベースはこのような感じです。

CREATE TABLE book (
 id integer primary key,
 title text,
 unique(title)
);
CREATE TABLE checkout_item (
 member_id integer,
 book_id integer,
 movie_id integer,
 unique(member_id, book_id, movie_id) on conflict replace,
 unique(book_id),
 unique(movie_id)
);
CREATE TABLE member (
 id integer primary key,
 name text,
 unique(name)
);
CREATE TABLE movie (
 id integer primary key,
 title text,
 unique(title)
);

データベースには、book, movie, member, checkout_item の4つのテーブルがあり、checkout_item は、book と movie に対するメンバーのチェックアウト記録を保持するために使用され、これらはリレーショナルテーブルである。

Q1: チェックアウトの記録がまだないメンバーは誰ですか？

SQL文(SQL1)は、以下の通りです。

SELECT * FROM member WHERE id NOT IN(SELECT member_id FROM checkout_item);

目的の結果を得ることができました。

Q 2: 貸出禁止図書は何ですか？

これは前回と似ているので、正しくは以下のSQL文（SQL2）を実行しました。

SELECT * FROM book WHERE id NOT IN(SELECT book_id FROM checkout_item);

しかし - 実行しても行が見つかりません! SQL2とSQL1では、この2つの文に違いは見られませんが、ブックテーブルの問題なのでしょうか？そこで、NOTを削除して、以下のクエリ文を実行しました。

SELECT * FROM book WHERE id IN(SELECT book_id FROM checkout_item);

チェックアウトされた本の数がブックテーブルの行の総数より少ないので、チェックアウトされていない本が確かに存在することになります。

その後、ググってみたのですが（ここではNGワードは省略）、解決策は見つかりませんでした。しかし、メンバーでは可能なのに、なぜブックでは不可能なのでしょうか？今までと何が違うのだろう？よくよく調べてみると、checkout_itemのbook_idとmovie_idはどちらもuniqueが付加されていますが、member_idはそうではありません。もしかしたら、これが原因でしょうか？idの代わりに、タイトルを変えてみてください。

SELECT * FROM book WHERE 
 title NOT IN( 
 SELECT title FROM book WHERE id IN(
 SELECT book_id FROM checkout_item));

非常に回りくどいですが、少なくともうまくいきました。

原因：NOTがNULLに触れたとき

実は、私自身の解決策は偶然の産物であり、問題はユニークとは関係ないのです。Qiu Juntaoの説明では、"SELECT book_id FROM checkout_item" の結果がNULL値を含んでいるため、NOTもNULLを返してしまうのだそうです。

解決策としては、checkout_itemでbook_idを選択する際に、null値を持つbook_idを削除することです。

SELECT * FROM book WHERE id NOT IN(SELECT book_id FROM checkout_item WHERE book_id IS NOT NULL);

概要

この問題を解くにあたって、私は間違った方向に進んでいたので、デバッガのように中間結果を確認するべきだった。例えば、次のようなステートメントを実行すると、空行を含む結果になってしまいます。

SELECT book_id FROM checkout_item

また、以下のようなステートメントを実行しても、空の行にはなりません。

SELECT member_id FROM checkout_item

これがSQL1文とSQL2文の実行の違いです。この違いによって、googleで検索すると、答えが見つかりやすいと思います。もちろん、NULLの概念がないのも困惑している理由です。

以上、個人的な経験ですが、参考にしていただき、Scripting Houseをもっと応援していただければと思います。もし間違いや不十分な考察があれば、遠慮なくアドバイスしてください。