- 集計関数： SUM、AVG、MAX、MIN、COUNT
- グループ化： GROUP BY 句
- 複数テーブルの結合
- 内部結合： INNER JOIN
- GROUP BY 句と結合
- 外部結合： OUTER JOIN
集計関数： SUM、AVG、MAX、MIN、COUNT
集計関数
SQL では、集計関数を利用して、合計や平均の計算など、集計値を計算することができます。集
計関数には、主に次の 5 つの関数があります。
- まずは、SUM 関数を利用して、「社員」テーブルから、“社員全員の給与の合計金額” を取得してみましょう。
SELECT SUM(給与) FROM 社員 
このように、SUM 関数のカッコ内へ列名を記述すると、その列の合計値を計算できるように
なります。 - 次に、AVG 関数を利用して、“給与の平均金額” を取得してみましょう。
SELECT AVG(給与) FROM 社員 
このように、AVG 関数を利用すると、平均値を取得することができます。Note： 集計関数では NULL 値が無視される
SUM と AVG、MAX、MIN 関数では、NULL 値が無視されます。AVG 関数の実行結果を、もう一度確認してみます。
「社員」テーブルの内容は、次のようになっています。
このように、AVG 関数の結果は、NULL 値が無視されて計算されていることが分かります。もし、社員数で給与の平均を計算したい場合には、NULL 値のデータの社員も含める必要があるので、数値が入る列には、NULL 値の取り扱いに注意して、NULL 値を許可するかどうかをよく考えて、運用前にきちんとルールを作っておく必要があります。
なお、NULL 値があった場合に、それを別の値（0 など）へ変換して集計関数で処理させる方法もあります。これは「ISNULL」という関数を使って、次のように記述することができます。
ISNULL 関数の第 2 引数には、NULL 値だった場合に変換したい値を指定します。ここでは、0 を指定している
ので、給与の平均を 41.6666 万円として計算することができています。なお、ISNULL 関数は、Oracle では、
「NVL」という関数に相当します。 - 次に、MAX 関数と MIN 関数を利用して、“給与の最大値と最小値” を取得してみましょう。
SELECT MAX(給与), MIN(給与) FROM 社員 
- 次に、COUNT 関数を利用して、「社員」テーブルの行数をカウントしてみましょう。
SELECT COUNT(*) FROM 社員 
結果は、「6」が返り、社員テーブルの行数（6 件）を取得できたことを確認できます。 - COUNT 関数のカッコ内へ、列名を指定した場合は、NULL 値を除いたデータ件数をカウン
トすることもできます。これを「給与」列で試してみましょう。
SELECT COUNT(給与) FROM 社員 
「給与」列には NULL 値が 1 件（浅田さん）あるので、この値が無視されて、5 件とカウン
トされています。このように、COUNT 関数で、列名を指定した場合には、前述の Note と
同様、NULL 値を無視してカウントすることができます。
グループ化： GROUP BY 句
GROUP BY 句でグループ化
集計関数は、GROUP BY 句と一緒に利用することで、グループごとの集計値を計算できるように
なります。これは、次のように利用します。
| SELECT 列名1, 列名2, … |
GROUP BY 句でグループ化を行う列を指定し、HAVING 句で絞り込みを行う条件を指定すること
ができます。
Let's Try
- 次のように GROUP BY 句を記述して、“部門ごとの給与の平均値” を取得してみましょう。
SELECT 部門番号, AVG(給与) FROM 社員
GROUP BY 部門番号
部門番号 ”20” は、給与が NULL 値の「浅田 あさみ」さんしかいないので、結果が NULL 値になっています。 - そこで、次のように、部門番号 ”20” の社員を 2 人追加してみましょう。
INSERT INTO 社員 VALUES (7, '大久保 光', 250000, '2006/04/01', 20)
INSERT INTO 社員 VALUES (8, '小笠原 翔', 300000, '2006/04/01', 20)
- 社員データを追加後に、もう一度 “部門ごとの給与の平均値” を取得してみましょう。
今度は、部門番号 ”20” は、大久保さん（25 万円）と小笠原さん（30 万円）の平均なので、
27.5 万円と計算されたことを確認できます（NULL 値の浅田さんのデータは無視されていま
す）。 - 次に、COUNT 関数を利用して、“部門ごとの社員数” を取得してみましょう。
SELECT 部門番号, COUNT(*) FROM 社員
GROUP BY 部門番号
部門番号 ”10” は 5 人、”20” は 3 人という結果を取得できます。このように、GROUP BY
句は、COUNT 関数と一緒に利用することもできます。 - 次のように HAVING 句を利用して、“社員数が 5 人以上の部門番号” を検索してみましょう。
SELECT 部門番号, COUNT(*) FROM 社員
GROUP BY 部門番号 HAVING COUNT(*) >= 5
このように、HAVING 句を利用すると、集計結果に対して絞り込みが行えるので便利です。Note： WHERE 句と HAVING 句の絞り込みの違い
WHERE 句と HAVING 句は、取得するデータの絞り込みができるという点では同じですが、WHERE 句が、集計
演算をする前に絞り込みを行うのに対して、HAVING 句では、集計を行った結果に対して絞り込みを行うので、
取得結果の意味が大きく異なります。集計の前か後か、目的に応じて使い分けるようにしましょう。
複数テーブルの結合
複数テーブルの結合
SQL では、1 つのテーブルからだけではなく、複数のテーブルを結合して、結果を取得すること
ができます。
たとえば、今まで使用してきた「社員」テーブルでは、社員の「部門番号」を確認することはでき
ても、「部門名」を確認することはできませんでした。しかし、「部門」テーブルと結合することで、
社員が所属する「部門名」を取得できるようになります。
リレーションシップ
「社員」テーブルと「部門」テーブルには、次のような関係があります。
「社員」テーブルの「部門番号」から、「部門」テーブルの「部門番号」を参照することで、部門
に関する情報（部門名）を取得できるようになります。このようなテーブル同士の関係は、リレー
ションシップと呼ばれます。
また、「部門番号」のように、リレーションシップのある列のことを結合キー列といいます。
結合の種類
テーブルの結合には、内部結合（Inner Join）と外部結合（Outer Join）、自己結合（Self Join）
の 3 つの種類があります。以降では、これらについて説明します。
内部結合： INNER JOIN
内部結合は、リレーションシップをたどって関連情報を取得できる結合です。構文は、次のとおり
です。
FROM 句のテーブル名の後に、INNERT JOIN を記述して、結合したいテーブルを指定し、ON 句
の後に結合キー列を「=」で指定します。INNER JOIN の INNER は、省略することもできます。
Let's Try
- 「社員」テーブルと「部門」テーブルを内部結合してみましょう。
SELECT *
FROM 社員 INNER JOIN 部門
ON 社員.部門番号 = 部門.部門番号
INNER JOIN で「社員」と「部門」テーブルを指定し、ON 句で「部門番号」列を指定する
ことで、社員と部門を結合できています。
内部結合では、ON 句で「=」演算子を利用しているように、「社員」テーブルと「部門」テ
ーブルで「部門番号」が等しいものを取得しています。したがって、この結合は「等結合」や
「等価結合」とも呼ばれています。 - 次に、社員テーブルと部門テーブルを結合して、「氏名」と「部門名」列のみを取得してみま
しょう。
SELECT 氏名, 部門名
FROM 社員 INNER JOIN 部門
ON 社員.部門番号 = 部門.部門番号
- 次に、上の結果に加えて、"部門番号" を取得してみましょう。
SELECT 氏名, 部門番号, 部門名
FROM 社員 INNER JOIN 部門
ON 社員.部門番号 = 部門.部門番号
この結果は、「列名 ‘部門番号’ があいまいです」とエラーになります。SQL Server にとって
は、「部門番号」列が、「社員」テーブルの列なのか「部門」テーブルの列なのかを区別することができないので、このエラーが発生しています。
テーブル名.列名
結合するテーブルに同じ列名がある場合には、「部門.部門番号」のようにテーブル名の後に「.」（ドット）を付けて列名を指定するようにします。したがって、部門番号を取得するには、次
のように記述します。
今度は、正しく結果を取得することができました。このように、同じ列名がある場合には、「テ
ーブル名.列名」と指定しなければなりません。なお、「テーブル名.＊」のように、列名のと
ころへ「*」を指定した場合は、そのテーブルのすべての列を取得できるようになります。
Note： テーブル名に対する別名
テーブル名が長い場合には、テーブル名を何度も記述するのが面倒な場合があります。このような場合に、テーブル名に対して別名をつけることができます。これは、列名に対して別名を付けたときと同じように「AS」を利用して、次のように記述することができます。
これは、「社員」テーブルに対して「s」、部門に対して「b」という別名をつけています。また、「AS」は省略してもよいので、次のように記述することもできます。
なお、Oracle では、AS を利用したテーブル名に対する別名がサポートされていないので、上記のように AS を省略して別名を記述しなければなりません。
■ 別名利用時の注意点
テーブル名に別名を利用している場合には、ON 句の中や列名を列挙するときにも、別名を利用しなければならないことに注意する必要があります。
Note： クエリ デザイナーで結合演算を GUI 生成
Management Studio では、「クエリ デザイナー」という機能を利用すると、GUI 操作だけで結合演算を行うSELECT ステートメントを生成させることもできます。「クエリ デザイナー」を起動するには、次のように「クエリ エディター」上の任意の場所を右クリックして、［エディターでクエリをデザイン］をクリックします。
これにより、［テーブルの追加］ダイアログが表示されるので、［Ctrl］キーを押しながら、「社員」と「部門」を選択し、［追加］ボタンをクリックします。追加後、［閉じる］ボタンをクリックすると「クエリ デザイナー」画面が表示されます。ここで、取得したい列をチェックすれば、それらが選択リストに追加された SELECT ステートメントが自動生成されるようになります。
GROUP BY 句と結合
続いて、GROUP BY 句と結合を組み合わせて、より実践的な集計演算を行ってみましょう。
Let's Try
- まずは、「部門名」ごとの所属人数を取得してみましょう。
SELECT 部門名, COUNT(*)
FROM 社員 INNER JOIN 部門
ON 社員.部門番号 = 部門.部門番号
GROUP BY 部門名
社員テーブルだけでは、部門名を取得することができませんでしたが、部門テーブルと結合することで、部門名を取得し、部門名ごとの集計ができるようになっています。 - 次に、上の結果に加えて、部門番号も取得してみましょう。
結果はエラーになります。GROUP BY 句では、選択リストへ指定できる列が、GROUP BY へ指定した列のみという制約があります。したがって、部門番号を取得したい場合には、次のように GROUP BY 句にも、部門番号を記述しなければなりません。
SELECT 部門.部門番号, 部門名, COUNT(*)
FROM 社員 INNER JOIN 部門
ON 社員.部門番号 = 部門.部門番号
GROUP BY 部門.部門番号, 部門名
今度は、部門番号を正しく取得することができました。このように GROUP BY 句で複数の
列を取得したい場合には、その列も GROUP BY 句へ含めるようにしなければなりません。
Note： GROUP BY 句で複数列を指定するときの冗長回避
GROUP BY 句で複数列を指定する場合は、記述が冗長になるので、これを回避する方法として、MAX 関数または MIN 関数を利用する方法があります。たとえば、上記のクエリを MAX 関数を利用すると、次のように記述することができます。
GROUP BY 句では、「部門番号」列のみを取得し、MAX 関数で「部門名」を指定すれば、GROUP BY 句へ「部門名」を記述しなくて済みます。「部門名」のように「部門番号」列に従属した列（部門テーブル内の列）であれば、MAX 関数や MIN 関数を使っても、値は１つしかなく、最大値も最小値もその値しかないので、このような利用方法で結果を取得することができます。
外部結合： OUTER JOIN
内部結合では、結合するテーブル同士で、対応するデータ（等しいデータ）のみを取得するのに対
して、外部結合では、対応するデータがなくても（結合するテーブルの片方にしか存在しないデー
タの場合にも）、値を取得できるようになります。構文は、次のとおりです。
内部結合（INNER JOIN）との記述の違いは、LEFT または RIGHT を指定した OUTER JOIN で
ある点です。LEFT（左）を指定した OUTER JOIN は、「左外部結合」、RIGHT（右）を指定した
OUTER JOIN は、「右外部結合」と呼ばれます。
RIGHT OUTER JOIN（右外部結合）
- まずは、OUTER JOIN の結果が分かりやすくなるように、「部門」テーブルへデータを 1 件
追加しておきましょう。次のように入力して、部門番号「30」の「情報システム部」を追加
します。
INSERT INTO 部門 VALUES ( 30, '情報システム部' ) 
- 新しく追加した情報システム部には、まだ所属する社員がいません。この状態で、次のように「RIGHT OUTER JOIN」（右外部結合）を利用して、「社員」テーブルと「部門」テーブル
を結合してみましょう。
SELECT 社員.*, 部門名
FROM 社員 RIGHT OUTER JOIN 部門
ON 社員.部門番号 = 部門.部門番号
INNER JOIN（内部結合）では、部門番号が等しいデータのみを取得するので、所属社員のいない「情報システム部」を取得することはできません。これに対して、OUTER JOIN（外部
結合）では、対応するデータがない場合（結合するテーブルの片方にしか存在しないデータの場合）にも、結果を取得できるようになります。この例では、JOIN の右側（RIGHT）に指定
した「部門」テーブルにしか存在しないデータの「情報システム部」を取得することができて
います。また、このデータは片方にしか存在しないので、残りのデータは NULL 値で表示さ
れています。
LEFT OUTER JOIN（左外部結合）
- 次に、「RIGHT OUTER JOIN」を「LEFT OUTER JOIN」へ変更して、社員テーブルと部門
テーブルを結合してみましょう。
SELECT 社員.*, 部門名
FROM 社員 LEFT OUTER JOIN 部門
ON 社員.部門番号 = 部門.部門番号
結果は、INNER JOIN（内部結合） の場合と同じになります。LEFT と指定した場合は、JOINの左側（LEFT）に指定した「社員」テーブルにしか存在しないデータを取得できるようになりますが、社員テーブルにしか存在しないデータはないので、INNER JOIN と同じ結果になっています。 - 次に、結合するテーブルの左と右を入れ替えてみましょう。「社員」テーブルを右へ、「部門」
テーブルを左へ指定して結合してみます。
SELECT 社員.*, 部門名
FROM 部門 LEFT OUTER JOIN 社員
ON 社員.部門番号 = 部門.部門番号
今度は、JOIN の左側（LEFT） が「部門」テーブルになるので、「情報システム部」を取得
できたことを確認できます。
このように RIGHT と LEFT の違いは、JOIN の左と右のどちらにテーブルを記述するかど
うかだけです。
Note： 自己結合
結合には、自分自身のテーブルと結合する「自己結合」もあります。たとえば、次のようなemp（社員）テーブルがあったとします。
このテーブルには、上司の社員番号を格納している「上司社員番号」列があります。このような親子関係のあるデータが同じテーブルに格納されている場合は、自己結合が役立ちます。自己結合を利用すれば、次のように上司の氏名を取り出すことができます。
自己結合では、同じテーブル（emp）を INNER JOIN で結合できます。このとき、1 つには別名を付ける必要があるので、ここでは「manager」と付けています。あとは、結合条件を「emp.上司社員番号=manager.社員番号」とすれば、上司の氏名を取得できるようになります。
自己結合では、次のように 1 つのテーブルを 2 つのテーブルのように見立てて、結合を行うことができます。
0 件のコメント:
コメントを投稿