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

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

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

重複名は、構文が意味をなすように、必ず異なるジョイン内に置きます。同一ジョイン内でテーブル名やビュー名を 2 回使用すると、2 番目のインスタンスは無視されます。たとえば、FROM A,A と FROM 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 (デフォルト) になっている場合だけです。

例 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;

参照

DocCommentXchange で意見交換できます