여행을 개발하다
[Join] ANSI Join & Oracle Join, Join의 종류 본문
지난 Join의 정의 및 문법에 관한 포스팅에 이어 ANSI Join & Oracle Join, Join의 종류에 대해 이야기하고자 한다.
ANSI Join vs ORACLE Join
SQL은 데이터베이스를 관리하기 위해 만들어진 프로그래밍 언어이며, 데이터베이스를 관리해주는 대부분의 Management System(DBMS; 오라클, MS-SQL, MYSQL 등)들은 SQL을 사용한다. 물론, DBMS 자체의 특수성 때문에 SQL의 사용법이 조금씩 다르기도 하지만, 큰 틀에서 보면 나름대로의 보편성을 가지고 있다. 왜냐하면 데이터베이스를 관리한다는 본질은 바뀌지 않기 때문이다.
이를 위해미국 국립 표준 협회(American National Standards Institute,ANSI)에서도 SQL에 대한 보편적인 문법을 제시하고 있는데, 그것이 바로 ANSI Query(SQL)이다. Join도 마찬가지로 ANSI 문법을 사용하는 경우가 대부분이긴 하지만, 편의에 따라 DBMS만의 자체 문법을 사용하기도 한다. 대표적으로, Oracle에서 사용하는 Oralce Join이 있다.
이번 글에서는, ANSI Join과 Oracle Join 모두 다룰 것이다. 하지만 Join 하고자하는 테이블이 많아지면 많아질수록, 쿼리의 가독성과 확장성을 고려할 수밖에 없다는 점에서 ANSI → Oracle 순으로 다룰 것이다. ANSI Join을 좀 더 편리하게 쓰려고 개발된 것이 Oracle Join인 것은 명백한 사실이므로.
Join의 종류 - ANSI 문법 기준
Join의 종류에는 대표적으로 Inner Join, Outer Join, Cross Join, Self Join이 있다.
그럼 Inner Join, Outer Join, Cross Join, Self Join은 언제 어떻게 사용해야 할까?
그것은'내가 원하는 데이터를 어떻게 select 해올까?'라는 물음에서부터 시작한다.
본격적으로 시작하기 전에, Join의 종류를 보여주는 다이어그램을 간단하게 살펴본다.
Join의 종류를 설명하기 위해, 다음과 같은 2개의 테이블이 있다고 가정해보자.
1. 테이블 SAWON은 기업 Z의 모든 직원의 사번, 이름, 직급 정보가 담겨있다.
2. 테이블 LICENSE는 직원들이 소유하고 있는 자격증 정보가 담겨있다.
단, 설명의 편의를 위해 LICENSE 테이블에 담길 수 있는 자격증 정보는 사원 1명당 1개로 한정한다.
그런데 여기서 가정해볼 수 있는 예외사항을 대략 2가지로 추려봤다.
예외사항1.자격증이 없는 사원이 있을 수도 있다. (사번 2, 4, 5)
예외사항2.자격증 기록은 있는데, 해당 자격증을 소유한 사원이 퇴사하여 SAWON 테이블에는 해당 사원의 정보(SABUN)가 부재한다. (사번 6)
아래는 위의 예외사항을 어느 정도 반영한 SAWON과 LICENSE 테이블의 레코드들이다. 가독성을 높이기 위해 SABUN이 같은 레코들은 동일 선상에 표시했다.
1. Inner Join
- 각 테이블에서 조인 조건에 일치되는 데이터만 가져온다.
대부분의 서적과 인터넷 글에서는 Inner Join은 '교집합'이라고 말한다. 중학교 때 배웠던 기억이 어렴풋이 나는데, 교집합은 집합 A와 B의 공통된 부분을 의미한다. 벤다이어그램으로 표시하면 두 개의 원이 겹치는 부분이다.
두 테이블을 Inner Join 해본다. 조인 조건은 SAWON 테이블, LICENSE 테이블에 공통적으로 있는 SABUN 컬럼이 된다.
SELECT *
FROM SAWON A
INNER JOIN
LICENCE B
ON A.SABUN = B.SABUN;
Inner Join은 위와 같은 예외적인 데이터(예외사항1, 예외사항2)들을 아예 무시하고, 조건에 부합하는 것만 SELECT 한다. 즉, SAWON 테이블에 조인 조건에 부합하는 SABUN이 없어도 무시되고, LICENSE 테이블에 조인 조건에 부합하는 SABUN이 없어도 무시된다.
SABUN NAME RANK SABUN LICENSE_NM
----------- ------- ------- ----------- ---------------
2020012901 정영훈 사원 2020012901 정보처리기사
2020020201 정영훈3 사원 2020020201 정보관리기술사
2. Outer Join
- 조인 조건에 일치하는 데이터 및 일치하지 않은 데이터를 모두 SELECT 한다.
- 조인 조건에 일치하는 데이터가 없다면 NULL로 가져온다.
Outer Join은 Inner Join 과는 다르게 주(主, main) 테이블이 어떤 테이블인지가 중요하다. 그래서 어떤 테이블이 중심이 되느냐에 따라 다시Left Outer Join, Right Outer Join, Full Outer Join으로 세분할 수 있다. Left Outer Join은 왼쪽에 있는 테이블이, Right Outer Join은 오른쪽에 있는 테이블이, Full Outer Join은 양쪽 테이블 모두가 중심(주)이라는 뜻이다.
그렇다면 왼쪽과 오른쪽에 있다는 말은 무슨 의미일까?
다음 SQL을 살펴본다.
SELECT *
FROM TABLE_A T1
LEFT OUTER JOIN
TABLE_B T2
ON T1.COULMN = T2.COLUMN;
TABLE_A에다가 TABLE_B를 조인한다. 그림으로 표현하면 TABLE_A에다가 TABLE_B를 갖다 붙이는 모양새이다.
이해도를 높이기 위해 줄 바꿈을 했던 위 SQL을 1열로 정렬해본다.
조인의 종류를 명시한 Left Outer Join을 기준으로TABLE_A은 왼쪽에, TABLE_B는 오른쪽에 언급된다. Left Outer Join이므로 왼쪽에 있는TABLE_A가 주(主) 테이블, TABLE_B가 종(從)테이블이 된다.
이번에는 한 개의 테이블을 더 조인해본다.
SELECT *
FROM TABLE_A T1
LEFT OUTER JOIN
TABLE_B T2
ON T1.COULMN = T2.COLUMN
LEFT OUTER JOIN
TABLE_C T3
ON T2.COLUMN = T3.COLUMN;
TABLE_B에게 TABLE_A는 왼쪽, TABLE_C는 오른쪽에 있는 테이블로 표현될 수 있다.
SELECT *
FROM TABLE_A T1
LEFT OUTER JOIN
TABLE_B T2
ON T1.COULMN = T2.COLUMN;
위 쿼리는 Left Outer Join이기 때문에 TABLE_A(T1)가 중심 테이블이 되어, TABLE_B(T2)를 JOIN 시킨다. 결괏값은 TABLE_A의 데이터는 모두 나오지만, TABLE_B의 조건에 만족하는 데이터가 있으면 해당 데이터를, 없으면 NULL로 SELECT된다.
2-1. LEFT OUTER JOIN
- 왼쪽 테이블이 기준이 된다.
- 조인 조건에 부합하는 데이터가 조인 당하는 테이블(오른쪽)에 있으면 해당 데이터를, 부재하면 NULL로 SELECT 된다.
-- Left Outer Join 문법
SELECT *
FROM TABLE_A T1
LEFT OUTER JOIN
TABLE_B T2
ON T1.COULMN = T2.COLUMN;SELECT *
FROM SAWON A
LEFT OUTER JOIN
LICENSE B
ON A.SABUN = B.SABUN;
SABUN NAME RANK SABUN LICENSE_NM
-------- ------ ------ ---------- ----------------
2020012901 정영훈 사원 2020012901 정보처리기사
2020020201 정영훈3 사원 2020020201 정보관리기술사
2020012902 정영훈2 사원 NULL NULL
2020020202 정영훈4 사원 NULL NULL
2020020203 정영훈5 사원 NULL NULL
2020020204 정영훈6 사원 NULL NULL
2-2. RIGHT OUTER JOIN
- 오른쪽 테이블이 기준이 된다.
- 조인 조건에 부합하는 데이터가 조인 당하는 테이블(왼쪽)에 있으면 해당 데이터를, 부재하면 NULL로 SELECT 된다.
-- Right Outer Join 문법
SELECT *
FROM TABLE_A T1
RIGHT OUTER JOIN
TABLE_B T2
ON T1.COULMN = T2.COLUMN;SELECT *
FROM SAWON A
RIGHT OUTER JOIN
LICENSE B
ON A.SABUN = B.SABUN;
SABUN NAME RANK SABUN LICENSE_NM
-------- ------ ------ ---------- ----------------
2020012901 정영훈 사원 2020012901 정보처리기사
2020020201 정영훈3 사원 2020020201 정보관리기술사
NULL NULL NULL 2012020204 정보처리산업기사
2-3. FULL OUTER JOIN
- 양쪽 테이블 모두가 기준이 된다.
- 조인 조건에 부합하는 데이터가 조인 당하는 테이블(왼쪽 OR 오른쪽)에 있으면 해당 데이터를, 부재하면 NULL로 SELECT 된다.
-- Full Outer Join 문법
SELECT *
FROM TABLE_A T1
FULL OUTER JOIN
TABLE_B T2
ON T1.COULMN = T2.COLUMN;SELECT *
FROM SAWON A
FULL OUTER JOIN
LICENSE B
ON A.SABUN = B.SABUN;
SABUN NAME RANK SABUN LICENSE_NM
-------- ------ ------ ---------- ----------------
2020012901 정영훈 사원 2020012901 정보처리기사
2020012902 정영훈2 사원 NULL NULL
2020020201 정영훈3 사원 2020020201 정보관리기술사
2020020202 정영훈4 사원 NULL NULL
2020020203 정영훈5 사원 NULL NULL
2020020204 정영훈6 사원 NULL NULL
NULL NULL NULL 2012020204 정보관리산업기사
3. Cross Join
-두 테이블 조인 시 가능한 모든 경우의 레코드를 SELECT 한다.
- INNER, OUTER JOIN이 특정 조건에 따라 조인된다면,CROSS JOIN은 별도의 조건을 기술하지 않는다.
예를 들어, 테이블 A에는 25개의 레코드가 있고, 테이블 B에 20개의 레코드가 있다고 가정한다. 이 두 테이블을 CROSS JOIN 하게 되면 총 25*20=500개의 레코드가 SELECT 된다.
-- Cross Join 문법
SELECT *
FROM TABLE_A T1
CROSS JOIN
TABLE_B T2;SELECT *
FROM SAWON A
CROSS JOIN
LICENSE B;
SABUN NAME RANK SABUN LICENSE_NM
-------- ------ ------ ---------- ----------------
2020012901 정영훈 사원 2020012901 정보처리기사
2020012901 정영훈 사원 2020020201 정보관리기술사
2020012901 정영훈 사원 2012020204 정보관리산업기사
2020012902 정영훈2 사원 2020012901 정보처리기사
2020012902 정영훈2 사원 2020020201 정보관리기술사
2020012902 정영훈2 사원 2012020204 정보관리산업기사
2020020201 정영훈3 사원 2020012901 정보처리기사
2020020201 정영훈3 사원 2020020201 정보관리기술사
2020020201 정영훈3 사원 2012020204 정보관리산업기사
2020020202 정영훈4 사원 2020012901 정보처리기사
2020020202 정영훈4 사원 2020020201 정보관리기술사
2020020202 정영훈4 사원 2012020204 정보관리산업기사
2020020203 정영훈5 사원 2020012901 정보처리기사
2020020203 정영훈5 사원 2020020201 정보관리기술사
2020020203 정영훈5 사원 2012020204 정보관리산업기사
2020020204 정영훈6 사원 2020012901 정보처리기사
2020020204 정영훈6 사원 2020020201 정보관리기술사
2020020204 정영훈6 사원 2012020204 정보관리산업기사
4. Self Join
- 테이블이 자기 자신을 마치 다른 테이블처럼 취급하여 조인한다.
- Main 및 Sub 테이블과 컬럼이 모두 같기 대문에, 특정 열을 명시할 때 열이 모호하다는 오류가 발생할 수 있다. 이런 점에서 반드시 알리아스(ALIAS)를 사용하여 조인하는 것을 권고한다.
SELECT *
FROM TABLE_A T1
JOIN
TABLE_A T2
ON T1.COLUMN = T2.COLUMN;SELECT *
FROM SAWON A
JOIN
SAWON B
ON A.SABUN = B.SABUN;
SABUN NAME RANK SABUN NAME RANK
---------- ------- ------ ----------- ------ ------
2020012901 정영훈 사원 2020012901 정영훈 사원
2020012902 정영훈2 사원 2020012902 정영훈2 사원
2020020201 정영훈3 사원 2020020201 정영훈3 사원
2020020202 정영훈4 사원 2020020202 정영훈4 사원
2020020203 정영훈5 사원 2020020203 정영훈5 사원
2020020204 정영훈6 사원 2020020204 정영훈6 사원
Oracle Join
ANSI Join의 개념을 명확하게 알고 있다면, Oracle Join도 이해하기 쉬울 것이라고 생각한다(물론, Oracle에서도 당연히 ANSI Join 문법을 문제없이 사용할 수 있다).
ANSI Join은 Join의 종류를 명시하며 테이블을 하나하나씩 붙여 나가는 구조였다. 하지만 Oracle Join은 가져오고자 하는 컬럼이 포함된 모든 테이블들을 일단 FROM 절에 기술한다. 그리고 WHERE 절에 Join조건을 명시한다.
예를 들어,
SELECT *
FROM TABLE_A T1
LEFT OUTER JOIN
TABLE_B T2
ON T1.COULMN = T2.COLUMN;
SQL은
SELECT *
FROM TABLE_1 T1, TABLE_2 T2
WHERE T1.COLUMN = T2.COLUMN;
으로 표현한다.
ANSI Join에 비교하면, Inner Join은 어차피 공통적인 레코드만 SELECT 하니 문제가 없어 보인다. 하지만 Outer Join은 어떤 테이블이 중심이 되는지 명확히 알 수 없다. 따라서, Outer Join 시에는 조인 당하는 테이블(從 테이블)에 반드시 '(+)' 표기를 해준다. 아쉬운 점은 Oralce Join의 문법으로는 Full Outer Join이 불가하다는 것이다.
-- TABLE_1이 메인 테이블일 경우
SELECT *
FROM TABLE_1 T1, TABLE_2 T2
WHERE T1.COLUMN = T2.COLUMN(+);-- TABLE_2가 메인 테이블일 경우
SELECT *
FROM TABLE_1 T1, TABLE_2 T2
WHERE T1.COLUMN(+) = T2.COLUMN;
본 포스팅 시작 부분의 예제인, SAWON과 LICENSE 테이블의 예제를 그대로 사용해본다.
* Inner Join
SELECT *
FROM SAWON A, LICENSE B
WHERE A.SABUN = B.SABUN;SABUN NAME RANK SABUN LICENSE_NM
------- ------- ----- --------- ------------
2020012901 정영훈 사원 2020012901 정보처리기사
2020020201 정영훈3 사원 2020020201 정보관리기술사
* Left Outer Join
SELECT *
FROM SAWON A, LICENSE B
WHERE A.SABUN = B.SABUN(+); SABUN NAME RANK SABUN LICENSE_NM
-------- ------ ------ ---------- ----------------
2020012901 정영훈 사원 2020012901 정보처리기사
2020020201 정영훈3 사원 2020020201 정보관리기술사
2020012902 정영훈2 사원 NULL NULL
2020020202 정영훈4 사원 NULL NULL
2020020203 정영훈5 사원 NULL NULL
2020020204 정영훈6 사원 NULL NULL
* Right Outer Join
SELECT *
FROM SAWON A, LICENSE B
WHERE A.SABUN(+) = B.SABUN; SABUN NAME RANK SABUN LICENSE_NM
-------- ------ ------ ---------- ----------------
2020012901 정영훈 사원 2020012901 정보처리기사
2020020201 정영훈3 사원 2020020201 정보관리기술사
NULL NULL NULL 2012020204 정보처리산업기사
* Cross Join
SELECT *
FROM SAWON A, SAWON B; SABUN NAME RANK SABUN NAME RANK
---------- ------- ------ ----------- ------ ------
2020012901 정영훈 사원 2020012901 정영훈 사원
2020012902 정영훈2 사원 2020012902 정영훈2 사원
2020020201 정영훈3 사원 2020020201 정영훈3 사원
2020020202 정영훈4 사원 2020020202 정영훈4 사원
2020020203 정영훈5 사원 2020020203 정영훈5 사원
2020020204 정영훈6 사원 2020020204 정영훈6 사원
* Self Join
SELECT *
FROM SAWON A, SAWON B
WHERE A.SABUN = B.SABUN; SABUN NAME RANK SABUN NAME RANK
---------- ------- ------ ----------- ------ ------
2020012901 정영훈 사원 2020012901 정영훈 사원
2020012902 정영훈2 사원 2020012902 정영훈2 사원
2020020201 정영훈3 사원 2020020201 정영훈3 사원
2020020202 정영훈4 사원 2020020202 정영훈4 사원
2020020203 정영훈5 사원 2020020203 정영훈5 사원
2020020204 정영훈6 사원 2020020204 정영훈6 사원
지금까지 ANSI Join & Oracle Join, Join의 종류에 대해 포스팅하였다.
긴 글 읽어주셔서 감사합니다! : )
'데이터베이스 > DB 일반' 카테고리의 다른 글
| [Join] Join의 정의 및 문법 (0) | 2020.01.30 |
|---|
