[MYSQL] 조인(JOIN)

조인에 개념에 대해 아예 무지한 상태라면 아래 포스트를 읽고 오는 것을 추천한다.

 

satisfactoryplace.tistory.com/201?category=933598

[Database] 관계대수 (Relational Algebra): 조인(JOIN), 외부 합집합(OUTER UNION)

 

SQL에서 조인은 2개 이상의 테이블에 대해 특정 조건을 만족하는 rows끼리 결합하여 새로운 테이블을 만드는 연산이다.

 

이 때 대상 테이블은 서로 같을 수도 있고, 다를 수도 있다.

내부 조인: INNER JOIN

내부 조인은 [테이블 1]과 [테이블 2]를 조인할 때,

조인 조건에서 사용된 열값이 [테이블 1]에도 존재하고, [테이블 2]에도 존재하는 rows에 대해서만 결과를 출력한다.

person_id	person_id
123456-1234567	홍길동
234567-2345678	길동홍
345678-3456789	동홍길

(Person 테이블)

 

person_id	phone_number
123456-1234567	010-1234-1234
234567-2345678	010-2345-2345
345678-3456789	010-3456-3456

(PhoneInfo 테이블)

 

여기서 각 모든 Person에 대한 {주민번호, 이름, 휴대폰번호}를 알고 싶을 때,

Person의 주민등록번호와 PhoneInfo의 주민등록번호를 기준으로 조인하면

우리가 원하는 정보을 얻을 수 있다.

SELECT Person.person_id, name, phone_number
  FROM Person, PhoneInfo
  WHERE Person.person_id = PhoneInfo.person_id;

위 쿼리에서 WHERE절이 조인의 핵심 역할을 하고 있는데,

만약 WHERE절이 없다면 결과값은 단순히 Person과 PhoneInfo의 카티션 프로덕트 값이 된다.

 

WHERE Person.person_id= PhoneInfo.person_id 에서 어떤 조건으로 조인할 것인지 명시해 주어,

카티션 프로덕트 값에서 Person.person_id= PhoneInfo.person_id인 row만 출력한다.

 

JOIN 키워드를 사용하면 조인을 하려 한다는 것을 좀더 명시적으로 보여줄 수 있다.

SELECT Person.person_id, name, phone_number
  FROM Person JOIN PhoneInfo 
  ON Person.person_id = PhoneInfo.person_id;

WHERE에 조인 조건을 명시하지 않고,

[테이블 1] JOIN [테이블 2] ON [조인조건] 처럼 사용할 수도 있다.

 

이 결과값은 위의 WHERE를 사용한 결과와 완벽히 일치한다.

 

person_id	name	phone_number
123456-1234567	홍길동	010-1234-1234
234567-2345678	길동홍	010-2345-2345
345678-3456789	동홍길	010-3456-3456

(조인 결과로 나타난 테이블)

 

그런데 만약 '동홍길'이 휴대폰 번호가 없다고 해 보자

 

그러면 PhoneInfo 테이블에는 해당 열이 존재하지 않을 것이다.

person_id	phone_number
123456-1234567	010-1234-1234
234567-2345678	010-2345-2345

(PhoneInfo 테이블)

 

내부 조인의 정의에서, 

[테이블 1]에도 해당 값이 존재하고 [테이블 2]에도 같은 값이 존재하는 rows만 출력한다고 했으므로,

PhoneInfo테이블에 존재하지 않는 '동홍길'에 대한 값은 조인 결과에 나타나지 않는다.

 

person_id	name	phone_number
123456-1234567	홍길동	010-1234-1234
234567-2345678	길동홍	010-2345-2345

(조인 결과로 나타난 테이블; 동홍길이 휴대폰번호가 없을 때)

 

외부 조인: OUTER JOIN

외부 조인은 위에서 든 예시에서처럼 한쪽 테이블에는 값이 있고 한쪽에는 없을 때,

있는 쪽의 데이터는 표시하고 없는 쪽의 데이터에는 NULL을 입력해 조인 결과에 포함시킨다.

 

외부 조인의 경우 조인 조건에서 명시된 애트리뷰트에 대해 어느 쪽(방향)의 애트리뷰트 집합이 더 큰 집합을 가지는지에 따라,

왼쪽 외부 조인(Left Outer Join), 오른쪽 외부 조인(Right Outer Join), 완전 외부 조인(Full Outer Join)으로 나뉜다.

 

왼쪽 외부 조인: LEFT OUTER JOIN

...FROM 테이블1 LEFT OUTER JOIN 테이블2 ON ...

에서 테이블1에는 조인조건에 명시한 애트리뷰트 값을 가진 row가 존재하지만 테이블 2에는 존재하지 않을 경우,

테이블 2에 존재하는 애트리뷰트에 NULL값을 주고 결과값에 포함시킨다.

=> 조인조건 ON에서 명시한 애트리뷰트에 대해, 테이블 1이 테이블 2에는 없는 값을 포함하는 row를 결과값에 포함시키려 할 때

 

person_id	person_id
123456-1234567	홍길동
234567-2345678	길동홍
345678-3456789	동홍길

(Person 테이블)

 

person_id	phone_number
123456-1234567	010-1234-1234
234567-2345678	010-2345-2345

(PhoneInfo 테이블)

 

여기에서 모든 Person의 주민번호, 이름, 휴대폰번호를 출력하되,

휴대폰번호가 없는 Person의 경우 NULL로 출력하고 싶을 땐

SELECT Person.person_id, name, phone_number
  FROM Person LEFT OUTER JOIN PhoneInfo 
  ON Person.person_id = PhoneInfo.person_id;

처럼 사용한다.

 

왼쪽에 있는 Person의 person_id에 대한 값 집합이 PhoneInfo의 person_id에 대한 값 집합보다 크므로,

왼쪽 외부 조인을 사용한다.

 

person_id	name	phone_number
123456-1234567	홍길동	010-1234-1234
234567-2345678	길동홍	010-2345-2345
345678-3456789	동홍길	NULL

(조인 결과로 나타난 테이블)

 

오른쪽 외부 조인:RIGHT OUTER JOIN

오른쪽 외부 조인은 단순히 왼쪽 외부 조인의 방향이 오른쪽으로 바뀐 것 뿐이다.

 

위와 같은 결과를 얻고 싶다면 다음처럼 작성한다.

 

SELECT Person.person_id, name, phone_number
  FROM PhoneInfo Right OUTER JOIN Person 
  ON Person.person_id = PhoneInfo.person_id;

Person과 PhoneInfo의 위치가 바뀌어 이제 오른쪽에 있는 테이블의 조인 조건 열이

더 많은 값 집합을 가지게 되었으므로 LEFT OUTER JOIN을 RIGHT OUTER JOIN으로 변경하였다.

 

완전 외부 조인:FULL OUTER JOIN

완전 외부 조인은

오른쪽 테이블의 조인 조건 애트리뷰트가 왼쪽에 존재하지 않으면 왼쪽 테이블 애트리뷰트에 모두 NULL을,

왼쪽 테이블의 조인 조건 애트리뷰트가 오른쪽에 존재하지 않으면 오른쪽 테이블 애트리뷰트에 모두 NULL을 입력한다.

 

따라서 조인 조건에 명시된 애트리뷰트에 대해서

왼쪽과 오른쪽 테이블에 있는 모든 애트리뷰트 값들이 결과값에 등장한다.

 

Person테이블에서 '길동홍'이라는 사람이 사망하여 Person 테이블에서 삭제되었다고 해보자.

person_id	person_id
123456-1234567	홍길동
345678-3456789	동홍길

(Person 테이블)

 

person_id	phone_number
123456-1234567	010-1234-1234
234567-2345678	010-2345-2345

(PhoneInfo 테이블)

 

이제 Person과 PhoneInfo 테이블에서 person_id가

345678-3456789인 열과 234567-2345678인 열은 각각 한쪽 테이블에서만 나타난다.

 

이 때 사망한 사람과 휴대폰 번호가 없는 모든 사람을 포함해서

주민번호, 이름, 휴대폰번호를 알고 싶은 경우 FULL OUTER JOIN을 사용하여 아래 쿼리를 사용한다.

 

SELECT Person.person_id, name, phone_number
  FROM PhoneInfo FULL OUTER JOIN Person 
  ON Person.person_id = PhoneInfo.person_id;

person_id	name	phone_number
123456-1234567	홍길동	010-1234-1234
234567-2345678	NULL	010-2345-2345
345678-3456789	동홍길	NULL

(조인 결과로 나타난 테이블)

 

다이어그램