4D v20

結合のサポート

ホーム

 
ホーム
4D v20
SQLリファレンス
4DでSQLを使用する
結合のサポート

結合のサポート  


 

 

4DのSQLエンジンは結合のサポートを拡張します。

結合操作はINNERまたはOUTER、そして明示または暗黙です。暗黙のINNER結合はSELECTコマンドでサポートされます。さらにJOINキーワードを使用して生成される明示的なINNER結合とOUTER結合が使用できます。

Note: 4D SQLエンジンの結合の現在の実装には以下が含まれていません:

  • NATURAL結合
  • INNER結合のUSING構成
  • CROSS INNER結合

概要  

結合操作は2つ以上のテーブル間のレコードの接続を作成し、結果を結合と呼ばれる新しいテーブルに一体化させるために使用します。

4D v15 R4以降、2つのテーブルをつなぐOUTER結合と、3つ以上のテーブルをつなぐOUTER結合では、実装が異なり、ルールが異なるようになりました。以下の章のうち必要な章を参照するようにして下さい。

Note: 通常データベースエンジンでは、テーブルの順番は検索の間に指定された順番です。しかし結合を使用する際、テーブルの順番はテーブルリストで決定されます。以下の例題で:

SELECT * FROM T1 RIGHT OUTER JOIN T2 ON T2.depID = T1.depID;

テーブルの順番は (テーブルリストに現れる通り) T1そしてT2です。 (結合条件に現れる順である) T2そしてT1ではありません。

joinがどのようにして動作するかを説明するために、この章では以下のデータベースを使用することにします:

  • Employees
    namedepIDcityID
    Alan1030
    Anne1139
    Bernard1033
    Fabrice1235
    Martin1530
    PhilipNULL33
    Thomas10NULL
  • Departments
    depIDdepName
    10Program
    11Engineering
    NULLMarketing
    12Development
    13Quality
  • Cities
    cityIDcityName
    30Paris
    33New York
    NULLBerlin

必要であれば、以下のコードを実行する事でこのデータベースを自動的に生成することができます:

 Begin SQL
         DROP TABLE IF EXISTS Employees;
         CREATE TABLE Employees ( depID INT32, name VARCHAR, cityID INT32);
         INSERT INTO Employees (name, depID, cityID) VALUES ('Alan', 10, 30);
         INSERT INTO Employees (name, depID, cityID) VALUES ('Anne', 11, 39);
         INSERT INTO Employees (name, depID, cityID) VALUES ('Bernard', 10, 33);
         INSERT INTO Employees (name, depID, cityID) VALUES ('Fabrice', 12, 35);
         INSERT INTO Employees (name, depID, cityID) VALUES ('Martin', 15, 30);
         INSERT INTO Employees (name, depID, cityID) VALUES ('Philip', NULL, 33);
         INSERT INTO Employees (name, depID, cityID) VALUES ('Thomas', 10, NULL);
 
         DROP TABLE IF EXISTS Departments;
         CREATE TABLE Departments ( depID INT32, depName VARCHAR );
         INSERT INTO Departments (depID, depName) VALUES (10, 'Program');
         INSERT INTO Departments (depID, depName) VALUES (11, 'Engineering');
         INSERT INTO Departments (depID, depName) VALUES (NULL, 'Marketing');
         INSERT INTO Departments (depID, depName) VALUES (12, 'Development');
         INSERT INTO Departments (depID, depName) VALUES (13, 'Quality');
 
         DROP TABLE IF EXISTS Cities;
         CREATE TABLE Cities ( cityID INT32, cityName VARCHAR );
         INSERT INTO Cities (cityID, cityName) VALUES (30, 'Paris');
         INSERT INTO Cities (cityID, cityName) VALUES (33, 'New York');
         INSERT INTO Cities (cityID, cityName) VALUES (NULL, 'Berlin');
 End SQL

INNER結合は2つの列間で一致する値を探す比較に基づきます。

以下は明示的なINNER結合の例です:

SELECT *  
        FROM employees, departments
        WHERE employees.DepID = departments.DepID;

4Dでは、JOINキーワードを使用して明示的なINNER結合を具体的に指定する事もできます:

SELECT *
        FROM employees
        INNER JOIN departments
            ON employees.DepID = departments.DepID;

このクエリを以下のようにして4Dのコード内に挿入する事もできます:

 ARRAY TEXT(aName;0)
 ARRAY TEXT(aDepName;0)
 ARRAY INTEGER(aEmpDepID;0)
 ARRAY INTEGER(aDepID;0)
 Begin SQL
        SELECT Employees.name, Employees.depID, Departments.depID, Departments.depName
        FROM Employees
                INNER JOIN Departments
                    ON Employees.depID = Departments.depID
                INTO :aName, :aEmpDepID, :aDepID, :aDepName;
 End SQL

結合の結果は以下のようになります:

aNameaEmpDepIDaDepIDaDepName
Alan1010Program
Anne1111Engineering
Bernard1010Program
Mark1212Development
Thomas1010Program

結果の中には、以下の理由により、PhilipまたはMartinという名前のemployeeはおらず、またMarketingあるいはQualityのDepartmentの人も含まれないという点に注意して下さい:

  • Philipという名前にはDepartmentが割り当てられていない(NULL値)
  • Martinに割り当てられたDepartment IDはDepartmentのテーブルに含まれていない
  • Quality Department(ID 13)に割り当てられているemployeeはいない
  • Marketing department にはIDが割り当てられていない(NULL値)

4DではOUTER結合を生成できます。OUTER結合では、結合させるテーブルの行間で値が一致する必要はありません。結果のテーブルには、たと え一致する行がなくても、テーブル(または少なくとも1つの結合されたテーブル)のすべての行が含まれます。これは、たとえ異なる結合されたテーブル間で 行が完全に値を持っていない場合でも、あるテーブルのすべての情報が使用できることを意味します。

OUTER結合にはLEFT、RIGHT、そしてFULLキーワードで指定される3つのタイプがあります。LEFTとRIGHTは (JOIN キーワードの左あるいは右どちらに位置するかで) すべてのデータが処理されるテーブルを示すために使用されます。FULLは両側のOUTER結合を示します。

Note: 4Dでは明示的なOUTER結合だけがサポートされます。

2テーブル間のOUTER結合の場合、条件は複雑なものでも可能ですが、結合に含まれているカラム感の比較演算子はかならず等号演算子に基づいていなければなりません。例えば、明示的なJOIN条件にの中で、>= 演算子を使用する事はできません。暗示的JOINの中ではどのような型の比較も使用する事ができます。内部的には、等号比較は(迅速な実行を保証する)4Dエンジンによって実行されています。

LEFT OUTER結合 (またはLEFT結合) の結果には、たとえ結合条件に一致するレコードがキーワードの右側のテーブルにない場合でも、常にキーワードの左にあるテーブルのすべてのレコードが含ま れます。これは検索が右側のテーブル内で一致する行を見つけることができない左テーブルの各行についても、結合はそれらを結果に含めることを意味します。 しかしこの場合右テーブルの各列にはNULL値が入ります。言い換えればLEFT OUTER結合は左側のテーブルのすべての行と、結合条件に一致する右テーブルの行 (または一致しない場合NULL) を作成します。左テーブルの1つの行に対し、結合述部に一致する右テーブルの行が複数ある場合、左テーブルの値は右テーブルの各行に対し繰り返される点に 留意してください。

以下はLEFT OUTER結合を行う4Dコードの例です:

 ARRAY TEXT(aName;0)
 ARRAY TEXT(aDepName;0)
 ARRAY INTEGER(aEmpDepID;0
 ARRAY INTEGER(aDepID;0)
 Begin SQL
        SELECT Employees.name, Employees.depID, Departments.depID, Departments.depName
            FROM Employees
            LEFT OUTER JOIN Departments
                ON Employees.DepID = Departments.DepID;
                INTO :aName, :aEmpDepID, :aDepID, :aDepName;
 End SQL

以下は例題データベースでこの結合を実行したときの結果です (赤は追加の行を示します):

aNameaEmpDepIDaDepIDaDepName
Alan1010Program
Anne1111Engineering
Bernard1010Program
Mark1212Development
Thomas1010Program
Martin15NULLNULL
PhilipNULLNULLNULL

RIGHT OUTER結合は完全にLEFT OUTER結合の反対です (前の段落参照)。たとえ結合条件に一致するレコードが左のテーブルに見つからない場合でも、結果には常にJOINキーワードの右側のテーブルのすべてのレコードが含まれます。

以下はRIGHT OUTER結合を行う4Dコードの例です:

 ARRAY TEXT(aName;0)
 ARRAY TEXT(aDepName;0)
 ARRAY INTEGER(aEmpDepID;0
 ARRAY INTEGER(aDepID;0)
 Begin SQL
        SELECT Employees.name, Employees.depID, Departments.depID, Departments.depName
            FROM Employees
            RIGHT OUTER JOIN Departments
                ON Employees.DepID = Departments.DepID;
                INTO :aName, :aEmpDepID, :aDepID, :aDepName;
 End SQL

以下は例題データベースでこの結合を実行したときの結果です (赤は追加の行を示します):

aNameaEmpDepIDaDepIDaDepName
Alan1010Program
Anne1111Engineering
Bernard1010Program
Mark1212Development
Thomas1010Program
NULLNULLNULLMarketing
NULLNULL13Quality

FULL OUTER結合は単純にLEFT OUTER結合とRIGHT OUTER結合の結果を組み合わせたものです。結果の結合テーブルには左および右のレコードがすべて含まれます。それぞれの側で失われたフィールドはNULL値になります。

以下はFULL OUTER結合を行う4Dコードの例です:

 ARRAY TEXT(aName;0)
 ARRAY TEXT(aDepName;0)
 ARRAY INTEGER(aEmpDepID;0
 ARRAY INTEGER(aDepID;0)
 Begin SQL
        SELECT Employees.name, Employees.depID, Departments.depID, Departments.depName
            FROM Employees
            FULL OUTER JOIN Departments
                ON Employees.DepID = Departments.DepID;
                INTO :aName, :aEmpDepID, :aDepID, :aDepName;
 End SQL

以下は例題データベースでこの結合を実行したときの結果です (赤は追加の行を示します):

aNameaEmpDepIDaDepIDaDepName
Alan1010Program
Anne1111Engineering
Bernard1010Program
Mark1212Development
Thomas1010Program
Martin15NULLNULL
PhilipNULLNULLNULL
NULLNULLNULLMarketing
NULLNULL13Quality

4D v15 R4以降、ビルトインSQLサーバーはSQLのOUTER結合のサポートを拡張し、3つ以上のテーブルでクエリが行えるようになりました。この実装には独自のルールと限度があり、その詳細については以下の章でまとめられています。

2テーブル間でのOUTER結合と同様、3テーブル間(またはそれ以上)でのOUTER結合でもLEFT、RIGHT、FULLを使用する事ができます。OUTER結合についての一般的な情報については、上記のOUTER結合の章を参照して下さい。

2テーブル間でのOUTER結合とは異なり、3テーブル間(またはそれ以上)でのOUTER結合では通常の等号(=)に加え、複数の比較演算子をサポートします: <、 >、 >=、 または <=です。これらの演算子は、ON節の中においてミックスする事が出来ます。

  • それぞれの明示的なOUTER結合のON節は、ちょうど2つのテーブルを参照しなければなりません(多くても少なくてもいけません)。結合されたテーブルは、ON節の中で少なくとも1回は参照されていなければなりません。
  • 一つのテーブルはJOIN節のすぐ左側から、もう一つはJOIN節のすぐ右側から来なければなりません。

例えば、以下のクエリは正常に実行されます:

SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T3.ID) -- ここで、T2は左側に、T3は右側に来ています
ON T1.ID=T3.ID -- ここではT1が左側に、T3が右側に来ています

この3つテーブルを使い、以下のように操作をすることができます:

 ARRAY TEXT(aName;0)
 ARRAY TEXT(aDepName;0)
 ARRAY TEXT(aCityName;0)
 ARRAY INTEGER(aEmpDepID;0)
 ARRAY INTEGER(aEmpCityID;0
 ARRAY INTEGER(aDepID;0)
 ARRAY INTEGER(aCityID;0)
 Begin SQL
        SELECT Employees.name, Employees.depID, Employees.cityID, Departments.depID, Departments.depName, Cities.cityID, Cities.cityName
            FROM Departments
                LEFT JOIN
                (Employees LEFT JOIN Cities ON Employees.cityID=Cities.cityID)
                ON Departments.depID=Employees.depID
                INTO :aName, :aEmpDepID, :aEmpCityID, :aDepID, :aDepName, :aCityID, :aCityName;
 End SQL

実行した結果は以下のようになります:

aNameaEmpDepIDaEmpCityIDaDepIDaDepNameaCityIDaCityName
Alan103010ProgramNULLNULL
Bernard103310Program30Paris
Anne113911Engineering33New York
Fabrice123512DevelopmentNULLNULL
Thomas10NULL10ProgramNULLNULL
NULLNULLNULLNULLMarketingNULLNULL
NULLNULLNULL13QualityNULLNULL

その一方で、以下の3つのクエリはいくつかのルールに違反しているため実行は拒否されます:

SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T1.ID) -- ここではT2は左側にありますが、T1はすぐ右側にはありません
ON T1.ID=T3.ID

SELECT * FROM
(T1 LEFT JOIN T2 ON T1.ID=T2.ID)
LEFT JOIN
(T3 LEFT JOIN T4 ON T3.ID=T4.ID)
ON T3.Name=T4.Name -- ここではT3もT4もJOIN節の右側から来ていて、左側からはどのテーブルも来ていません

SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T3.ID)
ON T1.ID=T3.ID AND T1.ID=T2.ID -- ここでは、2つを超えるテーブルがON節で使われています。T1、T2、そしてT3です

一般的に、JOIN節の左側のテーブル(Tx1、Tx2、、、Txn)と、右側にあるテーブル(Ty1、Ty2、、、Tyn)を結合する場合、ON表現は左側のテーブルから一つ(Txa)だけ、右側のテーブルから一つ(Txb)だけ参照する必要があります。

ON節でサポートされていないON節でサポートされている
ブール操作ORAND と NOT
プレディケートと関数IS NULL, COALESCEそれ以外の全てのプレディケートとビルトイン関数(どのような組み合わせも可能)
4D変数参照-制約なくサポート
4Dメソッド呼び出しカレントのJOIN節の、左か右のどちらかが明示的なOUTER結合である場合その他の全ての場合(以下の例を参照の事)

以下の4Dメソッド呼び出しの例は、結合されるのがどちらも非INNERサブ結合ではないため、サポートされます:

SELECT * FROM T1
LEFT JOIN T2
ON T1.ID={FN My4DCall (T2.ID) AS INT32}

その一方で、こちらの4Dメソッド呼び出しの例は非INNERサブ結合が結合されるため、サポートされません:

SELECT * FROM
(T1 LEFT JOIN T2 ON T1.ID=T2.ID)
LEFT JOIN -- このJOIN節の左右両側に明示的なLEFT結合が含まれます
(T3 LEFT JOIN T4 ON T3.ID=T4.ID)
ON T1.ID={FN My4DCall (T4.ID) AS INT32} -- 非INNERサブ結合が結合されます

  • SQL viewsへの参照は明示的JOIN宣言では許可されていません。
  • 外部JOINを使用するサブクエリはサポートされていません。以下の例は拒否されます:
    SELECT T2.ID FROM T2
    WHERE T2.ID=(
    SELECT COUNT ( * ) FROM
    (T1 LEFT JOIN T3 ON T1.ID=T3.ID)
    RIGHT JOIN T4 ON T3.ID=T4.ID)

前ページ
次ページ

 
プロパティ 

プロダクト: 4D
テーマ: 4DでSQLを使用する

 
ページの目次 
概要
サンプルデータベース
明示的なINNER結合
OUTER結合
LEFT OUTER結合
RIGHT OUTER結合
FULL OUTER結合
3つ以上のテーブルでのOUTER結合
基本的なルール
ON条件のサポート
一般的な制約
 
履歴 

 
ARTICLE USAGE

SQLリファレンス ( 4D v20)