4D v16
結合のサポート
結合のサポート
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
name depID cityID Alan 10 30 Anne 11 39 Bernard 10 33 Fabrice 12 35 Martin 15 30 Philip NULL 33 Thomas 10 NULL
- Departments
depID depName 10 Program 11 Engineering NULL Marketing 12 Development 13 Quality
- Cities
cityID cityName 30 Paris 33 New York NULL Berlin
必要であれば、以下のコードを実行する事でこのデータベースを自動的に生成することができます:
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 SQLINNER結合は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結合の結果は以下のようになります:
| aName | aEmpDepID | aDepID | aDepName |
| Alan | 10 | 10 | Program |
| Anne | 11 | 11 | Engineering |
| Bernard | 10 | 10 | Program |
| Mark | 12 | 12 | Development |
| Thomas | 10 | 10 | Program |
結果の中には、以下の理由により、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以下は例題データベースでこの結合を実行したときの結果です (赤は追加の行を示します):
| aName | aEmpDepID | aDepID | aDepName |
| Alan | 10 | 10 | Program |
| Anne | 11 | 11 | Engineering |
| Bernard | 10 | 10 | Program |
| Mark | 12 | 12 | Development |
| Thomas | 10 | 10 | Program |
| Martin | 15 | NULL | NULL |
| Philip | NULL | NULL | NULL |
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以下は例題データベースでこの結合を実行したときの結果です (赤は追加の行を示します):
| aName | aEmpDepID | aDepID | aDepName |
| Alan | 10 | 10 | Program |
| Anne | 11 | 11 | Engineering |
| Bernard | 10 | 10 | Program |
| Mark | 12 | 12 | Development |
| Thomas | 10 | 10 | Program |
| NULL | NULL | NULL | Marketing |
| NULL | NULL | 13 | Quality |
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以下は例題データベースでこの結合を実行したときの結果です (赤は追加の行を示します):
| aName | aEmpDepID | aDepID | aDepName |
| Alan | 10 | 10 | Program |
| Anne | 11 | 11 | Engineering |
| Bernard | 10 | 10 | Program |
| Mark | 12 | 12 | Development |
| Thomas | 10 | 10 | Program |
| Martin | 15 | NULL | NULL |
| Philip | NULL | NULL | NULL |
| NULL | NULL | NULL | Marketing |
| NULL | NULL | 13 | Quality |
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実行した結果は以下のようになります:
| aName | aEmpDepID | aEmpCityID | aDepID | aDepName | aCityID | aCityName |
| Alan | 10 | 30 | 10 | Program | NULL | NULL |
| Bernard | 10 | 33 | 10 | Program | 30 | Paris |
| Anne | 11 | 39 | 11 | Engineering | 33 | New York |
| Fabrice | 12 | 35 | 12 | Development | NULL | NULL |
| Thomas | 10 | NULL | 10 | Program | NULL | NULL |
| NULL | NULL | NULL | NULL | Marketing | NULL | NULL |
| NULL | NULL | NULL | 13 | Quality | NULL | NULL |
その一方で、以下の3つのクエリはいくつかのルールに違反しているため実行は拒否されます:
SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T1.ID) -- ここではT2は左側にありますが、T1はすぐ右側にはありません
ON T1.ID=T3.IDSELECT * 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節でサポートされている | |
| ブール操作 | OR | AND と 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を使用する
SQLリファレンス ( 4D v16)
