SQL
ジョイン

前書き

構文

• [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } ] JOIN

備考

基本的な明示的な内部結合

基本結合（「内部結合」とも呼ばれる）は、2つの表のデータをjoin節で定義された関係で照会します。

次の例では、Employeesテーブルから従業員のファーストネーム（FName）を選択し、Departmentsテーブルから（Name）担当する部門の名前を選択します。

SELECT Employees.FName, Departments.Name
FROM   Employees
JOIN   Departments
ON Employees.DepartmentId = Departments.Id

これはサンプルデータベースから次のものを返します ：

暗黙的な結合

結合は、 from節に複数の表を持ち、カンマで区切り, where節でそれらの関係を定義することによっても実行できます。この手法は暗黙結合と呼ばれます（実際にはjoin句は含まれていないjoin ）。

すべてのRDBMSがそれをサポートしていますが、構文は通常は推奨されていません。この構文を使用することが悪い考えである理由は次のとおりです。

• 間違った結果を返してしまう偶然のクロス結合を取得することは可能です。特に、クエリに多数の結合がある場合は、
• クロスジョインを意図していた場合は、構文から明らかではなく（代わりにCROSS JOINを書きます）、メンテナンス中に誰かがそれを変更する可能性があります。

次の例では、従業員のファーストネームと、自分が働いている部署の名前を選択します。

SELECT e.FName, d.Name
FROM   Employee e, Departments d
WHERE  e.DeptartmentId = d.Id

これはサンプルデータベースから次のものを返します ：

左外部結合

左外部結合（左結合または外部結合とも呼ばれます）は、左テーブルのすべての行が確実に表現される結合です。右側の表の一致する行が存在しない場合、対応するフィールドはNULLです。

次の例では、すべての部門とその部門で働く従業員のファーストネームを選択します。従業員のいない部署は依然として結果に返されますが、従業員名はNULLになります。

SELECT          Departments.Name, Employees.FName
FROM            Departments 
LEFT OUTER JOIN Employees 
ON              Departments.Id = Employees.DepartmentId

これはサンプルデータベースから次のものを返します ：

それではどうやって動くの？

FROM句には2つのテーブルがあります。

そして

最初に、中間テーブルを与える2つのテーブルからデカルト積が生成される。
結合条件を満たすレコード（ Departments.Id = Employees.DepartmentId ）は太字で強調表示されています。これらはクエリの次の段階に渡されます。

これはLEFT OUTER JOINであるため、すべてのレコードは結合のLEFT側（Departments）から戻されますが、RIGHT側のレコードは結合基準と一致しない場合はNULLマーカーが与えられます。下の表では、これはNULL TechをNULL

最後に、 SELECT句内で使用される各式は、最終テーブルを返すように評価されます。

自己結合

テーブルは、それ自身に結合されていてもよい。この使用例では、表の2つの出現を区別するためにエイリアスを使用する必要があります。

以下の例では、 サンプルデータベースEmployeesテーブルの Employeeごとに、従業員のマネージャの対応するファーストネームとともに従業員の名を含むレコードが返されます。マネージャーも従業員であるため、テーブル自体は従業員です。

SELECT 
    e.FName AS "Employee", 
    m.FName AS "Manager"
FROM   
    Employees e
JOIN   
    Employees m 
    ON e.ManagerId = m.Id

このクエリは、次のデータを返します。

それではどうやって動くの？

元の表には次のレコードが含まれています。

最初のアクションは、 FROM句で使用されるテーブル内のすべてのレコードのデカルト積を作成することです。この場合、Employeesテーブルが2回あるので、中間テーブルは次のようになります（この例では使用されていないフィールドはすべて削除しています）。

次のアクションは、 JOIN基準を満たすレコードのみを保持することです。エイリアス化されたeテーブルのManagerIdがエイリアス化されたmテーブルIdと等しいレコード：

次に、 SELECT句内で使用される各式が評価され、この表が戻されます。

最後に、列名e.FNameとm.FNameは、 AS演算子で割り当てられたエイリアス列名に置き換えられます。

クロスジョイン

クロス・ジョインは、2つのメンバーのデカルト積を行います。デカルト積は、1つの表の各行が結合の2番目の表の各行と結合されることを意味します。たとえば、 TABLEAに20行、 TABLEBに20行がある場合、結果は20*20 = 400出力行になります。

サンプルデータベースの使用

SELECT d.Name, e.FName
FROM   Departments d
CROSS JOIN Employees e;

返すもの：

デカルト結合を行いたい場合は、明示的なCROSS JOINを書くことをお勧めします。

サブクエリへの結合

サブクエリの結合は、子/詳細テーブルから集計データを取得し、それを親テーブルまたはヘッダーテーブルのレコードとともに表示する場合によく使用されます。たとえば、子レコードの数、子レコードの数値列の平均、または日付または数値フィールドに基づく上または下の行を取得することができます。この例では、エイリアスを使用しています。これにより、複数のテーブルが関わっている場合に、クエリが読みやすくなります。かなり典型的なサブクエリ結合のようなものがあります。この場合、親テーブルPurchase Ordersからすべての行を取得し、子テーブルPurchaseOrderLineItemsの各親レコードの最初の行のみを取得しています。

SELECT po.Id, po.PODate, po.VendorName, po.Status, item.ItemNo, 
  item.Description, item.Cost, item.Price
FROM PurchaseOrders po
LEFT JOIN 
     (
       SELECT l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price, Min(l.id) as Id 
       FROM PurchaseOrderLineItems l
       GROUP BY l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price
     ) AS item ON item.PurchaseOrderId = po.Id

適用されている＆結合されていない

非常に興味深いタイプのJOINは、LATERAL JOIN（PostgreSQL 9.3以降の新機能）です。
これはSQL-Server＆OracleのCROSS APPLY / OUTER APPLYとも呼ばれます。

基本的な考え方は、テーブル値関数（またはインラインサブクエリ）が、参加するすべての行に適用されるということです。

これにより、たとえば、最初に一致するエントリだけを別のテーブルに結合することができます。
通常の結合と横方向の結合の違いは、以前に結合したサブクエリに結合した列を使用できるという点にあります。

構文：

PostgreSQL 9.3+

左|右|内部JOIN LATERAL

SQLサーバー：

クロス| OUTERが適用されます

INNER JOIN LATERALはCROSS APPLYと同じです
LEFT JOIN LATERALはOUTER APPLYと同じです

使用例（PostgreSQL 9.3以降）：

SELECT * FROM T_Contacts 

--LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND MAP_CTCOU_SoftDeleteStatus = 1 
--WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989


LEFT JOIN LATERAL 
(
    SELECT 
         --MAP_CTCOU_UID    
         MAP_CTCOU_CT_UID   
        ,MAP_CTCOU_COU_UID  
        ,MAP_CTCOU_DateFrom 
        ,MAP_CTCOU_DateTo   
   FROM T_MAP_Contacts_Ref_OrganisationalUnit 
   WHERE MAP_CTCOU_SoftDeleteStatus = 1 
   AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID 

    /*  
    AND 
    ( 
        (__in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo) 
        AND 
        (__in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom) 
    ) 
    */
   ORDER BY MAP_CTCOU_DateFrom 
   LIMIT 1 
) AS FirstOE 

SQL Serverの場合

SELECT * FROM T_Contacts 

--LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND MAP_CTCOU_SoftDeleteStatus = 1 
--WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989

-- CROSS APPLY -- = INNER JOIN 
OUTER APPLY    -- = LEFT JOIN 
(
    SELECT TOP 1 
         --MAP_CTCOU_UID    
         MAP_CTCOU_CT_UID   
        ,MAP_CTCOU_COU_UID  
        ,MAP_CTCOU_DateFrom 
        ,MAP_CTCOU_DateTo   
   FROM T_MAP_Contacts_Ref_OrganisationalUnit 
   WHERE MAP_CTCOU_SoftDeleteStatus = 1 
   AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID 

    /*  
    AND 
    ( 
        (@in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo) 
        AND 
        (@in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom) 
    ) 
    */
   ORDER BY MAP_CTCOU_DateFrom 
) AS FirstOE 

フル・ジョイン

あまり知られていないJOINの1つのタイプは、FULL JOINです。
（注：FULL JOINは2016年のMySQLではサポートされていません）

FULL OUTER JOINは、左の表のすべての行と、右の表のすべての行を戻します。

左側のテーブルに、右側のテーブルに一致する行がない場合や、左側のテーブルに一致する行がない場合は、それらの行もリストされます。

例1：

SELECT * FROM Table1

FULL JOIN Table2 
     ON 1 = 2 

例2：

SELECT 
     COALESCE(T_Budget.Year, tYear.Year) AS RPT_BudgetInYear 
    ,COALESCE(T_Budget.Value, 0.0) AS RPT_Value 
FROM T_Budget 

FULL JOIN tfu_RPT_All_CreateYearInterval(@budget_year_from, @budget_year_to) AS tYear 
      ON tYear.Year = T_Budget.Year 

ソフト削除を使用している場合は、WHERE節でソフト削除ステータスを再度チェックする必要があります（FULL JOINはUNIONのように動作するため）。
あなたはAP_SoftDeleteStatus = 1をjoin節に置くので、この小さな事実を見落とすのは簡単です。

また、FULL JOINを実行している場合は、通常、WHERE句にNULLを許可する必要があります。値にNULLを許可するのを忘れると、INNER結合と同じ効果があります。これは、FULL JOINを実行している場合に望ましくないものです。

例：

SELECT 
     T_AccountPlan.AP_UID
    ,T_AccountPlan.AP_Code
    ,T_AccountPlan.AP_Lang_EN
    ,T_BudgetPositions.BUP_Budget
    ,T_BudgetPositions.BUP_UID 
    ,T_BudgetPositions.BUP_Jahr
FROM T_BudgetPositions    

FULL JOIN T_AccountPlan
    ON T_AccountPlan.AP_UID = T_BudgetPositions.BUP_AP_UID 
    AND T_AccountPlan.AP_SoftDeleteStatus = 1 

WHERE (1=1) 
AND (T_BudgetPositions.BUP_SoftDeleteStatus = 1 OR T_BudgetPositions.BUP_SoftDeleteStatus IS NULL) 
AND (T_AccountPlan.AP_SoftDeleteStatus = 1 OR T_AccountPlan.AP_SoftDeleteStatus IS NULL) 

再帰的な結合

再帰的結合は、親子データを取得するためによく使用されます。 SQLでは、これらは再帰的な共通テーブル式で実装されます。たとえば、次のようになります。

WITH RECURSIVE MyDescendants AS (
    SELECT Name
    FROM People
    WHERE Name = 'John Doe'

    UNION ALL

    SELECT People.Name
    FROM People
    JOIN MyDescendants ON People.Name = MyDescendants.Parent
)
SELECT * FROM MyDescendants;

内外の結合の違い

SQLには、一致する行が結果に含まれるかどうか（ INNER JOIN 、 LEFT OUTER JOIN 、 RIGHT OUTER JOIN 、およびFULL OUTER JOIN （ INNERおよびOUTERキーワードはオプション））を指定するための様々な結合タイプがあります。下の図は、これらのタイプの結合の違いを示しています。青色の領域は結合によって戻される結果を表し、白い領域は結合が戻らない結果を表します。

SQL Pictorial Presentation（ 参照 ）とのクロス結合：

以下はこの回答の例です。

たとえば、以下のような2つのテーブルがあります。

A    B
-    -
1    3
2    4
3    5
4    6

（1,2）はAに固有であり、（3,4）は共通であり、（5,6）はBに固有であることに留意されたい。

内部結合

等価クエリのいずれかを使用する内部結合は、2つのテーブル、すなわちそれらが共通して持つ2つのテーブルの共通部分を与えます。

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

左外部結合

左外部結合は、A内のすべての行とB内の共通行を与えます。

select * from a LEFT OUTER JOIN b on a.a = b.b;

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

右外部結合

同様に、右外部結合により、B内のすべての行と、A内の共通行が得られます。

select * from a RIGHT OUTER JOIN b on a.a = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

完全外部結合

完全な外部結合により、AとBの和集合、つまりAのすべての行とBのすべての行が得られます。A内の何かがB内の対応するデータを持たない場合、B部分はnullです。逆に。

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

JOIN用語：内、外、半、アンチ...

たとえば、2つのテーブル（AとB）とその行の一部が一致しているとします（特定のJOIN条件との関連で、特定のケースでは何でも構いません）。

さまざまな結合タイプを使用して、一致または不一致の行をいずれかの側から含めたり除外したり、上の図から対応する用語を選択して正しく名前を付けることができます。

以下の例では、次のテストデータを使用しています。

CREATE TABLE A (
    X varchar(255) PRIMARY KEY
);

CREATE TABLE B (
    Y varchar(255) PRIMARY KEY
);

INSERT INTO A VALUES
    ('Amy'),
    ('John'),
    ('Lisa'),
    ('Marco'),
    ('Phil');

INSERT INTO B VALUES
    ('Lisa'),
    ('Marco'),
    ('Phil'),
    ('Tim'),
    ('Vincent');

内部結合

一致する左右の行を結合します。

SELECT * FROM A JOIN B ON X = Y;

X      Y
------ -----
Lisa   Lisa
Marco  Marco
Phil   Phil

左外部結合

「左結合」と省略されることもあります。一致する左右の行を結合し、一致しない左の行を含みます。

SELECT * FROM A LEFT JOIN B ON X = Y;

X      Y
-----  -----
Amy    NULL
John   NULL
Lisa   Lisa
Marco  Marco
Phil   Phil

右外部結合

「右結合」と省略されることもあります。一致する左右の行を結合し、一致しない右の行を含みます。

SELECT * FROM A RIGHT JOIN B ON X = Y;

X      Y
-----  -------
Lisa   Lisa
Marco  Marco
Phil   Phil
NULL   Tim
NULL   Vincent

完全な外部結合

時には「完全結合」と略記されることもある。左外部結合の連合。

SELECT * FROM A FULL JOIN B ON X = Y;

X      Y
-----  -------
Amy    NULL
John   NULL
Lisa   Lisa
Marco  Marco
Phil   Phil
NULL   Tim
NULL   Vincent

左セミ結合

右の行に一致する左の行を含みます。

SELECT * FROM A WHERE X IN (SELECT Y FROM B);

X
-----
Lisa
Marco
Phil

右セミ結合

左の行に一致する右の行が含まれます。

SELECT * FROM B WHERE Y IN (SELECT X FROM A);

Y
-----
Lisa
Marco
Phil

ご覧のとおり、左と右のセミジョインには専用のIN構文がありません.SQLテキスト内のテーブル位置を切り替えるだけで効果が得られます。

左のアンチ・セミ・ジョイン

右側の行と一致しない左側の行が含まれます。

SELECT * FROM A WHERE X NOT IN (SELECT Y FROM B);

X
----
Amy
John

警告： NULL可能カラムでNOT INを使用している場合は注意してください！詳細はこちら 。

右アンチ・セミ・ジョイン

左側の行と一致しない右側の行が含まれます。

SELECT * FROM B WHERE Y NOT IN (SELECT X FROM A);

Y
-------
Tim
Vincent

ご覧のとおり、左と右のアンチ・セミ・ジョインには専用のNOT IN構文がありません - SQLテキスト内のテーブル位置を切り替えるだけで効果が得られます。

クロス結合

すべての右の行とすべての左のデカルト積。

SELECT * FROM A CROSS JOIN B;

X      Y
-----  -------
Amy    Lisa
John   Lisa
Lisa   Lisa
Marco  Lisa
Phil   Lisa
Amy    Marco
John   Marco
Lisa   Marco
Marco  Marco
Phil   Marco
Amy    Phil
John   Phil
Lisa   Phil
Marco  Phil
Phil   Phil
Amy    Tim
John   Tim
Lisa   Tim
Marco  Tim
Phil   Tim
Amy    Vincent
John   Vincent
Lisa   Vincent
Marco  Vincent
Phil   Vincent

クロス結合は、常に一致する結合条件を持つ内部結合に相当します。したがって、次のクエリは同じ結果を返します。

SELECT * FROM A JOIN B ON 1 = 1;

自己結合

これは、単にそれ自身と結合しているテーブルを示します。自己結合は、上で説明した任意の結合型にすることができます。たとえば、これは内部自己結合です。

SELECT * FROM A A1 JOIN A A2 ON LEN(A1.X) < LEN(A2.X);

X     X
----  -----
Amy   John
Amy   Lisa
Amy   Marco
John  Marco
Lisa  Marco
Phil  Marco
Amy   Phil