- 카테고리:
조인 관련 작업하기¶
조인에서는 두 테이블의 행을 결합하여, 쿼리에 사용할 수 있는 결합된 새 행을 생성합니다.
소개¶
조인은 테이블의 데이터가 연관된 경우에 유용합니다. 예를 들어, 한 테이블에는 프로젝트에 대한 정보가 포함되고, 한 테이블에는 해당 프로젝트 관련 작업을 수행하는 직원에 대한 정보가 포함될 수 있습니다.
CREATE TABLE projects (
project_id INT,
project_name VARCHAR);
INSERT INTO projects VALUES
(1000, 'COVID-19 Vaccine'),
(1001, 'Malaria Vaccine'),
(1002, 'NewProject');
CREATE TABLE employees (
employee_id INT,
employee_name VARCHAR,
project_id INT);
INSERT INTO employees VALUES
(10000001, 'Terry Smith', 1000),
(10000002, 'Maria Inverness', 1000),
(10000003, 'Pat Wang', 1001),
(10000004, 'NewEmployee', NULL);
테이블을 쿼리하여 데이터를 확인합니다.
SELECT * FROM projects ORDER BY project_ID;
+------------+------------------+
| PROJECT_ID | PROJECT_NAME |
|------------+------------------|
| 1000 | COVID-19 Vaccine |
| 1001 | Malaria Vaccine |
| 1002 | NewProject |
+------------+------------------+
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 |
+-------------+-----------------+------------+
조인된 두 테이블은 일반적으로 하나 이상의 공통 열을 포함하므로 한 테이블의 행을 다른 테이블의 해당 행과 연결할 수 있습니다. 예를 들어, 이러한 샘플 테이블에서 프로젝트 테이블의 각 행에는 고유한 프로젝트 ID 번호가 있으며, 직원 테이블의 각 행에는 직원이 현재 할당된 프로젝트의 ID 번호가 포함됩니다.
조인 작업은 한 테이블의 행을 다른 테이블의 해당 행과 연결하는 방법을 명시적 또는 암시적으로 지정합니다. 일반적으로 project_id``와 같은 하나 이상의 공통 열을 참조합니다. 예를 들어, 다음은 이전에 생성된 ``projects 및 employees 테이블을 조인합니다.
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 |
+------------+------------------+-------------+-----------------+------------+
단일 조인 작업은 두 테이블만 조인할 수 있지만, 조인은 함께 연결할 수 있습니다. 조인의 결과는 테이블과 유사한 오브젝트이며, 해당 테이블과 유사한 오브젝트는 다른 테이블과 유사한 오브젝트에 조인될 수 있습니다. 개념적으로, 이 아이디어는 다음과 유사하며, 이는 실제 구문이 아닙니다.
table1 JOIN (table2 JOIN table3)
이 의사 코드에서는 table2 및 ``table3``이 먼저 조인됩니다. 그런 다음 해당 조인의 결과 테이블은이 ``table1``과 조인됩니다.
조인은 테이블뿐만 아니라 테이블과 유사한 다른 오브젝트에도 적용할 수 있습니다. 조인이 가능한 오브젝트는 다음과 같습니다.
테이블.
뷰 (구체화된 뷰 또는 구체화되지 않은 뷰).
테이블과 동등한 것으로 간주되는 식(1개 이상의 열 및 0개 이상의 행 포함). 예:
테이블 함수 에서 반환된 결과 세트.
테이블을 반환하는 하위 쿼리에서 반환된 결과 세트.
이 항목에서 테이블 조인에 대한 설명은 일반적으로 테이블과 유사한 오브젝트를 조인하는 것을 의미합니다.
참고
Snowflake는 불필요한 조인을 제거하여 성능을 개선할 수 있습니다. 자세한 내용은 Snowflake가 중복 조인을 제거하는 방법 이해하기 섹션을 참조하세요.
조인 유형¶
Snowflake에서 지원하는 조인의 타입은 다음과 같습니다.
참고
Snowflake는 시계열 데이터 분석을 위한 ASOF JOIN도 지원합니다. 자세한 내용은 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 |
+------------+------------------+-------------+-----------------+------------+
이 예제에서 출력에는 이름이 PROJECT_ID``인 두 개의 열이 포함됩니다. 하나의 ``PROJECT_ID 열은 projects 테이블에서 가져오고 다른 하나의 열은 employees 테이블에서 가져온 것입니다. 출력의 각 행에 대해 두 PROJECT_ID 열의 값은 쿼리에서 ``e.project_id = p.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 |
+------------------+-----------------+
employees 테이블에 일치하는 행이 없는 경우에도 ``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 |
+------------------+-----------------+
전체 외부 조인에서는 모든 프로젝트 및 모든 직원이 나열됩니다.
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 |
+------------------+-----------------+
크로스 조인¶
크로스 조인은 첫 번째 테이블의 각 행을 두 번째 테이블의 각 행과 결합하여 가능한 모든 행 조합을 생성하며, 이를 데카르트 곱 이라고 합니다. 대부분의 결과 행에는 실제로는 관련이 없는 행의 일부가 포함되어 있으므로, 크로스 조인 자체는 거의 유용하지 않습니다. 실제로, 크로스 조인은 조인 조건을 실수로 생략했을 때 주로 발생합니다.
크로스 조인의 결과는 매우 크고 비용이 많이 들 수 있습니다. 첫 번째 테이블에 N개의 행이 있고 두 번째 테이블에 M개의 행이 있는 경우, 결과는 N x M개의 행입니다. 예를 들어, 첫 번째 테이블에 100개의 행이 있고 두 번째 테이블에 1,000개의 행이 있는 경우 결과 세트에는 100,000개의 행이 포함됩니다.
다음 쿼리는 크로스 조인을 보여줍니다.
참고
이 쿼리에는 ON 절이 포함되어 있으며 필터가 없습니다.
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 |
+------------------+-----------------+
크로스 조인의 출력은 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 |
+------------------+-----------------+
이 크로스 조인 및 필터의 결과는 다음 내부 조인의 결과와 같습니다.
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 |
+------------------+-----------------+
중요
이 예제에서는 2개의 쿼리가 다른 절(WHERE 및 FROM ... ON ...)에서 동일한 조건(e.project_id = p.project_id)을 사용할 때 동일한 출력을 생성하지만, 동일한 조건을 사용하지만 동일한 출력을 생성하지 않는 쿼리 페어를 구성하는 것이 가능합니다.
가장 일반적인 예제는 외부 조인과 관련이 있습니다. table1 LEFT OUTER JOIN table2``를 실행하면 일치하는 항목이 없는 ``table1``의 행에 대해 ``table2``에서 가져온 열에 NULL이 포함됩니다. ``WHERE table2.ID = table1.ID``와 같은 필터는 ``table2.id 또는 table1.id``에 NULL이 포함된 행을 필터링하는 반면, ``FROM ... ON ... 절의 명시적 외부 조인은 NULL 값이 있는 행을 필터링하지 않습니다. 즉, 필터를 사용한 외부 조인은 외부 조인처럼 작동하지 않을 수 있습니다.
자연 조인¶
자연 조인은 이름이 동일하고 호환되는 데이터 타입이 있는 열의 두 테이블을 조인합니다. employees 및 projects 모두 이전에 생성된 테이블에 이름이 project_ID``인 열이 있습니다. 자연 조인은 암시적으로 :code:`ON` 절(``ON projects.project_ID = employees.project_ID)을 구성합니다.
두 테이블에 공통된 여러 열이 있는 경우 자연 조인은 구성된 ON 절의 모든 공통 열을 사용합니다. 예를 들어, 두 테이블에 각각 이름이 city 및 ``province``인 열이 있는 경우 자연 조인은 다음 ON 절을 구성합니다.
ON table2.city = table1.city AND table2.province = table1.province
자연 조인의 출력에는 각 공유 열의 복사본이 하나만 포함됩니다. 예를 들어, 다음 쿼리는 두 테이블의 모든 열을 포함하는 자연 조인을 생성합니다. 단, 중복 project_id 열의 복사본 하나를 제외한 모든 열이 생략됩니다.
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 |
+------------+------------------+-------------+-----------------+
자연 조인은 외부 조인과 결합할 수 있습니다.
조인 조건이 이미 내포되어 있으므로 자연 조인 ON 절을 결합할 수 없습니다. 그러나 WHERE 절을 사용하면 자연 조인의 결과를 필터링할 수 있습니다.
조인 구현하기¶
구문적으로 테이블을 조인하는 방법은 다음의 두 가지입니다.
Snowflake에서는 구문이 더 유연하기 때문에 FROM 절에 ON 하위 절을 사용하는 것을 권장합니다. 또한, ON 하위 절에 조건자를 지정하면 외부 조인의 조인 조건을 지정하기 위해 WHERE 절을 사용할 때 실수로 NULL 값이 있는 행을 필터링하는 문제를 방지할 수 있습니다.
또한 DIRECTED 키워드를 사용하여 테이블의 조인 순서를 적용할 수 있습니다. 이 키워드를 지정하면 첫 번째 또는 왼쪽 테이블이 두 번째 또는 오른쪽 테이블보다 먼저 스캔됩니다. 예를 들어, o1 INNER DIRECTED JOIN o2`는 :samp:`o1 테이블을 먼저 스캔한 후 o2 테이블을 스캔합니다. DIRECTED 키워드가 추가된 경우, 조인 유형(예: INNER 또는 OUTER)은 필수입니다. 자세한 내용은 JOIN 섹션을 참조하십시오.
참고
방향성 조인은 모든 계정에서 사용할 수 있는 :doc:`미리 보기 기능</release-notes/preview-features>`입니다.