SQL_select文の結果の結合等(集合演算子)

今回は、select文の結果を結合する文法の紹介です。
結果を結合するためには、集合演算子のUNION(重複排除したい場合はUNION ALL)を用います。
運用作業やプログラミングで複数のselect文の結果を1回のSQL文発行で取得したいことがあるので、知っておくと便利です。

ついでに、UNIONやUNION ALL以外の集合演算子についても紹介します。

【文法】

※集合演算子には以下のようなものがある

・UNION

前後のselect文の結果を結合する。
重複する結果は1行にまとめられる。

・UNION ALL

前後のselect文の結果を結合する。
重複する結果はまとめられない。

・MINUS、EXCEPT

前のselect文の結果から後のselect文の結果を取り除く。
OracleはMINUS、それ以外はEXCEPTを用いる。
(MySQLではサポート外)

・INTERSECT

前のselect文の結果と後のselect文の結果で一致するものだけを抽出する。
(MySQLではサポート外)

※「order by」は個別のselect文にはかからず、集合演算子で結合した結果全体にかかる。

【対象テーブル例】

・A支店商品

・B支店商品

【使用例】

・UNION

・UNION ALL

・MINUS(EXCEPT)

・INTERSECT


いかがでしたでしょうか。

今回は、UNIONに代表される集合演算子について記事を書きました。
何回かに分けて基本的なSQLの書き方に関する記事を書いてきましたが、これが最後になります。
これまで書いてきた文法は何れも実務では頻出なので、運用作業に携わっている人は特に覚えておいた方が良いものばかりです。知らない・使い方があやふやな文法があれば、これを機に覚えることをお勧めします。

これからも、様々な角度から役に立つ記事を書いていきたいと思います!

SQL_副問合せ・相関副問合せ

今回は副問合せと相関副問合せの紹介です。
この文法を用いると、問い合わせの結果を別の問い合わせに使い回すことができるようになり、複雑な問い合わせを1本のSQLで書けるようになります。

副問合せは相関副問合せよりも文法的に簡単で、実務、特に運用作業では頻出です。
また、性能面を考慮すると相関副問合せを使った方が良いケースが多いため、相関副問合せもプログラミングでは良く使われます。

なお、今回は紹介しませんが、副問合せはFROM句にも書くことができます。

【文法】

・副問合せ(単一行を返す場合のみ使用可能)

・副問合せ(副問合せの何れかの結果と一致することを確認)

・副問合せ(副問合せのどの結果とも一致しないことを確認)

・副問合せ(副問合せの全ての結果と比較し、全ての結果よりも(大きい、小さい等))

・副問合せ(副問合せの何れかの結果よりも(大きい、小さい等))

※SOMEの代わりにANYを使用しても良い

・相関問い合わせ(副問合せ部で存在する行のみ抽出)

※上記は正式な文法ではないが、通常は上記のように使う
※EXISTSの前のNOTをつけると存在しない行のみ抽出することができる
※EXISTSの中のSELECT文のカラム指定は問わない(1でなくても良い)

【対象テーブル例】

・商品

・消費者テスト結果

・合格基準点

【使用例】

asは省略可能である。
実務でも試験でも使用頻度が高い文法のみ例を記載する。

・=演算子

・不等号演算子

・IN句

・EXISTS句

※上記のIN句の例と同じ結果となる

【性能について補足】

EXISTS句と同様のことはIN句でも可能だが、性能面で違いが出ることが多い。
細かい所はRDBMSによって異なるため、最終的にはRDBMSの特性を調べたり実行計画を取得したりしながらチューニングするべきであるが、一般的に以下のようなことが言える。

1.実行順の違い

原則として件数の絞り込みはなるべく早い段階で行うべきであり、少ない件数に絞り込むことができる条件は先に実行するべきである。
例えば、100件中10件まで絞り込める条件と、100件中90件までしか絞り込めない条件がある場合、100件中10件まで絞り込める条件を先に実行すれば後に実行される条件はその10件(以下)のみ対象とすれば良くなるので、その方が性能が良くなる。
IN句とEXISTS句ではこの実行順が異なり、先に副問合せが実行されるのがIN句、後で副問合せが実行されるのはEXISTS句なので、副問合せで条件を絞り込めるか否かでどちらの句を使うのかを決めるべきである。

2.インデックスの使用有無

IN句、特にNOT IN句については、インデックスを使用しない逐次検索が行われてしまうことが多い。
そのため、一般論として、インデックスが使われやすいEXISTS句の方が性能が良くなることが多い。


いかがでしたでしょうか。

今回は、SQLでの副問合せと相関副問合せについて記事を書きました。
プログラムでSQLを発行する場合は複数回問い合わせを発行してその結果をプログラム側で突き合わせることの方が多いかもしれませんが、運用作業では重宝します。

複数の問い合わせを1つの問い合わせで済ませる文法としては、UNIONのような集合演算子も良く使います。
これは次回取り上げたいと思います!

SQL_重複排除と集計

今回は重複排除(distinct句)と集計(group by)の紹介です。
どちらも挙動が似ているのでまとめて紹介します。

【機能説明】

・重複排除

select文の結果について、重複を排除することができる。

・集計

指定したカラムで集計を行うことができる。
(結果として重複排除もされる)
select文で集計関数を使用することができるようになる。
(該当行数を返すCOUNT関数、最大値を返すMAX関数、最小値を返すMIN関数等)
なお、having句でgroup byで集計した結果に対して条件で抽出することができる。
(where句も同じように抽出を行うが、where句は集計前に、having句は集計後に抽出する違いがある)

【文法】

・重複排除

・集計

【対象テーブル例】

・ご意見

【使用例】

asは省略可能である。

・重複排除(distinct句)

※店舗ID=1、商品ID=4の行は2行存在するが、重複排除され1行のみ出力された。

・重複排除(group by句)

※この例のように、order by句でdistinct句と同じように重複排除を実現できる。
 しかし、本来、order by句は後述の集計で使うので、重複排除のために使うのであればdistinct句の方が意図は伝わりやすい。
 また、distinct句とorder by句で実行時間に差が出る場合もある。

・集計(count関数)

※count関数の引数にカラム名を指定すると、そのカラムがNULLではない件数を返す。
 例えば、count(ご意見件数)なら、店舗ID=1、商品ID=4の行のご意見件数は1になる。

・集計(having句での抽出)


いかがでしたでしょうか。

今回は、SQLでの重複排除と集計について記事を書きました。
SQLではこのような操作も簡単に書くことができます。
特に、運用作業でデータ加工する時に重宝すると思います。

次回は、SQLを入れ子で実行する副問合せ・相関副問合せについて取り上げたいと思います!

SQL_取得結果に対する条件判定

SELECT文で取得した結果に対して、CASE式を使用することで条件判定が可能です。
条件判定を行うことで、SQL文の出力結果を変えることができます。
プログラムのIF文の判定をSQL文の中で行っているようなイメージであり、プログラムを作るまでもない運用作業で使用する他、プログラムに組み込む場合も使い方次第でプログラムの可読性・保守性を高めることができる場合があります。

なお、NULLの時に出力結果を変えるだけであればCOALESCE関数の方がスマートなので、これも併せて紹介します。

【文法】

・CASE式(単純CASE式)

SELECT~FROMの間のカラム指定の箇所で使用する。
カラムの値に応じて返す値を指定する。

・CASE式(検索CASE式)

SELECT~FROMの間のカラム指定の箇所で使用する。
式に応じて返す値を指定する。

・COALESCE関数

SELECT~FROMの間のカラム指定の箇所で使用する。
可変長の引数を持ち、NULLではない最初の値を返す。

【対象テーブル例】

・商品

【取得例】

※ASは省略可能


いかがでしたでしょうか。

今回紹介した文法も、日々の保守運用作業の役に立つものです。
WHERE句以外の箇所でも条件を指定できることを知ると、SQLでの表現の幅が広がり、作業が効率的になります。

SELECT文の結果については、結果として表示される値を変えるだけでなく、その値の重複を排除したり、集計したりすることもできます。
こちらについては次回の記事で紹介したいと思います!

SQL_取得結果に対する演算子の利用

SELECT文で取得した結果に対して、演算子を使うことで文字列結合や計算が可能です。
これを使うことで、DBから抜き出してきたデータを他人に見せる時の加工が用意になります。
また、プログラムで実装していたロジックをSQL文に組み込むことが可能になりますし、副問い合わせで使用すれば「抽出→抽出結果をプログラムで加工→加工した結果を用いて再度抽出」としていたものを1つのSQL文にまとめることもできます。

【文法】

・文字列結合

演算子「||」を用いることで前後の文字列の結合が可能。
例えば、「商品名||’_特価品’」とすれば、商品名の取得結果の後ろに「_特価品」という文字列を追加し出力することができる。

・計算

算術演算子(+,-,*,/,%)を用いることで取得結果の計算が可能。
例えば、「売値 * 0.8」とすれば、売値の取得結果について0.8をかけた値を出力することができる。

【対象テーブル例】

・商品

【取得例】

※ASは省略可能


いかがでしたでしょうか。

SQLのSELECT文で取得した結果を加工できるのは意外だったかもしれませんが、特に日々の保守運用作業が容易になるメリットが大きいので、覚えておいて損は無い文法です。

なお、取得した結果を加工する代表的な方法としては、CASE文もあります。
こちらについては次回の記事で紹介したいと思います!