テーブル結合のバリエーションを増やす：SQL実践講座（6）

[篠原光太郎，＠IT]

今回掲載の内容

• より複雑な結合に挑戦
• プライマリキーとフォーリンキー
• 関数の使い方
• もう1つのJOIN結合「OUTER JOIN」

より複雑な結合に挑戦

　今回は、少し複雑な結合の仕方と、結合のさらなるバリエーション（OUTER JOIN）について説明します。前回までは2つのテーブルの結合を例に説明しましたが、結合するテーブルの数は要求に応じて増やすことが可能です（Microsoft SQL Server 7.0の場合は、64個のテーブルまで結合できます）。

　では、前回の例をさらに進めて、顧客ごとのオーダーリストを作成してみましょう。

【例1】

SELECT cst.CustomerID CustID,
       cst.CompanyName Company,
       ord.OrderID OrderID,
       CONVERT(varchar,ord.OrderDate,11) OrderDate,
       ord.ShipCountry Country,
       odd.ProductID ProdID,
       prd.ProductName ProdName,
       odd.UnitPrice UnitPrice,
       odd.Quantity Qty
FROM Orders ord
  INNER JOIN "Order Details" odd on ord.OrderID = odd.OrderID
  INNER JOIN Products prd on odd.ProductID = prd.ProductID
  INNER JOIN Customers cst on ord.CustomerID = cst.CustomerID
ORDER BY Cst.CustID

画面1　顧客ごとのオーダーリスト（画面をクリックすると拡大表示します）

　随分、SQLが複雑になってきましたね。まずは、テーブル間の関係を図で解説しましょう。

プライマリキー（主キー）とフォーリンキー（外部キー）

図1　今回使用する4つのテーブル同士の関係（図版をクリックすると拡大表示します）

　例1では、4つのテーブルを結合しました。顧客マスタのCustomersテーブル、製品マスタのProductsテーブル、注文を管理するOrdersテーブルとOrder Detailsテーブルです。Customers、Products、Ordersにはそれぞれ、一番左の列に各データを代表するIDが保存されているのが分かります。これを、「プライマリキー（主キー）」と呼びます。Order Detailsは、OrderIDとProductIDが組み合わされて、各データの代表とすることが出来るので、OrderIDとProductIDが対でプライマリキーです。

　各テーブルの結合のキーを見てみると、OrdersとCustomersはCustomerIDをキーにして結合しています。同様に、OrdersとOrder DetailsはOrderID、Order DetailsとProductsはProductIDをキーにして結合しています。OrdersテーブルのCustomerID列のように、他のテーブルのプライマリーキーと対になってテーブルを結合する際のキーとなる列を、「フォーリンキー（外部キー）」と呼びます。

　これらの結合をSQLで記述したのが、例1のFROM句です。

FROM Orders ord
  INNER JOIN "Order Details" odd on ord.OrderID = odd.OrderID
  INNER JOIN Products prd on odd.ProductID = prd.ProductID
  INNER JOIN Customers cst on ord.CustomerID = cst.CustomerID
ORDER BY Cst.CustID

　この例の場合は、Ordersテーブルをスタート地点にして、それぞれ残りのテーブルと順にINNER JOIN句で結合をしています。On?の部分が、結合のキーとなるフィールドの指定です。

関数の使い方

　一方、SELECT句では、テーブル数が4つになりましたので、すべてのカラムにテーブルの別名で指定しています。例えば、cst.CustomerIDのcst.の部分が、Customersテーブルの別名です。cstがCustomersテーブルだ、という定義はFROM句に記述されています。

　 また、各カラムにも、分かりやすい別名を指定しました。これは、結果を表示させたときにカラムのタイトルとなります。CustomerIDに続くCustIDが、カラムの別名です。

SELECT cst.CustomerID CustID,
       cst.CompanyName Company,
       ord.OrderID OrderID,
       CONVERT(varchar,ord.OrderDate,11) OrderDate,
       ord.ShipCountry Country,
       odd.ProductID ProdID,
       prd.ProductName ProdName,
       odd.UnitPrice UnitPrice,
       odd.Quantity Qty

　さらに、4行目では、「CONVERT」という関数を使用しています。関数は、SELECT句などでカラムの指定をするときに使用することが出来ます。例1では、Ordersテーブルに保存されている発注日（OrderDate）を、"yy/mm/dd" といった一般的な日付の形式で表示させるため、CONVERT関数を使用しています。1つ目の引数は「どういう形式に変換させたいか」の指定です。この例では日付を文字に変換したいため、varcharを指定しています。2つ目の引数は、変換元のカラム名です。ここでは、発注日を指定しています。3つ目の引数は、日付の形式です。11は "yy/mm/dd" 形式を、111であれば "yyyy/mm/dd" となります。他にもヨーロッパ方式やUS方式も指定できますので、ヘルプを参照してください。

もう1つのJOIN結合「OUTER JOIN」

　それでは次の例に移りましょう。これまで説明してきた結合は、全て「INNER JOIN」というキーワードで実現をしてきました。INNER JOINのINNERとは、どういう意味でしょうか？

　これは、JOINのもう1つの形態である「OUTER JOIN」と対比して説明したほうがわかりやすいと思いますので、例を実行しながら確認していきましょう。

【例2】

SELECT *
FROM Customers cst
WHERE cst.CustomerID >= 'F' and cst.CustomerID < 'G'

画面2　顧客数を確認してみる（画面をクリックすると拡大表示します）

　このSQL文の結果から、CustomerIDの頭文字がFである顧客数は、8であることが分かります。では次に、Ordersテーブルと結合してみましょう。

【例3】

SELECT *
FROM Customers cst
INNER JOIN Orders ord on cst.CustomerID = ord.CustomerID
WHERE cst.CustomerID >= 'F' and cst.CustomerID < 'G'

画面3　INNER JOINの結果表示（画面をクリックすると拡大表示します）

　各顧客ごとに複数の注文があるため、同じ顧客が複数行表示されているのが分かります。ところで、ここに検索結果として表示された顧客の数はいくつでしょうか？ 数えてみると7ですので、先ほど実行したSQL文の結果である8顧客と合いません。顧客が1つ欠けてしまっています。その顧客IDは、調べてみると「FISSA」であることが分かります（この調査をSQLで実施する方法は、次回以降で説明します）。

　これは、FISSAから1つも注文が発行されていないことが原因です。つまり、CustomerIDがFISSAである注文データがOrdersテーブルに存在しないため、結合をした結果、FISSAはここにリストされないのです。これを、「INNER JOIN」（内部結合）と呼びます。

　しかし、場合によっては、注文が無い顧客は「ない」ということを明示した上で、リストには表示してほしいことがあります。これを実現するのが、「OUTER JOIN」（外部結合）です。

　次の例を実行してみましょう。

【例4】

SELECT *
FROM Customers cst
LEFT OUTER JOIN Orders ord on cst.CustomerID = ord.CustomerID
WHERE cst.CustomerID >= 'F' and cst.CustomerID < 'G'

画面4　OUTER JOINの結果表示（画面をクリックすると拡大表示します）

　今度は、FISSAが表示されましたが、注文は存在しないのでOrderIDは「Null」と表示されているのが分かります。

　例4では、「LEFT OUTER JOIN」としましたが、これは、左側のテーブル（ここではCustomersテーブル）に軸をおいて、すべて表示させる、という指定です。この例をRIGHT OUTER JOINにすると、全てのOrderを表示する、という指定になります。

　非常に便利な機能ですが、RDBMSの世界では値として扱われない「NULL」の列を生成してしまうため、扱いには十分気をつける必要があります。

今回のまとめ

今回は、「結合の応用」を紹介しました。次回は、SELECT文の入れ子であるサブクエリについて解説予定です

「次回」へ

「SQL実践講座」バックナンバー

• SQL Serverで「デッドロック」を回避する
• トランザクションの一貫性を保証するロック
• トランザクションを用いて注文登録をする
• トランザクションでデータの不整合を防ぐ
• テーブルで複数の処理を実行させるトリガー
• ユーザー定義関数を作成するストアドファンクション
• ストアドプロシージャによる繰り返し処理
• 条件分岐のあるストアドプロシージャ
• ストアドプロシージャの作成
• システム・ストアドプロシージャを用いたロールの詳細設定
• ロールを利用したグループ単位での権限設定
• SQL Serverのオブジェクトに権限を設定する
• Enterprise Managerによるビューの作成
• 作成したSELECT文をDBに登録する「ビュー」
• データの更新と主キーの重要性
• テーブル中のデータ識別に必要な主キーを定義する
• データの登録を行うINSERT文
• CREATE文をさらに使いこなそう
• CREATE文でテーブルを作成する
• SELECT文を統合する「UNION」
• サブクエリーの応用「相関サブクエリー」
• SELECT文の結果を抽出条件に使う
• テーブル結合のバリエーションを増やす
• テーブル結合の仕組みを理解する
• 異なるテーブル同士を結合する「JOIN」句
• 集計を行う「GROUP BY」句
• SELECT文で並べ替えを行うには？
• SQLの基礎 「SELECT」文を覚えよう