2-1-7. 조인(JOIN) [ SQL 가이드 ]

 

 

---

드디어 7절, 조인이 등장했습니다!

지금까지는 하나의 테이블에서 원하는 데이터를 출력하는 쿼리만 작성했는데, 조인을 활용하게 되면 여러개의 테이블에서 원하는 정보를 쏙쏙 골라서 출력할 수 있게 됩니다 ^^

---

 

과목 II. SQL 기본과 활용
  - 제1장 SQL 기본
    > 제7절 조인

 

1. 조인(JOIN)

두 개 이상의 테이블들을 연결해 데이터를 출력하는 것을 조인(JOIN)이라고 한다. 조인은 관계형 데이터베이스의 가장 큰 장점이면서 핵심 기능이라고 할 수 있다.

일반적인 경우 행들은 PK(PRIMARY KEY)나 FK(FOREIGN KEY) 값의 연관에 의해 조인이 성립된다.

하지만 어떤 경우에는 PK,FK의 관계가 없어도 논리적인 값들의 연관만으로 조인이 성립될 수도 있다.

예를 들면, 선수라는 테이블과 팀이라는 테이블이 있다고 하자. 선수 테이블을 기준으로 필요한 데이터를 검색하고 이 데이터와 연관된 팀 테이블의 특정 행들을 찾아서 연결하는 것이 조인이다. 팀과 운동장 테이블도 조인 조건을 통해 필요한 데이터를 조합해서 가져올 수 있으며, 하나의 SQL문장에서 선수,팀,운동장 등 여러 테이블을 조인해서 사용할 수도 있다.

다만 한가지 주의할점은, FROM절에 여러 테이블이 나열되더라도, SQL에서 데이터를 처리할 때는 단 두개의 집합간에만 조인이 일어난다. FROM절에 A,B,C 테이블이 나열됐더라도 특정 2개의 테이블만 먼저 조인 처리되고, 조인의 결과인 중간 데이터 집합과 남은 한 개의 테이블이 다음차례로 조인되는것이다.

이때 테이블 조인 순서는 내부적으로 DBMS(옵티마이저)에 의해 결정된다.

 

 

2. EQUI JOIN

EQUI(등가) JOIN은 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용되는 방법으로, 대부분 PK ↔ FK의 관계를 기반으로 한다. 그러나 일반적으로 테이블 설계 시에 나타난 PK ↔ FK 관계를 이용하는 것이지, 반드시 PK ↔ FK 관계로만 EQUI JOIN이 성립하는 것은 아니다. 이 기능은 계층형(Hierarchical)이나 망형(Network) 데이터베이스와 비교해서 관계형 데이터베이스의 큰 장접이다.

JOIN의 조건은 WHERE절에 기술하게 되는데, "=" 연산자를 사용해서 표현한다.

 

대략적인 구조는 다음과 같다.

SELECT 테이블1.칼럼명, 테이블2.칼럼명 ...
FROM 테이블1, 테이블2
WHERE 테이블2.칼럼명 = 테이블1.칼럼명 -- WHERE절에 JOIN조건을 기술한다
;

 

같은 내용을 ANSI/ISO SQL 표준 방식으로 표현하면 다음과 같다.

SELECT 테이블1.칼럼명, 테이블2.칼럼명...
FROM 테이블1 INNER JOIN 테이블2
	ON 테이블2.칼럼명 = 테이블1.칼럼명 -- ON절에 JOIN 조건을 기술한다.
 ;

 

ANSI/ISO SQL 표준 방식으로 선수테이블과 팀 테이블에서 선수 이름과 소속된 이름을 출력하는 SQL문은 다음과 같다.

SELECT PLAYER.PLAYER_NAME AS 선수명, TEAM.TEAM_NAME AS 소속팀명
	FROM PLAYER INNER JOIN TEAM
		ON TEAM.TEAM_ID = PLAYER.TEAM_ID;

 

이렇게 꼭 테이블명.칼럼명 방식으로 칼럼명을 기술하는 이유는, 조인에 사용되는 여러개의 테이블에 같은 칼럼명이 존재할 수 있기 때문에 정확히 명시해주는 것이고, 개발자나 사용자가 조회할 데이터가 어느 테이블에 있는 칼럼인지 쉽게 알수있게 하므로 SQL에 대한 가독성이나 유지보수성을 높이는 효과가 있기 때문이다.

 

EQUI JOIN 사례

선수(PLAYER) 테이블과 팀(TEAM)테이블에서 케이리그 소속 선수들의 이름, 백넘버와 그 선수가 소속되어있는 팀명 및 연고지를 출력하려면 어떻게 해야 할까?

 

선수테이블과 팀테이블의 관계도

선수(PLAYER)테이블과 팀(TEAM)테이블의 관계가 위의 그림과 같다고 생각해보자.

보이는바와 같이 선수테이블에 있는 소속팀ID(TEAM_ID)칼럼이 팀테이블의 팀ID(TEAM_ID)칼럼과 FK관계에 있다.

TEAM_ID를 통해 그 선수의 팀명과 연고지를 알수있는 것이다.

예를들어 선수테이블의 박주호 선수를 보면, TEAM_ID가 K01이다. 팀 테이블에서 TEAM_IDX가 K01인 행을 보면 팀명은 수원FC, 연고지는 수원임을 알 수 있다.

 

SQL문으로 작성하면 다음과 같다.

SELECT A.PLAYER_NAME AS 선수명, A.BACK_NO AS 백넘버, A.TEAM_ID AS 팀코드
	, B.TEAM_NAME AS 팀명, B.REGION_NAME AS 연고지
FROM PLAYER A, TEAM B
WHERE B.TEAM_ID = A.TEAM_ID;

FROM절에서 보면 PLAYER에 A, TEAM에 B라는 ALIAS를 사용하였다. SELECT절에서 칼럼에 대한 ALIAS를 사용하는 것 처럼, FROM절의 테이블에 대해서도 ALIAS를 사용할 수 있다.

 

조인 조건을 기술할 때 주의해야 할 사항이 한 가지 있다.

만약 테이블에 대한 ALIAS를 적용해서 SQL문장을 작성했다면, WHERE절과 SELECT 절에는 테이블명이 아닌 테이블에 대한 ALIAS를 사용해야 한다.

즉 , 위의 SQL문 예제에서 FROM절에서 PLAYER를 A라고 ALIAS를 지정했는데, SELECT에서 선수명을 출력하고자 할때 PLAYER.PLAYER_NAME 이라고 해서는 안된다는것이다. A.PLAYER_NAME 이라고 해야 한다.

 

3. Non EQUI JOIN

Non EQUI(비등가) JOIN은 두 개의 테이블 간에 논리적인 연관 관계를 갖고 있으나, 칼럼 값드리 서로 일치하지 않는 경우에 사용한다. Non EQUI JOIN의 경우 "="연산자가 아닌 다른(Between, >, >=, <, <= 등) 연산자들을 사용해 JOIN을 수행한다.

다음은 Non EQUI JOIN의 대략적인 형태이다.

SELECT 테이블1.칼럼명, 테이블2.칼럼명 ...
FROM 테이블1, 테이블2
WHERE 테이블1.칼럼명 BETWEEN 테이블2.칼럼명1 AND 테이블2.칼럼명2;

 

Non EQUI JOIN은 케이리그 관련 테이블로 구현되기 힘드므로 사원(EMP)테이블과 가상의 급여등급(SAL_GRADE)테이블로 설명을 한다.

사원테이블과 급여등급 테이블의 관계도

사원테이블에 나타나는 급여(SAL)를 급여등급테이블의 5개의 급여등급으로 분류할수 있는것을 알수 있다.

예를들어 사원테이블의 SMITH같은 경우 급여가 800이므로, 급여등급 테이블에서 700~1200에인 1등급임을 알 수 있다.

 

이를 SQL로 나타내면 다음과 같다.

SELECT A.ENME AS 사원명, A.SAL AS 급여, B.GRADE AS 급여등급
FROM EMP A, SALGRADE B
WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL;

 

결과는 다음과 같을 것이다.

사원명	급여	급여등급
SMITH	800	1
ALLEN	1600	3
WARD	1250	2
...	...	...

 

 

4. 3개 이상 TABLE JOIN

이번에는 3개 이상의 테이블을 조인하는 예제를 알아본다.

선수별로 홈그라운드 경기장이 어디인지 출력하고자 할때, 선수 테이블과 운동장 테이블이 서로 관계가 없으므로 중간에 팀 테이블이라느 연관관계가 있는 테이블을 추가해서 세개의 테이블을 조인해서 원하는 데이터를 출력한다.

 

SELECT A.PLAYER_NAME AS 선수명, A.POSITION AS 포지셔
	, B.REGION_NAME AS 연고지, B.TEAM_NAME AS 팀명
    , C.STADIUM_NAME AS 구장명
FROM PLAYER A, TEAM B, STADIUM C
WHERE B.TEAM_ID = A.TEAM_ID
	AND C.STADIUM_ID = B.STADIUM_ID
ORDER BY 선수명;

선수 테이블의 소속팀ID(TEAM_ID)가 팀테이블의 팀ID(TEAM_ID)와 PK-FK관계가 있었고,

경기장 테이블의 경기장ID(STADIUM_ID)와 팀테이블의 전용구장ID(STADIUM_ID)가 PK=FK 관계인 것을 알 수 있다.

세개의 테이블에 대한 조인이므로  WHERE절에 2개 이상의 조인 조건이 필요하다.

 

5. OUTER JOIN

앞서 다룬 EQUI JOIN, Non EQUI JOIN은 모두 조인조건의 결과가 참(TRUE)인 행들만 반환하는 INNER(내부)조인이다.

OUTER(외부) JOIN은 조인 조건을 만족하지 않는 행들도 함께 반환할때 사용된다.

Oracle은 다음과 같이 조인 칼럼 뒤에 '(+)' 기호를 표시해 OUTER JOIN을 나타낸다.

SELECT 테이블1.칼럼명, 테이블2.칼럼명 ...
FROM 테이블1, 테이블2
WHERE 테이블2.칼럼명(+) = 테이블1.칼럼명;

OUTER JOIN을 사용하기 위해 '(+)'를 표시할 때 주의할점은  '(+)' 기호의 위치이다.

OUTER JOIN의 기준이 되는 테이블 (조인할 데이터가 없는 경우에도 모든 데이터를 표기하는 테이블)은 위 쿼리문에서 테이블1이다. 즉 '(+)'표시 반대편에 있는 테이블이 OUTER JOIN의 기준 테이블이 된다.

OUTER JOIN의 결과에서 조인에 성공한 행들은 INNER JOIN과 같이 조인에 참여한 각 테이블의 칼럼 값들이 표시된다. 반면 조인에 실패한 행들의 경우, 기준테이블은 칼럼들의 값이 표시되고, 그 외 테이블에서 가져오는 칼럼들은 NULL로 표시된다.

 

STADIUM에 등록된 경기장 중에 홈팀이 없는 경기장도 있다. STADIUM과 TEAM을 조인하되, 홈팀이 없는 경기장의 정보도 같이 출력하도록 하는 SQL문을 작성하면 다음과 같다.

SELECT A.STADIUM_NAME, A.STADIUM_ID, A.HOMETEAM_ID, B.TEAM_NAME
FROM STADIUMA, TEAM B
WHERE B.TEAM_ID(+) = A.HOMETEAM_ID
ORDER BY A.HOMETEAM_ID;

 

그리고 결과는 다음과 같다.

STADIUM_NAME	STADIUM_ID	HOMETEAM_ID	TEAM_NAME
수원 종합운동장	A01	K01	수원FC
수원월드컵경기장	B04	K02	수원삼성
서울월드컵경기장	C03	K03	FC서울
부산시민경기장	D02
대구시민경기장	D03
...	...	...	...

등록된 경기장중 홈팀이 없는 경기장은 NULL 값으로 표시된걸 볼수있다.

INNER JOIN이었다면 홈팀이 있는 경기장만 출력되었겠지만, OUTER JOIN이었기 때문에 홈팀이 없는 경기장도 출력되는 것이다.

 

---

요즘 날씨가 너무 덥습니다 ㅠㅠ 가만히 있어도 땀이 줄줄 나는 날씨네요!

무더운 날씨 건강 잘 챙기시기 바랍니다!

다음절은 2과목 1장의 마지막, 표준조인입니다 ㅎㅎ

구독과 하트, 댓글은 큰 힘이 됩니다 :)

---

본 포스팅은 '한국데이터산업진흥원' 에서 발행한 'SQL 전문가 가이드' 를 참고/인용 하였음을 밝힙니다.