Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (日本語) » SQL Anywhere サーバ - SQL の使用法 » データのクエリと変更 » ジョイン:複数テーブルからのデータ検索 » 特殊なジョイン

 

ジョインで重複する相関名 (スター・ジョイン)

重複するテーブル名は、「スター・ジョイン」を作成するために使用します。スター・ジョインでは、1 つのテーブルまたはビューが複数のテーブルやビューにジョインされます。

スター・ジョインを作成するには、同じテーブル名、ビュー名、または相関名を FROM 句内で 2 回以上使用します。これは、ANSI/ISO SQL 標準の拡張機能です。重複名を使用しても機能は追加されませんが、使用すると特定のクエリを簡単に作成できます。

重複名は、構文が意味をなすように、必ず異なるジョイン内に置きます。同一ジョイン内でテーブル名やビュー名を 2 回使用すると、2 番目のインスタンスは無視されます。たとえば、FROM A,AFROM A CROSS JOIN A は、どちらも FROM A と解釈されます。

次の例は SQL Anywhere で有効です。A、B、C はテーブルです。この例では、テーブル A の同一インスタンスは B と C のどちらにもジョインされます。スター・ジョインでジョインを分けるときにはカンマが必要です。スター・ジョインでのカンマの使用方法は、スター・ジョインの構文特有のものです。

SELECT *
FROM A LEFT OUTER JOIN B ON A.x = B.x,
     A LEFT OUTER JOIN C ON A.y = C.y;

これは、次の例と同義です。

SELECT *
FROM A LEFT OUTER JOIN B ON A.x = B.x,
     C RIGHT OUTER JOIN A ON A.y = C.y;

2 つの例はどちらも次の ANSI/ISO 標準構文と同義です (カッコはオプションです)。

SELECT *
FROM (A LEFT OUTER JOIN B ON A.x = B.x)
LEFT OUTER JOIN C ON A.y = C.y;

次の例では、テーブル A が 3 つのテーブル B、C、D にジョインされます。

SELECT *
FROM A JOIN B ON A.x = B.x,
     A JOIN C ON A.y = C.y,
     A JOIN D ON A.w = D.w;

上の例は、次の ANSI/ISO 標準構文と同義です (カッコはオプションです)。

SELECT *
FROM ((A JOIN B ON A.x = B.x)
JOIN C ON A.y = C.y)
JOIN D ON A.w = D.w;

複雑なジョインは図にするとわかりやすくなります。前述の例は次の図で説明できます。この図から、テーブル B、C、D がテーブル A を介してジョインされることがわかります。

テーブル A が中心にあり、テーブル B、C、D がそれぞれテーブル A につながっています。
注意

重複するテーブル名を使用できるのは、extended_join_syntax オプションが On (デフォルト) になっている場合だけです。

詳細については、extended_join_syntax オプション [データベース]を参照してください。

例 1

Rollin Overbey に発注した顧客名リストを作成します。FROM 句にある Employeesテーブルのどのカラムも結果に表示されないことに注意してください。また、Customers.id や Employees.EmployeeID など、ジョインしたどのカラムも結果に表示されません。それでも、FROM 句に Employees テーブルを使用するだけで、このジョインは可能です。

SELECT Customers.GivenName, Customers.Surname,
   SalesOrders.OrderDate
FROM   SalesOrders KEY JOIN Customers,
      SalesOrders KEY JOIN Employees
WHERE Employees.GivenName = 'Rollin'
  AND Employees.Surname = 'Overbey'
ORDER BY SalesOrders.OrderDate;
GivenName Surname OrderDate
Tommie Wooten 2000-01-03
Michael Agliori 2000-01-08
Salton Pepper 2000-01-17
Tommie Wooten 2000-01-23
... ... ...

次の例は、ANSI/ISO 標準構文の文と同義です。

SELECT Customers.GivenName, Customers.Surname,
  SalesOrders.OrderDate
FROM SalesOrders JOIN Customers
  ON SalesOrders.CustomerID = 
   Customers.ID
JOIN Employees
  ON SalesOrders.SalesRepresentative = 
   Employees.EmployeeID
WHERE Employees.GivenName = 'Rollin'
  AND Employees.Surname = 'Overbey'
ORDER BY SalesOrders.OrderDate;
例 2

次の例では、「各顧客が製品ごとに発注した数はどれくらいか、また受注した営業部員の管理者は誰か」という質問に回答します。

回答するために、ます、どの情報を取り出すのかをリストします。ここでは、製品、数量、顧客名、管理者名を取り出します。次に、これらの情報を保持しているテーブルをリストします。ここでは、Products、SalesOrderItems、Customers、Employees になります。SQL Anywhere サンプル・データベースの構造 (サンプル・データベース・スキーマを参照) を見ると、これらのテーブルがすべて SalesOrders テーブルを介して関連していることがわかります。SalesOrders テーブルにスター・ジョインを作成すると、他のテーブルから情報を取り出すことができます。

また、管理者名を取得するには、セルフジョインを作成する必要があります。Employees テーブルには、管理者の ID 番号とすべての従業員の名前はありますが、管理者名だけをリストしたカラムがないからです。詳細については、セルフジョインを参照してください。

次の文は SalesOrders テーブルを中心にしてスター・ジョインを作成します。このジョインは、結果セットにすべての顧客が含まれるように、すべて外部ジョインになっています。注文しなかった顧客もいますが、そういう顧客の他の値は NULL になっています。結果セットのカラムは、Customers、Products、Quantity ordered、営業部員の管理者名です。

SELECT Customers.GivenName, Products.Name,
  SUM(SalesOrderItems.Quantity), m.GivenName
FROM SalesOrders
    KEY RIGHT OUTER JOIN Customers,
   SalesOrders
    KEY LEFT OUTER JOIN SalesOrderItems
    KEY LEFT OUTER JOIN Products,
      SalesOrders
    KEY LEFT OUTER JOIN Employees AS e
    LEFT OUTER JOIN Employees AS m
        ON (e.ManagerID = m.EmployeeID)
WHERE Customers.State = 'CA'
GROUP BY Customers.GivenName, Products.Name, m.GivenName
ORDER BY SUM(SalesOrderItems.Quantity) DESC, 
   Customers.GivenName;
GivenName Name SUM(SalesOrderItems.Quantity) GivenName
Sheng Baseball Cap 240 Moira
Laura Tee Shirt 192 Moira
Moe Tee Shirt 192 Moira
Leilani Sweatshirt 132 Moira
... ... ... ...

以下は、このスター・ジョインを使ったテーブルの図です。矢印は、外部ジョインの方向 (右または左) を示します。顧客の完全なリストはすべてのジョイン全体で保持されます。

Customers テーブルは右側にあり、矢印は SalesOrders テーブルを指しています。この SalesOrders テーブルから 2 つの矢印が出ており、両方とも左を指しています。1 つの矢印は SalesOrderItems を指し、SalesOrderItems から Products を指しています。もう 1 つの矢印は Employees AS e を指し、Employees AS e から Employees AS m を指しています。

次に示す ANSI/ISO 標準構文は、例 2 のスター・ジョインと同義です。

SELECT Customers.GivenName, Products.Name,
  SUM(SalesOrderItems.Quantity), m.GivenName
FROM SalesOrders LEFT OUTER JOIN SalesOrderItems
    ON SalesOrders.ID = SalesOrderItems.ID
  LEFT OUTER JOIN Products
    ON SalesOrderItems.ProductID = Products.ID
  LEFT OUTER JOIN Employees as e
    ON SalesOrders.SalesRepresentative = e.EmployeeID
  LEFT OUTER JOIN Employees as m
    ON e.ManagerID = m.EmployeeID
  RIGHT OUTER JOIN Customers
    ON SalesOrders.CustomerID = Customers.ID
WHERE Customers.State = 'CA'
GROUP BY Customers.GivenName, Products.Name, m.GivenName
ORDER BY SUM(SalesOrderItems.Quantity) DESC, 
   Customers.GivenName;