SQL
어울리다

소개

통사론

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

비고

기본 명시 적 내부 조인

기본 조인 ( "내부 조인"이라고도 함)은 join 절에 정의 된 두 테이블의 데이터를 쿼리합니다.

다음 예제에서는 Employees 테이블에서 직원의 이름 (FName)을 선택하고 Departments 테이블에서 작업하는 부서 이름 (Name)을 선택합니다.

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

예제 데이터베이스 에서 다음을 반환 합니다 .

암시 적 조인

또한 여러 테이블함으로써 수행 될 수 조인 from 쉼표로 구분 절을 , 그리고 그들 사이의 관계를 형성하는 where 절. 이 기술은 암시 적 조인 (실제로는 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 절에는 두 개의 테이블이 있습니다.

과

먼저 중간 테이블을 제공하는 두 테이블로부터 카티 전 곱이 생성됩니다.
조인 기준 ( Departments.Id = Employees.DepartmentId )을 충족시키는 레코드는 굵게 강조 표시됩니다. 이들은 쿼리의 다음 단계로 전달됩니다.

이것은 LEFT OUTER JOIN이므로 조인의 왼쪽 측면 (Departments)에서 모든 레코드가 반환되는 반면 조인 기준과 일치하지 않으면 RIGHT 측면의 레코드에는 NULL 표식이 주어집니다. 아래 테이블에서 NULL 로 Tech 를 반환 NULL

마지막으로 SELECT 절에서 사용 된 각 표현식이 최종 테이블을 반환하도록 평가됩니다.

자체 조인

테이블은 조건에 따라 서로 다른 행이 서로 일치하여 자체에 조인 될 수 있습니다. 이 경우 테이블의 두 번을 구별하기 위해 별명을 사용해야합니다.

아래 예제에서 예제 데이터베이스 인 Employees 테이블 의 각 Employee에 대해 직원의 이름과 직원의 관리자 이름을 포함하는 레코드가 반환됩니다. 관리자는 직원이기 때문에 테이블 자체에 조인됩니다.

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

이 쿼리는 다음 데이터를 반환합니다.

어떻게 작동합니까?

원본 테이블에는 다음 레코드가 포함됩니다.

첫 번째 작업은 FROM 절에 사용 된 테이블의 모든 레코드의 카디 전 곱을 만드는 것입니다. 이 경우 Employees 테이블이 두 번이므로 중간 테이블은 다음과 같습니다 (이 예제에서 사용되지 않은 필드는 모두 제거했습니다).

다음 작업은 JOIN 기준을 충족하는 레코드 만 유지하므로 앨리어싱 된 e 테이블 ManagerId 가 별칭이 지정된 m 테이블과 동일한 모든 레코드 Id :

그런 다음 SELECT 절에서 사용 된 각 표현식이이 테이블을 반환하도록 평가됩니다.

마지막으로 열 이름 인 e.FName 과 m.FName 은 AS 연산자로 할당 된 별칭 열 이름으로 바뀝니다.

크로스 조인

교차 결합은 두 멤버의 데카르트 곱을 수행합니다. 데카르트 곱은 하나의 테이블의 각 행이 조인의 두 번째 테이블의 각 행과 결합됨을 의미합니다. 예를 들어 TABLEA 에 20 개의 행이 있고 TABLEB 에 20 개의 행이 있으면 결과는 20*20 = 400 출력 행이됩니다.

예제 데이터베이스 사용하기

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

어느 것이 돌려 보낸다 :

데카르트 조인 (Cartesian Join)을 원한다면 명시 적으로 CROSS JOIN을 작성하여 원하는대로 강조하는 것이 좋습니다.

하위 쿼리에 가입

서브 u 리 결합은 하위 / 세부 사항 테이블에서 집계 데이터를 가져 와서 상위 / 헤더 테이블의 레코드와 함께 표시하려는 경우에 자주 사용됩니다. 예를 들어, 하위 레코드의 수, 하위 레코드의 숫자 열의 평균 또는 날짜 또는 숫자 필드를 기반으로하는 맨 위 또는 맨 아래 행을 가져올 수 있습니다. 이 예제에서는 여러 테이블이 관련되어있을 때 쿼리를 읽기 쉽게 만드는 별칭을 사용합니다. 다음은 상당히 전형적인 서브 쿼리 조인의 모습입니다. 이 경우 부모 테이블 Buy 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

적용되는 & 합법적 인 합류 (CROSS APPLY & LATERAL JOIN)

매우 흥미로운 유형의 JOIN은 LATERAL JOIN (PostgreSQL 9.3 이상에서 새로 도입되었습니다)입니다.
이는 SQL-Server & Oracle에서 CROSS APPLY / OUTER APPLY로 알려져 있습니다.

기본 개념은 참여하는 모든 행에 테이블 반환 함수 (또는 인라인 하위 쿼리)가 적용된다는 것입니다.

이렇게하면 예를 들어 첫 번째로 일치하는 항목 만 다른 테이블에 조인 할 수 있습니다.
일반 조인과 측면 조인의 차이점은 이전에 "적용한" 하위 쿼리에서 조인 한 열을 사용할 수 있다는 것입니다.

통사론:

PostgreSQL 9.3 이상

왼쪽 | 오른쪽 | 내부 JOIN LATERAL

SQL 서버 :

교차 | 어포 적용

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의 한 유형은 FULL JOIN입니다.
(참고 : FULL JOIN은 MySQL에 의해 2016 년까지 지원되지 않습니다)

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) 

재귀 JOIN

재귀 조인은 종종 부모 - 자식 데이터를 얻기 위해 사용됩니다. 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에 합류 ( 참조 ) :

아래는 이 답변의 예입니다.

예를 들어 아래와 같은 두 개의 테이블이 있습니다.

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

(1,2)는 A에 고유하고, (3,4)는 공통이며, (5,6)는 B에만 고유합니다.

내부 결합

상응하는 쿼리 중 하나를 사용하는 내부 조인은 두 테이블, 즉 그들이 공통으로 가지고있는 두 개의 행의 교차를 제공합니다.

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 용어 : 내부, 외부, 세미, 안티 ...

두 개의 테이블 (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