[Database] 조인(JOIN)의 종류(MySQL 기준)

   이번 글에서는 간단한 예를 통해 SQL JOIN문의 종류에 대해 알아보고자 합니다. 조인의 개념에 대한 표준은 정해져 있지만 RDBMS의 Vendor마다 그 구체적인 Syntax는 약간의 차이가 존재합니다. 이 글에서는 MySQL을 기준으로 JOIN을 설명함을 먼저 밝힙니다.

 조인을 크게 INNER JOIN, OUTER JOIN 그리고 그 외의 JOIN(SELF JOIN, CROSS JOIN 등)으로 분류할 수 있습니다. 이 중 실질적으로 가장 자주 사용되는 INNER JOIN과 OUTER JOIN(LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN), CROSS JOIN에 대해서만 알아보겠습니다.

 

 학생(students)과 지도교수(professors) 테이블

 아래에 두 개의 테이블이 존재하는데요. 각각 지도교수 테이블과 학생 테이블입니다. 지도교수 테이블의 기본키(Primary Key)는 professor_id이고 학생 테이블에서는 같은 이름의 칼럼(Column)으로 지도교수 테이블의 기본키를 참조합니다. 즉 학생 테이블에서는 professor_id 칼럼이 지도교수 테이블에 대한 외래키인 것이죠. 이제 이 두 테이블에 여러 조인을 시도해보고 그 결과를 통해 각 JOIN문이 의미하는 바를 알아보겠습니다.

 

지도교수(professors) 테이블

학생(students) 테이블

 

INNER JOIN

 INNER JOIN은 어떤 테이블 A와 B가 존재할 때, 특정한 칼럼을 기준으로 같은 값을 같는 레코드들을 묶어서 하나의 가상 테이블로 뽑아내기 위해 사용합니다. 이것을 집합 관계중 하나인 교집합을 이용하여 매우 직관적으로 표현할 수 있는데요. 학생 테이블과 지도교수 테이블의 INNER JOIN을 한 결과 나오게 되는 테이블은 아래의 교집합 관계를 만족합니다.

INNER JOIN Diagram

 

- SQL

MySQL에서 INNER JOIN을 실행하기 위한 SQL Syntax는 다음과 같습니다.

 

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

 

INNER JOIN을 한 결과 테이블에 포함될 칼럼명을 SELECT 키워드 다음에 나열하고 기본 테이블명을 FROM 키워드 뒤에 명시합니다. 그리고 바로 뒤가 핵심인데요. INNER JOIN 키워드를 적은 후 그 뒤에 조인할 테이블명을 명시합니다. 그리고 ON 키워드 이후에는 기본 테이블의 조인 속성과 조인할 테이블의 조인 속성을 '='를 사이에 두고 명시합니다. 즉, 기준 테이블의 칼럼 값과 조인할 테이블의 칼럼 값이 같은 레코드만을 조인의 대상으로 하겠다는 의미이죠.

 

- INNER JOIN 실행 결과

 그렇다면 위에 제가 만들어 놓은 두 테이블에 INNER JOIN을 직접 해 보고 그 결과를 살펴보겠습니다.

SELECT * FROM students s
	INNER JOIN professors p ON s.professor_id = p.professor_id;

INNER JOIN 결과 테이블

결과 테이블에 나온 레코드를 자세히 살펴보면, 학생 테이블과 지도교수 테이블이 수평으로 합쳐진 것을 볼 수 있습니다. 하지만 학생 테이블에 있던 레코드 전부와 지도교수 테이블에 있던 레코드 전부를 그대로 합쳐놓은 것이 아닙니다. 학생 레코드와 지도교수 레코드 중에서 professor_id가 같은 각 레코드를 수평으로 붙인 후 그러한 것들을 전부 모아 결괏값으로써 하나의 테이블로 구성하였네요.

 또 하나 눈여겨볼 점이 있습니다. 위의 INNER JOIN 결과 테이블을 보면 조인의 기준이 되는 칼럼인 professor_id가 중복되어서 나란히 나타난 것을 확인할 수 있는데요. 이렇게 INNER JOIN의 결과 테이블은 앞서 언급한 바와 같이 조인 테이블을 단순히 수평으로 합쳐서 구성하기 때문에 조인의 기준이 되는 칼럼이 중복되어 나타나게 됩니다. 만약 이 중복을 제거하고 싶다면 SELECT 키워드 다음에 칼럼명을 직접 명시하거나 NATURAL JOIN을 사용하면 됩니다(NATURAL JOIN은 조인할 기준이 되는 칼럼명과 자료형을 자동으로 바인딩하여 조인 결괏값을 보여주는 반면, INNER JOIN은 ON 키워드 뒤에 직접적으로 조인할 기준이 되는 칼럼명을 명시해야 한다는 차이점이 존재하기는 합니다).

 

LEFT OUTER JOIN

 OUTER JOIN에는 LEFT, RIGHT, FULL 이렇게 세 가지 형태의 조인이 존재합니다. 그중 먼저 LEFT JOIN을 설명하겠습니다.

 LEFT OUTER JOIN은 어떤 테이블 A와 B가 존재할 때, A의 모든 레코드에 B의 레코드를 수평으로 붙여서(조인하여) 하나의 테이블을 구성합니다. 단, 이때 A에서 B의 조인할 칼럼과 같은 값을 갖는 레코드는 앞서 설명한 INNER JOIN과 같이 나타내고 B의 조인할 칼럼과 같은 값을 갖는 레코드가 A에 없을 때는 NULL 값을 넣어서 나타냅니다. 어찌 되었든 두 테이블을 조인하여 하나의 테이블을 구성하되, 테이블 A에 원래 존재했던 레코드는 모두 포함하겠다는 것이 핵심입니다. 이를 집합 관계로 나타내면 다음과 같습니다.

LEFT (OUTER) JOIN Diagram

 

- SQL

MySQL에서 LEFT OUTER JOIN을 실행하기 위한 SQL Syntax는 다음과 같습니다.

 

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

 

위에서 LEFT JOIN 키워드를 보면 OUTER가 빠진 것을 볼 수 있습니다. 사실 LEFT OUTER JOIN과 LEFT JOIN은 동일합니다. 따라서 OUTER를 생략해도 무방합니다. 이 외에 SQL JOIN문에 대한 문법적인 설명은 위의 INNER JOIN과 같습니다.

 

- LEFT JOIN 실행 결과

SELECT * FROM students s
	LEFT JOIN professors p ON s.professor_id = p.professor_id;

LEFT (OUTER) JOIN 결과 테이블

 앞서 설명한 그대로 결과에 반영되어 나타난 것을 확인할 수 있습니다. 즉 학생 레코드의 professor_id와 지도교수 레코드의 professor_id가 같은 레코드를 수평으로 합쳐서 결과 테이블을 구성하되, 학생 레코드 중 professor_id값이 지도교수 레코드의 professor_id와 일치하는 값을 찾을 수 없을 경우는 NULL 값을 넣어서 표현합니다. 위의 INNER JOIN과는 달리 학생 테이블에 있던 모든 레코드가 포함되어 있는 것을 볼 수 있습니다.

 

RIGHT OUTER JOIN

 LEFT OUTER JOIN을 이해했다면 RIGHT OUTER JOIN도 이해했다고 볼 수 있습니다. 이름에서 나타나듯이 이 둘은 방향만 바뀌었다는 차이만 존재할뿐이니까요. LEFT OUTER JOIN은 FROM 키워드 다음에 명시했던 기본 테이블에 있는 모든 레코드를 결과 테이블에 포함하였지만, RIGHT OUTER JOIN은 그와는 반대로 조인할 테이블에 있는 모든 레코드를 포함합니다. 이를 집합 관계로 나타내면 다음과 같습니다.

RIGHT (OUTER) JOIN Diagram

 

- SQL

MySQL에서 RIGHT OUTER JOIN을 실행하기 위한 SQL Syntax는 다음과 같습니다.

 

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

 

 앞선 LEFT OUTER JOIN과 마찬가지로 RIGHT OUTER JOIN에서도 OUTER 키워드가 생략될 수 있습니다. 나머지는 LEFT OUTER JOIN에서의 설명과 동일합니다.

 

- RIGHT JOIN 실행 결과

SELECT * FROM students s
	RIGHT JOIN professors p ON s.professor_id = p.professor_id;

RIGHT (OUTER) JOIN 결과 테이블

 LEFT OUTER JOIN에서의 결과와는 다르게 이번에는 조인할 테이블인 지도교수 테이블에 존재하는 레코드가 모두 결과 테이블에 포함된 것을 볼 수 있습니다. 한편, LEFT OUTER JOIN과 마찬가지로 매칭되지 못한 칼럼에는 모두 NULL값이 들어있는 것을 확인할 수 있네요.

 

FULL OUTER JOIN

 FULL OUTER JOIN은 LEFT OUTER JOIN과 RIGHT OUTER JOIN을 모두 합한 개념이라고 볼 수 있습니다. 실제로도 집합 관계를 표현했을 때 합집합으로 표현하는데요, 나타내면 다음과 같습니다.

FULL (OUTER) JOIN Diagram

 

- SQL

MySQL에서 RIGHT OUTER JOIN을 실행하기 위한 SQL Syntax는 다음과 같습니다.

 

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

 

앞선 LEFT OUTER JOIN, RIGHT OUTER JOIN과 마찬가지로 FULL OUTER JOIN에서도 OUTER 키워드가 생략될 수 있습니다.

 MySQL에서 FULL OUTER JOIN을 할 때 주의할 점이 있는데요. MySQL에서는 바로 위의 문법 그대로를 지원하지 않는 다는 점입니다. 그렇다면 MySQL에서는 FULL OUTER JOIN을 할 수 없는 걸까요? 당연히 그렇지 않습니다. UNION 키워드를 사용한다면 FULL OUTER JOIN과 동일한 결과 테이블을 얻을 수 있습니다. UNION은 집합에서 합집합을 의미합니다. 앞서 FULL OUTER JOIN의 개념은 LEFT OUTER JOIN과 RIGHT OUTER JOIN의 합집합과 같다고 언급했는데요. 바로 이 점을 이용하는 것이죠. 즉 LEFT OUTER JOIN의 결과와 RIGHT OUTER JOIN의 결과를 UNION으로 엮습니다. 

 

- FULL OUTER JOIN 실행 결과

SELECT * FROM students s
	LEFT JOIN professors p ON s.professor_id = p.professor_id
UNION
SELECT * FROM students s
	RIGHT JOIN professors p ON s.professor_id = p.professor_id;

FULL (OUTER) JOIN 결과 테이블

 SQL문이 앞서 언급한 FULL OUTER JOIN의 syntax를 그대로 따르지는 않지만, 그 결괏값을 살펴보면 개념적으로 봤을 때 FULL OUTER JOIN이 의미하는 바와 정확히 같습니다. 가장 위의 세 개의 레코드는 INNER JOIN을 실행했을 때의 결과입니다. 그리고 여기에 그 아래 두 개의 레코드를 포함한다면 LEFT OUTER JOIN의 결과이구요, 끝에서부터 두 개의 레코드를 포함한다면 RIGHT OUTER JOIN의 결과입니다. 결론적으로 LEFT OUTER JOIN과 RIGHT OUTER JOIN의 합집합 연산을 했을 때의 결괏값을 하나의 테이블로 뽑아낸 것입니다.

 

CROSS JOIN

 CROSS JOIN은 지금까지 살펴본 INNER JOIN 및 OUTER JOIN과는 전혀 다른 성질을 지닙니다. INNER JOIN 및 OUTER JOIN에서는 JOIN을 수행하기 위해 기준이 될 칼럼을 ON 키워드 뒤에 명시하여 제약 조건을 걸어 주었는데요. CROSS JOIN은 어떠한 제약 조건도 명시할 필요가 없습니다. 그저 어떤 테이블 A와 B가 존재할 때, A에 존재하는 모든 레코드를 B에 존재하는 레코드 각각에 한 번씩 수평으로 붙인 레코드를 형성하여 결과 테이블을 구성합니다.

 

- SQL

MySQL에서 CROSS JOIN을 실행하기 위한 SQL Syntax는 다음과 같습니다.

 

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

 

문법적인 구조가 매우 간결합니다. CROSS JOIN 키워드와 함께 조인할 테이블명을 명시해 주기만 하면 되죠. 그렇다면 앞선 학생 테이블에 지도교수 테이블을 CROSS JOIN한 결과를 한 번 살펴보겠습니다.

 

- CROSS JOIN 실행 결과

SELECT * FROM students s
	CROSS JOIN professors p;

CROSS JOIN 결과 테이블

 뭔가 결과 테이블이 복잡하고 비대해진 것을 볼 수 있습니다. 앞서 설명한 바와 같이 기본 테이블인 학생 테이블에 있는 모든 레코드를 조인할 테이블인 지도교수 테이블의 레코드 각각에 그대로 붙여서 레코드를 만든 뒤, 이것들을 모두 모아 결과 테이블로 구성하기 때문입니다. 학생 테이블에는 5개의 레코드가 있고 지도교수 테이블에도 5개의 레코드가 있습니다. 즉 CROSS JOIN을 통해 만들어진 결과 테이블은 현재 총 5 * 5 = 25개의 레코드를 포함하고 있습니다.

 이러한 결과를 바탕으로 일반화 해보면, 테이블 A에 레코드가 M개이고 테이블에 B에 레코드가 N개 존재할 때 이 두 테이블을 CROSS JOIN한 결과 테이블은 총 M * N개의 레코드를 포함하게 됩니다. 이것을 카티션 곱(Cartesian Product), 즉 데카르트 곱이라고도 부르는데 그 이유는 CROSS JOIN의 연산이 해석학에서 정의하고 있는 집합에 대한 데카르트 곱과 같기 때문입니다.

 위에서는 간단한 예를 위해 두 테이블에 레코드를 각각 5개씩만 넣었기 때문에 CROSS JOIN에 대한 연산이 큰 무리가 없지만, 실제 쓰이는 DB의 테이블에 CROSS JOIN을 수행할 경우 데이터베이스 시스템에 엄청난 부하가 걸릴 것입니다. 따라서 CROSS JOIN은 사용하지 않는 것이 좋으며 실제에서도 거의 사용되지 않습니다.

 

참고

SQL Joins, https://www.w3schools.com/sql/sql_join.asp