関係データベースの操作とSQL

要点と定義

関係データベースの操作は、表の行や列を取り出したり、複数の表を関連付けたり、データを追加・変更したりする処理です。関係代数では、条件に合う行を選ぶ「選択」、必要な列を取り出す「射影」、共通する列の値を手掛かりに表を結び付ける「結合」などで表します。例えば、学生表と学部表を学部コードで結合し、情報学部の行を選択した後、学部名・学生番号・氏名を射影すれば、条件に合う学生の必要な情報が得られます。選択は行を絞り、射影は列を絞る演算で、役割を取り違えないことが大切です。

SQLでは、SELECT文で検索し、INSERT文で行を追加し、UPDATE文で既存行を変更します。検索ではFROMで表を指定し、WHEREで行の条件を絞ります。複数表を使うときは、関連するキー列の一致条件を指定して結合します。例えば、保護者表と児童表を保護者番号で結び、学年が6の児童に条件を絞って保護者の氏名と住所を取り出します。同じ保護者が複数の対象児童を持つ場合に結果の重複を避けるには、SELECT DISTINCTを使えます。単に複数表をFROMに並べても、結合条件がなければ無関係な行同士まで組み合わさり得ます。

結合は、どの行を結果に残すかにも注意が必要です。内部結合は両表で対応する行があるものだけを返します。外部結合は指定した側の表の行を残し、相手側に対応行がない場合は相手側の列をNULLにします。申込みをすべて残し、決済記録がまだない申込みも調べるなら、申込み側を基準にした左外部結合が使えます。未決済は決済額がNULL、決済済みは0以上、取消済みは−1というように、値の意味と行の有無を分けて判定します。

決済表にまだレコードがない申込みを探す場合は、販売IDが決済表に存在しないことを副問合せのNOT INなどで調べられます。これは、決済表にレコードはあるが決済額が特定値であるケースとは異なります。処理時点も条件に影響します。取消レコードが期限日の翌日に作られる運用なら、期限前の未決済申込みにはそのレコードがまだ存在しません。

集計では、SUMなどの集約関数で値をまとめ、GROUP BYで集計単位を定めます。商品番号ごとの販売数なら、明細表の商品番号ごとに個数を合計すればよく、必要な列を持つ表だけで集計できます。ビューは問合せ結果を表のように扱う定義で、必要最小限の表を選ぶことが、意図した集計や表示の土台になります。一度でも入荷した商品を在庫ゼロでも表示するには、入荷集計を基準に販売集計を組み合わせれば、販売がない商品も結果に残せます。

既存行の列を変更するときはUPDATEを使い、SETで更新する列と値を指定し、WHEREで対象行を絞ります。金額帯など複数条件に応じて値を決めるには、CASE WHENで条件分岐できます。例えば決済額が2万円以上なら3％、1万円以上なら2％、それ未満なら1％を掛け、FLOORで小数部分を切り捨てて整数ポイントにする処理です。条件は大きい範囲から並べると、上限のない条件に先に一致してしまう誤りを防げます。

結合の実行方法の一つであるソートマージ結合法は、両表を結合列の値で並べ替え、先頭から順に値を比較して一致する行を結び付けます。並べ替えた順序を使って走査するため、どの値を照合しているかを追いやすい方法です。

埋込みSQLで検索結果を一行ずつ処理する際は、カーソルを使います。カーソルは検索結果の現在位置を指す仕組みです。宣言して処理対象を定め、OPENで開始し、FETCHで行を読み込み、必要なら現在行を更新し、CLOSEで終了します。プログラム言語側の変数に値を受け渡しながら、複数行を順に扱う場面で役立ちます。

適用条件と例

どの操作を使うかは、欲しい結果を行・列・表の関係に分けて考えると決めやすくなります。例えば、特定の学部の学生名だけが必要なら、まず学部コードで学部表と学生表を結合し、学部名の条件で行を選択し、最後に必要な列を射影します。条件だけで表の関連付けができるとは限らないので、結合キーの対応も確認します。

重複排除と集計は別の目的です。対象行の列値が重なる結果を一つにしたいならDISTINCT、値を合計・件数化したいなら集約関数とGROUP BYを検討します。集計表を作るときは、集計値を持つ明細と集計単位を識別できる列を選びます。不要な表を結合すると、行数が増えたり、合計を二重計上したりすることがあります。

未対応行を含める必要があるかどうかで結合の種類を決めます。両表に対応行があるデータだけなら内部結合、片側の全行を残して相手がない場合も扱うなら外部結合です。例えば購入申込みと決済記録を照合し、申込みを漏れなく判定するには、申込み表を左側に置いた左外部結合が適します。決済額がNULLの場合は相手行がない可能性があり、NULLとの通常の等号比較ではなくIS NULLで判定します。

ビューや在庫のように、表示対象を漏らさないことが条件なら、どの表を基準にするかを先に決めます。入荷した商品をすべて表示する要件では、入荷集計を基準にし、販売集計を組み合わせて入荷総数から販売総数を差し引きます。入荷はあるが販売がない商品も在庫ゼロまたは入荷数のまま表示できるよう、結合方法とNULL時の扱いを設計します。