조인 관련 작업하기¶

내부 조인¶

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

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

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            |
+------------------+-----------------+

직원 테이블에 일치하는 행이 없는 경우에도 이 출력에는 이름이 “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

자연 조인¶

자연 조인은 이름이 같고 해당 열의 데이터가 해당하는 열이 두 테이블에 포함된 경우에 사용됩니다. 위의 직원 및 프로젝트 테이블에서 두 테이블에는 이름이 “프로젝트_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 절을 사용하여 자연 조인의 결과를 필터링할 수 있습니다.