■共通事項

マニュアルや教則本を読んでいて、何のために必要な機能なのか、パッと見、 読み取れない事ってあると思います。
詳細はマニュアル等を紐解いてもらうとして、ここでは、殆どの RDBMS について共通する言葉の説明をザックリと 書きます。また RDBMS とは直接関係ないですが、後述する例題で使用する言葉（正規表現など）についても、 ここで説明します。
 
SQLについて
ANSI/ISO SQL92, SQL99 とは？
UNIONとは？
等価結合とは？
自己結合（セルフジョイン）とは？
外部結合（アウタージョイン）とは？
サブクエリー（副問合わせ）とは？
相関サブクエリー（相関副問合わせ）とは？
FROM句のインラインビューとは？
動的SQLとは？
 
パフォーマンスについて
正規化しないとどのような時に困るのか？
非正規化（正規化の解除）してもよい場合は？
オプティマイザとは？　実行計画（プラン）とは？
複雑なSQLを書くのは結構楽しい。…だが！
とにかくインデックスを張れば速くなる？
とにかくアナライズ（統計情報の取得）をすれば速くなる？
とにかくストアドプロシージャ化すれば速くなる？
 
その他
クロス集計とは？
正規表現とは？
 
サンプルテーブルについて
各項で使用するサンプルテーブルについて

ANSI/ISO SQL92, SQL99 とは？　▲TOP

SQL の仕様は ISO（国際標準化機構）及び ANSI（アメリカ国家規格協会）で制定されます。 その仕様は、制定された年を付加して SQLxx（例えばSQL92など）と言った呼び方をします。
今、通常使われているSQLの殆どはSQL92までに制定されたもので、それ以降、ストアドプロシージャ やLOB、Javaとの連携、外部ファイルとの連携などが加わり、2001年現在の最新版は、SQL99 （SQL3 のコードネームで知られる）です。
RDBMS と呼ばれるものの殆どは、この ANSI/ISO SQL に準拠していますが、その度合いはまちまちです。 また、ANSI/ISO の機能を満たしていても、構文が異なったりする場合もあります。例えば、表の外部結合において、 ANSI では OUTER JOIN句を使用しますが、オラクルには OUTER JOIN句がありません。 しかし、当然の事ながらオラクルで外部結合は可能です。
 
※ORACLE 9i からは、OUTER JOIN句を使えます

UNIONとは？　▲TOP

複数の結果セットを連結して表示する時に使います


Ａ表 番号 名前 ０００１ 山田 ０００２ 田中

Ｂ表 番号 名前 ０００３ 鈴木 ０００４ 佐藤

この２表の内容をすべて表示したい　→

結果 番号 名前 ０００１ 山田 ０００２ 田中 ０００３ 鈴木 ０００４ 佐藤

等価結合とは？　▲TOP

結合条件として指定した列の値が同じ行を表示する時に使います



Ａ表 番号 名前 ０００１ 山田 ０００２ 田中

Ｂ表 番号 名前 ０００１ 山田 ０００４ 佐藤

この２表から番号の同じ行だけ表示したい　→

結果 番号 名前 ０００１ 山田

自己結合とは？　▲TOP

同じ表を結合して、別の行に存在する値を表示する時に使用します。



Ａ表 番号 名前 役職 ０００１ 山田 部長 ０００２ 田中 スタッフ ０００３ 鈴木 スタッフ ０００４ 佐藤 スタッフ

この表から、スタッフの横に上司の名前を表示したい　→

結果 番号 名前 上司 ０００１ 山田   ０００２ 田中 山田 ０００３ 鈴木 山田 ０００４ 佐藤 山田

外部結合とは？　▲TOP

結合条件に一致しない行も含めて表示する時に使います



Ａ表 番号 名前 ０００１ 山田 ０００２ 田中

Ｂ表 番号 名前 金額 ０００１ 山田 １０００ ０００４ 佐藤 ２０００

この２表から番号の同じ行を結合し、 　Ｂ表の金額を表示したいが、 　Ａ表の名前はすべて表示したい

→

結果 番号 名前 金額 ０００１ 山田 １０００ ０００２ 田中

サブクエリー（副問合わせ）とは？　▲TOP

SELECT文の where 句や having 句に入れ子式に含まれる SELECT文をサブクエリと言います。
 
（１） select 部署ID from 部署 where 部署名 = '営業部' -- 営業部の部署IDは '01' である事がわかった
（２） select * from 社員 where 部署ID = '01' -- 部署IDを使って営業部のデータを取得
 
　↓　これは以下の様に一つにまとめられます。カッコ内がサブクエリーです。
 
select * from 社員 where 社員.部署ID = (select 部署ID from 部署 where 部署名 = '営業部')

相関サブクエリー（相関副問合わせ）とは？　▲TOP

サブクエリーの中でも、入れ子の親と子のクエリーに相関関係がある場合を言います。
以下は、売上のある社員の一覧を表示する例です。相関サブクエリーの場合、 カッコ内のサブクエリーのみでは機能しません（エラーになります）。
 
select 社員.* from 社員 where 社員番号 in
(select 社員番号 from 売上
where 売上.社員番号 = 社員.社員番号) -- ←親クエリーの列と結合している

FROM句のインラインビューとは？　▲TOP

ANSI ではFROM句の中にもサブクエリーを書ける事になっています。
これは、ある条件のクエリーを、あたかも定義済みのビューとして使える事になるので、非常に便利です。
これまで述べてきた事は、殆どの RDBMS で実装されていますが、この FROM句のインラインビュー については、RDBMS の種類やバージョンによって、実装されていたりいなかったりするので注意が必要です。
 
select * from (select * from 社員 where …)

動的SQLとは？　▲TOP

アプリケーションから実行されるSQLが常に固定である場合、これを静的SQLと言います。これに対し、 条件などによりSQL文をアプリケーション側で生成して実行する場合を動的SQLと言います。
動的SQLは、実行条件の違いによる過剰なモジュール分割を避けるのに有効ですが、常に前回と違う SQL が流される可能性が大きいため、サーバー側での解析負荷も高くなります。

正規化しないとどのような時に困るのか？　▲TOP

正規化とは、テーブルを設計する上で、データの重複や冗長性を排除する事を指します。
と、ここまではよく目にする言葉だと思います。では、『何故しないとダメなのか？しないとどう困るのか？』 を、１つのわかりやすい例を挙げてみます。
 

◎社員の売上を日付単位で表示する必要がある
　↓
そこでこんなテーブルを作ってみた


日付	社員CD_001	売上_001	社員CD_002	売上_002	社員CD_003	売上_003	・・・・	社員CD_100	売上_100
2001/04/01	２	10000	３	20000	４	12000	・・・・
2001/04/02	６	35000	７	12000	８	50000	・・・・

社員名を持つと長くなりすぎな気がしたので、氏名は社員マスタを社員コードで検索しよう。主キーだから高速なはずだ
　↓
社員名を表示するためには社員CD_001～100まですべてに結合が必要。めちゃめちゃ冗長！
　↓
ＳＱＬ文が冗長過ぎてクライアントアプリケーションから実行できる限度を超えてしまった！
　↓
やむなく社員名取得用のストアドファンクションを作る事に。だが！
　↓
１行あたり100回も社員マスターを見に行くので主キーでの検索とは言え遅い！
　↓
そこへ、社員番号でソートして表示して欲しいとのユーザーの要望が！む、無理だ！
　↓
何とか本番運用に入ったが、社員数が100人を超えてしまった。テーブルもアプリケーションも作り直し！
 

この例での教訓は、『とりあえず横にダラ～ッと繰り返し列を持つな』って事です。 手慣れた人は「こんなバカな設計しないよ」と思われるでしょうが、実際に仕事をしていて、 こう言うテーブルって結構見かけたりするものです。

非正規化（正規化の解除）してもよい場合は？　▲TOP

では非正規化（正規化の解除）してもよい場合と言うのはあるでしょうか。
カリカリに正規化したテーブルは、机上では理想的なのですが、実はアプリケーションを作る上で、却って面倒だったり、 パフォーマンスが上がらなかったりする場合があります。 後述するクロス集計クエリーなどがよい例です。ANSIのCUBEやROLLUPを使わずにクロス集計を実装するためには、 SQLの中で分岐と集計をいっぺんに行わなければなりません。これはかなりコスト高なクエリーになります。
あるいは、クロス集計を SQL１文でどう実装するのかがわからずに、 アプリケーションレベルでどうにかしてしまう場合もあるでしょう。
 
例えば、社員単位の月別一年計程度であれば、横に12ヶ月分の繰り返し列を持つテーブルを作ってしまった方が、 重いSQLを流したり複雑な処理系を作成したりせずに済む可能性が高くなります。 但し、どのレベルで正規化を解除をするかは慎重な判断が必要です。

オプティマイザとは？　実行計画（プラン）とは？　▲TOP

RDBMS にはオプティマイザと呼ばれる機能があります。これは、SQLを実行する際に『インデックスを使った方がよいのか』 とか『どうやって結合するか』とか『どの順番で結合するか』と言った『最適な検索方法』を導き出すための機能です。
オプティマイザが導き出した『最適な検索方法』が実行計画（プラン）です。RDBMS はこのプランに基づきデータの検索を行います。

複雑なSQLを書くのは結構楽しい。…だが！　▲TOP

SQL を書く事に慣れてくると、複雑な要件を１つの文で実装できるような SQL が書けるようになります。
それらは、UNIONやHAVING、相関サブクエリー、外部結合と言ったものを総動員して、ようやく可能になるかも知れないし、 場合によっては、プログラム並みに長大になる事だってあるでしょう。
こうして解いたパズルが意図した結果を返してくれたら、それは結構嬉しい事です。 しかし、これは、ある程度スキルを積んだ人が陥りやすい問題を孕んでいます。
 
前述の通り、オプティマイザは彼の考え得る『最適な検索方法』を導き出してくれますが、それがアプリケーションにとっては必ずしも『最適』でない場合があるのです。
 
オプティマイザは、決め事に基づき、あるいは統計情報を参照して、あくまでも定量的にプランを決定しています。
複雑な SQL は、業務やデータ量を把握している人間が意図したものと異なるプランになってしまう事が多々あります。 特にバージョンの古い RDBMS ほど、オプティマイザが混乱しやすいと考えた方がよいでしょう。
 
正規化（かつ適度な非正規化）がなされたテーブルを使用し、過度な複雑性や冗長性を排した比較的単純な SQL の組み合わせのみで作られたアプリケーション。 これが理想です（ま、そうは言っても、実際には顧客要望を受け入れて行く過程で、 なし崩しになってしまう部分も多いんですが）。但し、SQLパズルを解く能力が不要とは決して思いません。 それは『いざとなれば、いつでも抜けるナイフ』として研いでおくべきものでしょう。

とにかくインデックスを張れば速くなる？　▲TOP

掲示板などでよく見掛ける『インデックスを張ったのに遅い』についてです。
これについては、まず前述のオプティマイザが参照するための統計情報がないために、 インデックスを認識していない場合があります。
 
もう１点。インデックスは（よく説明で使われる例ですが）、RDBMS の検索において、 書籍の索引と同様の機能を提供するためのものです。 書籍の索引で、ある項目についてのページが１個所しかなければ、その項目の情報は１個所見れば得られますが、 複数ページに渡っていたら、自分が必要とする情報を得るために、何個所も見る必要があります。
 
これと同じ事が、テーブルのインデックスにも言えます。例えば、男：５０人、女：５０人の社員マスタの 性別フラグにインデックスを作成し、統計情報を取得したとします。このように値が２種類しかない場合、 殆どのオプティマイザはインデックスを使わないでしょう。何故なら、全体の半分（もしくはそれ以上） のデータを検索するのに索引を使用するのは却って非効率だ、と判断するからです。上記の書籍の例で言えば、 索引で『男』と引いた時に、関連する項目が100ページある内の50ページに渡っていたとしたら、 それを索引を見ながら１ページずつ繰って行く人はあまりいないでしょうし、 そもそもそんな索引の付け方をする編集者もあまりいないでしょう、って事です。
 
このような列の値の種類の数（DISTINCT値）をカーディナリティと言います。この場合のカーディナリティは、 ２なので、かなり『カーディナリティが低い』と言えます。 反対に主キーのようなユニークなインデックスは非常に 『カーディナリティが高い』という事になります。

とにかくアナライズ（統計情報の取得）をすれば速くなる？　▲TOP

これも掲示板などでよく見掛ける『アナライズしたら遅くなった』についてです。
アナライズコマンド（方法は RDBMS によって異なります）は、検索を速くするためのおまじないではありません。 統計情報の取得の目的は、オプティマイザに正しいデータ量やインデックスの有無や分布を教えてやる事です。 それによって、どう判断するかは、オプティマイザ次第なのです。
前述の『複雑なSQLを書くのは結構楽しい。…だが！』でも書きましたが、複雑なクエリーの場合、 オプティマイザは、しばしば開発者が意図せぬプランを提示します。そんな時は、 統計情報を削除して『まだましだったアナライズ前』に戻すか、 覚悟を決めて SQLをチューニングしていく必要があります。

とにかくストアドプロシージャ化すれば速くなる？　▲TOP

これまた掲示板などでたまさか見掛ける『ストアドにしたのに速くならない』についてです。
この中で多いと思われるのが、 『単純にアプリケーションからSELECT文を発行するよりもストアドの中でSELECTした方が速いらしい』 と言う勘違いでしょう。
オラクルの場合、PL/SQLブロック内で使うSELECT文には、カーソルやサブクエリーを除き INTO が必須なので、ここでは、 SQL Server系の Transact-SQL を例に取ります。
 

クライアントアプリケーション側の処理
'SELECT文の生成
sSQL = "select col1... from hoge where col2=" & 変数a
If 変数a = ... Then
　　'更新処理
　　If 変数b = ... Then
　　　'SELECT文の実行
　　　ダイナセットオープン（sSQL）
　　　Do Until ダイナセット.EOF
　　　　ダイナセット.Edit
　　　　ダイナセット("col1") = 変数b
　　　　ダイナセット.Update
　　　　ダイナセット.MoveNext
　　　Loop
　　　ダイナセットクローズ
　　End If
Else
　...
End If
 
この処理が遅いとの理由から、SELECT文だけストアド化した
　↓
ストアドプロシージャ
CREATE PROCEDURE sp_hoge
@icol int
AS
select col1... from hoge where col2=@icol
/

 
残念ながら、これでは殆ど何も解決しないでしょう。
まず最初に、SELECT文自体にチューニングの余地があるかどうかを検討すべきです。
 
SELECT文単体での検索に問題がない事が確認できたら、次に更新回りの処理をチェックします。
ミドルウェア側の操作メソッド（"Edit"，"Update"，"MoveNext" etc.）を使った場合、RDBMS側の DML （データ操作言語）には、この様なメソッドが存在しないので、 ネイティブなSQLへの変換が必ず行われます。
この例の場合はUPDATE部分をExecuteSQLに変更してネイティブなSQLを実行する などの対処が考えられるでしょう。
 
これでもなお、遅い場合、それはつまり、クライアントアプリケーション側で、 冗長なトランザクションを行っている事が問題の一因と考えられるので、ここにおいて始めてストアド化を 検討します。それもSELECT文だけを含めるのは意味がないので、トランザクション全体（上記の例で言えば、 SELECT文の生成からIF文まですべて）をストアド化します。
 
※もっともこの手の（クライアント側でLoopさせるような）更新処理は大抵、即座にストアドにしてしまう事が多いです。

クロス集計とは？　▲TOP

要するに縦横変換して集計する事です。
 


Ａ表 番号 名前 売上 ０００１ 山田 １０００ ０００２ 田中 １５００ ０００１ 山田 ２０００ ０００３ 鈴木 ２０００ ０００３ 鈴木 １５００ ０００４ 佐藤 １０００ ０００２ 田中 １０００

この表から、社員毎の売上を横に表示したい　→

結果 山田 田中 鈴木 佐藤 ３０００ ２５００ ３５００ １０００

正規表現とは？　▲TOP

正規表現は、grepコマンドや sed awk perl などのテキスト操作ツールで使われる、 検索・置換のための文字列パターンを表わす手法です。
多くのコマンドやツールで実装されており、普通の曖昧検索よりもはるかに自由度の高い検索が可能になります。 RDBMS とは関係ありませんが、別項で Like 使用時の正規表現について述べるので、知らない人は『そーゆーものがある』 と認識しておいてください。

各項で使用するサンプルテーブルについて　▲TOP

以下のスクリプトは、架空の会社『サン東京デンチャーズ株式会社』のデータを作成します。
サン東京デンチャーズは、東京支店と大阪支店があり、社長以下、社員は11人。 謎の何物か（多分何かの機械）を販売している会社です。
 
サンプルテーブル作成スクリプト（ORACLE用）
サンプルテーブル作成スクリプト（MSSQL用）
サンプルテーブル作成スクリプト（SSA用）
サンプルテーブル作成スクリプト（Postgres用）
サンプルテーブル作成スクリプト（MySQL用）
サンプルテーブル作成スクリプト（Jet用）
 
※スクリプトファイルはすべて SJIS かつ改行はCRLF
※Jetについてはバッチで実行するツールを鋭意作成中
 
作成されるテーブルは
 
社員マスタ：kemp
部署マスタ：kdept
顧客マスタ：kcust
商品マスタ：kprod
売上テーブル：ksale
 
です。DROP文をコメントアウトしてありますので、既存のオブジェクトを消してしまう事はありません。 再作成する時はDROP文のコメントアウトを外してください。