카테고리:

쿼리 구문

조인 관련 작업하기

조인에서는 두 테이블의 행을 결합하여, 쿼리에 사용할 수 있는 결합된 새 행을 생성합니다.

이 항목의 내용:

소개

조인은 테이블의 데이터가 연관된 경우에 유용합니다. 예를 들어, 한 테이블에는 프로젝트에 대한 정보가 포함되고, 한 테이블에는 해당 프로젝트 관련 작업을 수행하는 직원에 대한 정보가 포함될 수 있습니다.

SELECT * FROM projects ORDER BY project_ID;
+------------+------------------+
| PROJECT_ID | PROJECT_NAME     |
|------------+------------------|
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+
Copy
SELECT * FROM employees ORDER BY employee_ID;
+-------------+-----------------+------------+
| EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|-------------+-----------------+------------|
|    10000001 | Terry Smith     | 1000       |
|    10000002 | Maria Inverness | 1000       |
|    10000003 | Pat Wang        | 1001       |
|    10000004 | NewEmployee     | NULL       |
+-------------+-----------------+------------+
Copy

조인된 테이블 2개는 일반적으로 1개 이상의 공통 열이 포함되므로, 한 테이블의 행이 다른 테이블의 해당 행과 연결될 수 있습니다. 예를 들어, 프로젝트 테이블의 각 행에는 고유한 프로젝트 ID 번호가 포함되고 직원 테이블의 각 행에는 직원이 현재 할당된 프로젝트의 ID 번호가 포함될 수 있습니다.

조인 작업은 일반적으로 프로젝트 ID 등의 공통 열을 참조하여 한 테이블의 행을 다른 테이블의 해당 행과 연결하는 방법을 (명시적 또는 암시적으로) 지정합니다. 예를 들어, 다음에서는 위의 프로젝트 및 직원 테이블을 조인합니다.

SELECT p.project_ID, project_name, employee_ID, employee_name, e.project_ID
    FROM projects AS p JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------+------------------+-------------+-----------------+------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|------------+------------------+-------------+-----------------+------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     | 1000       |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness | 1000       |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        | 1001       |
+------------+------------------+-------------+-----------------+------------+
Copy

단일 조인 작업에서는 테이블을 2개만 조인할 수 있지만, 조인을 서로 연결할 수 있습니다. 조인의 결과는 테이블과 유사한 오브젝트이며 테이블과 유사한 해당 오브젝트는 테이블과 유사한 다른 오브젝트와 조인할 수 있습니다. 개념적인 설명은 다음과 같습니다(실제 구문이 아님).

table1 join (table2 join table 3)
Copy

이 의사 코드에서는 테이블2와 테이블3이 먼저 조인됩니다. 이후에 이 조인의 결과로 생성된 테이블이 테이블1과 조인됩니다.

조인은 테이블뿐만 아니라 테이블과 유사한 다른 오브젝트에도 적용할 수 있습니다. 조인이 가능한 오브젝트는 다음과 같습니다.

  • 테이블.

  • (구체화된 뷰 또는 구체화되지 않은 뷰).

  • 테이블 리터럴.

  • 테이블과 동등한 것으로 간주되는 식(1개 이상의 열 및 0개 이상의 행 포함). 예:

    • 테이블 함수 에서 반환된 결과 세트.

    • 테이블을 반환하는 하위 쿼리에서 반환된 결과 세트.

이 항목에서 테이블 조인에 대한 설명은 일반적으로 테이블과 유사한 오브젝트를 조인하는 것을 의미합니다.

참고

Snowflake는 불필요한 조인을 제거함으로써 성능을 향상할 수 있습니다. 자세한 내용은 Snowflake가 중복 조인을 제거하는 방법 이해하기 섹션을 참조하십시오.

조인의 타입

Snowflake에서 지원하는 조인의 타입은 다음과 같습니다.

  • 내부 조인.

  • 외부 조인.

  • 크로스 조인.

  • 자연 조인.

  • ASOF JOIN.

내부 조인

내부 조인에서는 한 테이블의 각 행을 다른 테이블의 일치하는 행과 짝을 짓습니다.

아래 예에서는 내부 조인을 사용합니다.

SELECT p.project_ID, project_name, employee_ID, employee_name, e.project_ID
    FROM projects AS p INNER JOIN employees AS e
        ON e.project_id = p.project_id
    ORDER BY p.project_ID, e.employee_ID;
+------------+------------------+-------------+-----------------+------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|------------+------------------+-------------+-----------------+------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     | 1000       |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness | 1000       |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        | 1001       |
+------------+------------------+-------------+-----------------+------------+
Copy

이 예에서, 출력 테이블에는 “프로젝트_ID”라는 열이 2개 포함되어 있습니다. 1개의 프로젝트_ID 열은 프로젝트 테이블의 열이며 1개의 열은 직원 테이블의 열입니다. 쿼리에서 e.project_id = p.project_id 가 지정되었으므로, 출력 테이블의 각 행에서 두 Project_ID 열의 값이 일치합니다.

출력에는 유효한 쌍(즉, 조인 조건에 일치하는 행)만 포함됩니다. 이 예에는 이름이 “NewProject”인 프로젝트(아직 직원이 할당되지 않음) 또는 이름이 “NewEmployee”인 직원(아직 프로젝트에 할당되지 않은 직원)에 대한 행이 없습니다.

외부 조인

외부 조인은 지정된 테이블의 모든 행을 나열하며, 해당 행이 다른 테이블과 일치하지 않는 경우에도 행이 나열됩니다. 예를 들어, 프로젝트와 직원 사이의 왼쪽 외부 조인은 아직 직원이 할당되지 않은 프로젝트를 포함하여 모든 프로젝트를 나열합니다.

SELECT p.project_name, e.employee_name
    FROM projects AS p LEFT OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
+------------------+-----------------+
Copy

직원 테이블에 일치하는 행이 없는 경우에도 이 출력에는 이름이 “NewProject”인 프로젝트가 포함됩니다. 이름이 “NewProject”인 프로젝트에 일치하는 직원 이름이 없기 대문에 직원 이름이 NULL로 설정됩니다.

오른쪽 외부 조인에서는 모든 직원(프로젝트와 관계없음)이 나열됩니다.

SELECT p.project_name, e.employee_name
    FROM projects AS p RIGHT OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NULL             | NewEmployee     |
+------------------+-----------------+
Copy

전체 외부 조인에서는 모든 프로젝트 및 모든 직원이 나열됩니다.

SELECT p.project_name, e.employee_name
    FROM projects AS p FULL OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
| NULL             | NewEmployee     |
+------------------+-----------------+
Copy

크로스 조인

크로스 조인은 첫 번째 테이블의 각 행을 두 번째 테이블의 각 행과 결합하여, 가능한 모든 행 조합(“데카르트 곱”이라고 함)을 생성합니다. 대부분의 결과 행에는 실제로 연관되지 않은 행 부분도 포함되므로, 크로스 조인 자체는 거의 유용하지 않습니다. 사실, 크로스 조인은 일반적으로 조인 조건을 실수로 생략하여 발생하는 결과입니다.

크로스 조인의 결과는 매우 큽니다(그리고 비용이 많이 소요됨). 첫 번째 테이블에 행이 N개 있고 두 번째 테이블에 행이 M개 있는 경우, 결과는 N x M개의 행입니다. 예를 들어, 첫 번째 테이블에 100개의 행이 있고 두 번째 테이블에 1,000개의 행이 있는 경우 결과 세트에는 100,000개의 행이 포함됩니다.

다음 쿼리는 크로스 조인을 보여줍니다.

SELECT p.project_name, e.employee_name
    FROM projects AS p CROSS JOIN employees AS e
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Pat Wang        |
| COVID-19 Vaccine | NewEmployee     |
| Malaria Vaccine  | Terry Smith     |
| Malaria Vaccine  | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
| Malaria Vaccine  | NewEmployee     |
| NewProject       | Terry Smith     |
| NewProject       | Maria Inverness |
| NewProject       | Pat Wang        |
| NewProject       | NewEmployee     |
+------------------+-----------------+
Copy

이 쿼리에는 ON 절과 필터가 포함되지 않았음에 유의하십시오.

크로스 조인의 결과는 WHERE 절에 필터를 적용하여 유용하게 사용할 수 있습니다.

SELECT p.project_name, e.employee_name
    FROM projects AS p CROSS JOIN employees AS e
    WHERE e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+
Copy

이 크로스 조인 및 필터의 결과는 다음 내부 조인의 결과와 같습니다.

SELECT p.project_name, e.employee_name
    FROM projects AS p INNER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+
Copy

중요

이 예에서는 2개의 쿼리가 다른 절(WHERE vs FROM ... ON ...)에서 동일한 조건(e.project_id = p.project_id)을 사용할 때 동일한 출력을 생성하지만, 동일한 조건을 사용하지만 동일한 출력을 생성하지 않는 쿼리 페어를 구성하는 것이 가능합니다.

이러한 가장 일반적인 예는 외부 조인입니다. table1 LEFT OUTER JOIN table2 를 실행하면 일치하지 않는 테이블1의 행에 대하여 테이블2의 열에 NULL이 포함됩니다. WHERE table2.ID = table1.ID 와 같은 필터는 테이블2.id 또는 테이블1.id에 NULL이 포함된 행을 필터링하지만, FROM ... ON ... 절의 명시적 외부 조인은 NULL 값이 포함된 행을 필터링하지 않습니다. 즉, 필터가 포함된 외부 조인은 실제로 외부 조인처럼 동작하지 않을 수 있습니다.

자연 조인

자연 조인은 이름이 같고 해당 열의 데이터가 해당하는 열이 두 테이블에 포함된 경우에 사용됩니다. 위의 직원 및 프로젝트 테이블에서 두 테이블에는 이름이 “프로젝트_ID”인 열이 있습니다. 자연 조인은 ON 절: ON projects.project_ID = employees.project_ID 를 암시적으로 구성합니다.

두 테이블에 공통 열이 여러 개 있는 경우, 모든 공통 열이 ON 절에서 사용됩니다. 예를 들어, 각각 이름이 “city” 및 “province”인 열이 있는 2개의 테이블이 있는 경우 자연 조인은 다음 ON 절을 구성합니다.

ON table2.city = table1.city AND table2.province = table1.province.

자연 조인의 출력에는 각 공유 열의 복사본이 1개만 포함됩니다. 예를 들어, 다음 쿼리에서는 두 테이블의 모든 열을 포함하는 자연 조인을 생성하지만, 중복 프로젝트_ID 열의 복사본은 1개를 제외하고 모두 생략됩니다.

SELECT *
    FROM projects NATURAL JOIN employees
    ORDER BY employee_ID;
+------------+------------------+-------------+-----------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   |
|------------+------------------+-------------+-----------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        |
+------------+------------------+-------------+-----------------+
Copy

자연 조인은 외부 조인과 결합할 수 있습니다.

조인 조건이 이미 내포되어 있으므로 자연 조인을 ON 절과 결합할 수 없습니다. 그러나 WHERE 절을 사용하여 자연 조인의 결과를 필터링할 수 있습니다.

조인 구현하기

구문적으로 테이블을 조인하는 방법은 다음의 두 가지입니다.

  • FROM 절의 ON 하위 절에서 JOIN 연산자를 사용합니다.

  • FROM 절과 함께 WHERE 를 사용합니다.

Snowflake는 FROM 절에서 ON 하위 절을 사용하는 것을 권장합니다. 구문은 보다 복잡합니다. 그리고 ON 절에 조건자를 지정하면 WHERE 절을 사용하여 외부 조인에 대한 조인 조건을 지정할 때 실수로 NULLs을 사용하여 행을 필터링하는 문제를 방지할 수 있습니다.