[데이터베이스] 조인연산(Join Operation)

Join

조인은 다수의 테이블을 연결해서 데이터를 뽑아내는 연산을 의미합니다. 기본적으로 Join 은 정규화된 테이블을 사용하게 되고 여러 종류의 조인연산들을 정리해봅시다.

Natural Join

Natural 은 두 테이블에서 같은 속성이름을 기준으로 테이블을 합쳐서 새로운 테이블을 만드는 것을 의미합니다. 그리고 중복되는 속성들은 합쳐서 하나로 만듭니다.

students_info

student_id	student_name	age	sex
1	여훈	26	M
2	윤이	24	F
3	현기	21	M

students_department

student_id	Department
1	CSEE
2	CPSW
3	CSEE

위와 같은 두 테이블이 있다면, Natural Join을 했을 때, student_id 를 기준으로 합쳐져서 다음과 같은 테이블을 만들 수 있습니다. 두 테이블에서 Student_id 가 각각 존재하지만 중복되는 속성이기 때문에 조인 이후에는 하나가 된 것을 볼 수 있습니다.

student_id	student_name	age	sex	Department
1	여훈	26	M	CSEE
2	윤이	24	F	CPSW
3	현기	21	M	CSEE

MySQL 에서 Natural Join을 할때는 다음과 같은 문법을 사용합니다.

SELECT *
FROM students_info, students_department
WHERE students_info.student_id = student_department.student_id

or

SELECT *
FROM students_info NATURAL JOIN students_department

첫번째 쿼리는 Natural Join을 직접적으로 사용하지는 않았지만, WHERE 문을 통해서 student_id 가 같은 튜플을 뽑기로 했기 때문에 NATURAL JOIN 을 사용한 것과 동일한 결과를 얻게됩니다. 두번째 쿼리는 NATURAL JOIN 연산을 이용해서 두 테이블의 공통된 속성들의 값을 자동으로 찾아 테이블을 합쳐줍니다. 이때 주의할 점은 Natural Join은 사용자가 지정하지 않고 연산과정에서 동일한 속성의 이름을 찾아서 join 하기 때문에 속성의 이름만 같고 그 의미가 다른 테이블들이 합쳐지면서 정보가 훼손 될 가능성이 있습니다.

SELECT name, title
FROM student, NATURAL JOIN takes NATURAL JOIN course

이런 쿼리를 사용하다면, 한번에 세 테이블을 합치면서 세 테이블이 가진 동일한 속성의 의미가 달라지는 경우가 생길 수 있게됩니다.

Join (Inner Join)

Natural Join의 문제점은 동일한 이름의 속성을 마음대로 하나로 합쳐버린다는 것이었습니다. 이를 해결하기 위해서 Inner Join 연산을 사용할 수 있습니다. Natural Join은 ON 연산의 사용을 허용합니다. Where 처럼 ON을 통해서 테이블을 합칠 조건을 직접 명시해줄 수 있습니다.

students_info

student_id	student_name	age	sex
1	여훈	26	M
2	윤이	24	F
3	현기	21	M

students_department

student_id	Department
1	CSEE
2	CPSW
3	CSEE

앞서 사용했던 테이블을 Natural Join을 통해서 합칠 때는

SELECT *
FROM students_info NATURAL JOIN students_department

이런 쿼리를 사용할 수 있습니다. 우리는 쿼리를 통해서 어떤 속성이 같은 이름을 가지는지 명시하지 않았지만, Natural Join이 해당 속성을 알아서 찾고 합쳐주었습니다.

Inner Join 을 사용하게 되면

SELECT *
FROM students_info JOIN students_department
ON tudents_info.student_id = student_department.student_id

이렇게 어떤 속성을 기준으로 합칠 것인지 지정해줄 수가 있다. 따라서, 중복되는 속성이 여러개 있을 때, 어떤 속성을 기준으로 합칠지 사용자가 선택해줄 수 있습니다. 만약, 두 테이블에 값이 일치하지 않는 튜플들이 있다면 어떻게 될까요? 이런 튜플들은 모두 삭제되고 완벽하게 일치하는 튜플들만 새로 합쳐진 테이블에 남게됩니다.

Outer Join

Natural Join 과 Inner Join은 두 테이블에서 일치하는 값이 없는 튜플들을 모두 없애버리게 되는데, 어떤 경우에는 해당 정보들이 필요할지도 모릅니다.

Outer Join 은 Inner Join 이나 Natural Join 과는 다르게 짝을 찾지 못한 속성값들도 결과 테이블에 유지합니다. 그리고 Outer Join의 종류에 따라 짝을 찾지 못한 튜플들 중 어떤 종류의 튜플들만 남길지 선택할 수 있다. 짝을 찾지 못하게 되면 합쳐져서 새로 추가된 속성에 넣어줄 값이 존재하지 않기 때문에, 해당 속성의 값은 NULL로 최기화하게 됩니다.

Left Outer Join

Left Outer Join은 join 연산을 수행할 때 왼쪽에 위치시켰던 테이블의 튜플은 모두 남기고, 오른쪽에 위치시켰던 테이블은 값이 일치하는 튜플만 남기는 연산입니다.

students_info

student_id	student_name	age	sex
1	여훈	26	M
2	윤이	24	F
3	현기	21	M
4	국인	22	M

students_department

student_id	Department
1	CSEE
2	CPSW
3	CSEE
5	SWFL

위 테이블은 이유는 모르겠지만 국인이의 정보가 department 테이블에 없고, id 5번의 정보가 student 테이블에 없습니다. Inner Join 과 Natural Join 을 사용했다면, 국인이와 id 5번 학생의 정보가 사라졌겠지만, 이번엔 그렇지 않습니다. Left Outer Join 이기 때문에 왼쪽에 기준으로 테이블을 합치는 연산을 수행하게 됩니다.

student_id	student_name	age	sex	Department
1	여훈	26	M	CSEE
2	윤이	24	F	CPSW
3	현기	21	M	CSEE
4	국인	22	M	null

Join 을 수행했을 때, 서로 공유되는 속성은 student_id 입니다. 하지만 왼쪽 테이블에 있는 국인이에 대한 정보가 오른쪽 테이블에는 존재하지 않습니다. Left Outer Join 은 이때 왼쪽에만 존재하는 정보를 유지시키고 합쳤을 때 비어있게 되는 속성들에 null을 삽입합니다. 따라서 왼쪽 테이블을 기준으로 합쳤을 때 추가되는 Department 에 국인이에 대한 값이 null이 들어가게 되는 것입니다.

쿼리문은 다음과 같이 작성합니다.

SELECT *
FROM students_info NATURAL LEFT OUTER JOIN students_department

Right Outer Join

Right Outer Join 은 Left Outer Join 과는 반대로 오른쪽 테이블에 있는 정보를 유지시키는 연산입니다.

Left Outer Join 에서 사용한 것과 동일한 테이블은 Right Outer Join으로 합쳐보겠습니다.

students_info

student_id	student_name	age	sex
1	여훈	26	M
2	윤이	24	F
3	현기	21	M
4	국인	22	M

students_department

student_id	Department
1	CSEE
2	CPSW
3	CSEE
5	SWFL

위 두 테이블을 오른쪽을 기준으로 합치면,

student_id	Department	student_name	age	sex
1	CSEE	여훈	26	M
2	CPSW	윤이	24	F
3	CSEE	현기	21	M
5	SWFL	null	null	null

이렇게 국인이의 정보는 포함하지 않지만 오른쪽 테이블에 있던 id 5번의 정보는 포함한 채로 합쳐지는 것을 확인할 수 있습니다.

쿼리문은 다음과 같이 작성할 수 있습니다.

SELECT *
FROM students_info NATURAL RIGHT OUTER JOIN students_department

Full Outer Join

Full Outer Join은 양쪽 테이블의 정보를 모두 유지한채로 테이블을 합치는 연산입니다.

위에서 계속 사용했던 테이블을 full outer join 으로 합쳐보겠습니다.

students_info

student_id	student_name	age	sex
1	여훈	26	M
2	윤이	24	F
3	현기	21	M
4	국인	22	M

students_department

student_id	Department
1	CSEE
2	CPSW
3	CSEE
5	SWFL

두 테이블을 합치면,

student_id	student_name	age	sex	Department
1	여훈	26	M	CSEE
2	윤이	24	F	CPSW
3	현기	21	M	CSEE
4	국인	22	M	null
5	null	null	null	SWFL

이렇게 두 양쪽 테이블의 정보를 모두 포함한 테이블이 만들어집니다. 이번에는 국인이의 정보와 id 5번의 정보가 모두 포함된 것을 확인할 수 있습니다. 그리고 비어있는 값은 null 로 초기화됩니다.

 

MySQL 에서는 Full Outer Join의 쿼리를 지원하지 않습니다. 그래서 우리는 직접 이 쿼리를 만들어서 사용할 수 있는데, 양쪽 정보를 모두 포함시키려면, Left Outer Join 과 Right Outer Join 의 내용을 모두 포함하는 것임으로 UNION 연산을 통해 왼쪽 기준으로 조인한 테이블, 오른쪽 기준으로 조인한 테이블을 합쳐주면 됩니다.

 

쿼리문은 다음과 같이 작성할 수 있습니다.

SELECT student_id, student_name, age, sex, Department
FROM students_info NATURAL LEFT OUTER JOIN students_Department

UNION

SELECT student_id, student_name, age, sex, Department
FROM students_info NATURAL RIGHT OUTER JOIN students_Department;

이렇게 두 쿼리를 합쳐주는 것으로 Full Outer Join을 구현할 수 있습니다. 하지만 명심해야할 부분은 UNION 같은 집합 연산자를 사용할 때, 두 테이블의 속성의 순서가 동일해야하기 때문에, SELECT 문에서 속성이름의 순서를 지정해줘야한다는 것입니다.