[DB] Oracle - Join의 이해

 

안녕하세요 취업블로거 우기입니다 오늘은 살짝 어렵다면 어려운 주제로 포스팅을 해볼까합니다. 오늘은...! Join에 대해서 좀 알아보도록 하겠습니다. 내용흐름이 중구난방해도 핵심들은 잘 이야기해드리겠습니다.

 

Preview.

Join을 기본적으로 파악하기전에 논리모델, 물리모델과 SQL의 연관성에대해 정리하는 시간을 미리 가져보겠습니다.

 

의미	논리모델	물리모델	SQL
집합의 단위	엔티티(Entity-Type)	테이블(Table)	FROM절에 기술
집합의 내용	속성(Attribute)	컬럼(Column)	SELECT절에 기술
집합의 관계	릴레이션(Relationship)	외부키(Foriegn Key)	WHERE절에 기술(Join Column)
집합의 구분	식별자(Identifier)	기본키(Primary Key)

 

아마 논리모델은 Spring-boot 많이 해보신 분들은 익숙하실거라 생각합니다. 상응하는 물리모델들은 DB용어로 많이쓰이는거 같구요 저희가 주목해야될점은 집합의 관계에서 릴레이션 혹은 외부키입니다. Join Column에 많이 사용하기도 하죠 

의미	논리모델	물리모델
집합의 제약	무결성	제약 (Constraint)
	실체무결성	Primary Key Constraint
	도메인무결성	Data Type Constraint, Not Null Constraint, Default Column Value
	참조무결성	Foreign Key Constraint
	기타무결성	Unique Constraint, Check Constraint

 

먼저 조인을 하기위해서는 조인 대상 테이블의 기본키(당연히 조인하는 테이블에겐 외부키겠죠?)와 테이블간의 관계에 대한 파악이 먼저입니다!  이때 가장 중요한 것은 업무 요건과 데이터모델에 따른 조인조건의 결정과 추출되어야하는 결과집합의 수준(Level) 이라고 말씀드릴수 있습니다.

 

❗️결과집합의 수준이란? 어떤 테이블을 기준으로 추출할 것인가를 말합니다.

예시를 들어보면 사원 Table, 부서 Table을 서로 조인하여 결과를 추출할때 무엇을 기준으로 두고 결과집합을 추출할것인지 생각하는겁니다. 사원 Table이 기준이라면 최종결과는 사원의 Row 수에 맞춰서 !, 반대로 부서 Table이 기준이라면 최종결과는 부서의 Row로 결정하는겁니다.

이 때 예시로 1:1 관계라면, 부서 Table이 기준이라면 사원이 부서 Row수에 맞추어 GROUP BY, DISTINCT(비선호) 를 통해 부서 Table의 결과집합을 만들수 있게끔 도와줘야합니다. 

 

❗️RelationShip 을 더 알고싶어요 !

관계형 데이터베이스에서 RelationShip이란 테이블 간의 논리적인 연결 구조를 의미하곤 합니다. 기본키의 상속에따라 부모Table, 자식Table로 구분할 수 있습니다. 이는 테이블간의 매칭정도(기수성: Cardinality)에 따라서, 1:1 , 1:M, M:M 관계로 구분할 수 있다고 생각합니다.  (🌈기수성이란? 엔티티 간 관계에서 한 엔티티의 인스턴스가 다른 엔티티의 인스턴스와 갖는 관계의 개수)

 

<사원테이블>      <부서테이블>  이 있다는 가정하에 부서테이블의 기본키를 사원테이블이 상속받았다면 사원테이블은 "자식", 부서테이블은 "부모" 테이블인것이라고 생각하면됩니다. 즉 기본키를 누가 누구에게서 상속받았는지 따져 자식,부모 테이블로 이야기할수있습니다.

 

Join이란?

Join의 종류

조인은 결과집합의 구성에 필수적인 테이블과 조인인지 선택적인 테이블과 조인인지에 따라 Inner Join과 Outer Join으로 나뉩니다.

또는 조인에 사용하는 연산자의 종류에 따라 Equijoin과 Non-Equijoin으로 구분할 수 있습니다. 

구분	조인의 종류	내용
결과집합의 구성 유형	Inner Join	결과집합 구성에 필수적인 테이블과 조인
	Outer Join	결과집합 구성에 선택적인 테이블과 조인
조인 연산자의 유형	Equijoin	=(Equal) 연산자를 이용한 조인
	Non-Equijoin	=(Equal) 이외의 연산자를 이용한조인 ❗️ 카테시안 조인 : 조인조건이 없거나 일부 누락된 조인. Non-Equijoin의 한 유형으로 볼 수 있다.

 

기준집합과 참조집합

🌈 기준집합 : 결과집합의 구성에 결정적인 영향을 주는 집합(결과집합 로우 수의 기준을 결정)

🌈 참조집합 : 결과집합의 구성에 컬럼 단위로 참조되는 집합 (결과집합 로우 수의 기준이 아님)

 

이는 도대체 정확하게 무엇인지 모르겠으니 하나의 예시를 들며 이야기 해보겠습니다.

[전제 조건]

<사원> 테이블과 <부서> 테이블이 있다는 가정하.

 

[가정 1.]

<사원>의 로우 수를 기준으로 결좌집합이 추출되었다면,

[결론 1-1]

<사원>이 기준집합이고 <부서>는 참조집합이다.

 

[가정 2]

<부서>의 로우 수를 기준으로 결과집합이 추출되었다면,

[결론 2-1]

<부서>가 기준집합이고 <사원>이 참조집합입니다.

[부가설명]

물론 이경우에, <사원>은 [부서코드]로 GROUP BY 한 후에 <부서>와 조인합니다.

 

이러한 결정에서 조인조건 이외의 조건은 생략합니다. 어느 집합의 형태를 기준으로 결과집합의 로우 수를 반영하는 지가 중요한 것 같습니다.

 

SQL에서 기준집합과 참조집합을 결정하는 요소는 '관계'와 '조인의 종류'입니다. 조인의 종류에 의해서 기준집합이 결정되는것은 대부분 Outer Join이나 Non-Equijoin인 경우입니다. (일단 Outer Join으로 다뤄볼 예정)

요인	테이블의 유형	기준/참조	비고
관계	부모테이블	참조집합	특별한 업무 요건이 없다면, 일반적인 조인에서 자식테이블이 로우 수를 결정하는 기준
	자식테이블	기준집합
조인의 종류 (아우터조인)	아우터조인하는 테이블	기준집합	특별한 업무 요건이 없다면, 아우터조인에서 아우터조인하는 테이블이 로우 수를 결정하는 기준
	아우터조인되는 테이블	참조집합
업무 요건	부모테이블	기준집합	특별히 부모테이블의 수준으로 데이터를 추출하고자 한다면, 부모테이블이 로우 수를 결정하는 기준
	자식테이블	참조집합

 

좀만 구체적으로 예시를 들어서 해보겠습니다.

 

관계에 의한 조인조건 예시

SELECT
A.사원번호
A.사원명
B.부서명
B.지역
FROM 사원 A,						--> 자식테이블 : 기준집합
부서 B							--> 부모테이블 : 참조집합
WHERE A.부서코드 = B.부서코드		--> 조인조건
AND A.급여구분 = '연봉'			--> 기준집합의 필터조건
AND B.지역    = '수원'			--> 참조집합의 필터조건

 

조인조건 자체만으로는 테이블 간에 조인하기 위한 조건 , 필터조건은 데이터를 걸러내기 위한 조건

결과집합의 로우 수는 <사원> 테이블의 필터조건에 의해서 영향을 받고, <부서>테이블의 필터조건에서도 영향을 받는다.

하지만 결과집합의 로우수는 <사원>의 로우 수를 "기준"으로 만들어진것이라고 생각해야합니다. 기준집합과 참조집합의 결정에 제일 중요한것은 관계에 의한 조인조건이지, 데이터를 걸러내는 필터조건이 아닙니다 

 

 

업무 요건에의해 모델의 기본적인 '관계'가 변경된 경우

SELECT A.부서코드
      ,A.부서명
      ,B.사원수
FROM 부서 A          --> 부모테이블 : 기준집합
    ,(
     SELECT 부서코드   부서코드
           ,COUNT(*)   사원수
     FROM 사원
     GROUP BY 부서코드
     ) B             --> 자식테이블 : 참조집합
WHERE A.부서코드 = B.부서코드
;

 

여기서 의문점을 가질 수 있는 부분은 그냥 서로 바꾼것 뿐인데 업무 요건이 도대체 무엇이길래 라고 생각 하실수도있습니다. 자식테이블을 보면 부서코드에 의한 사원수를 COUNT 한것밖에없다는 가정하라면 or 자식테이블에 사원수가 너무나도 많다면 업무 요건에의해 바뀔수도 있는 것 같습니다. 

 

조인에 종류(아우터조인)에 의해 기준집합과 참조집합이 결정

SELECT A.사원번호
      ,A.사원명
      ,A.부서코드
      ,B.기본수당액
      ,B.특별수당액
FROM 사원     A          --> 아우터조인하는테이블 : 기준집합
    ,기타급여 B          --> 아우터조인되는테이블 : 참조집합
WHERE A.부서코드 = B.부서코드(+)

 

기본적으로 LEFT OUTER JOIN을 많이 하시는데, 잠깐 Oracle Join으로 눈으로 보고 가셔도 될 것 같습니다~

 

'관계'와 '조인의 종류'의 조합에 의해 기준집합과 참조집합 결정이 어려운 쿼리

SELECT A.부서코드
      ,A.부서명
      ,B.사원번호
      ,B.사원명
FROM 부서 A      --> 부모테이블,아우터조인하는테이블 : 참조집합,기준집합
    ,사원 B      --> 자식테이블,아우터조인되는테이블 : 기준집합,참조집합
WHERE A.부서코드 = B.부서코드(+)

 

이렇게 되면 많이 혼란스러우실 수 있습니다. 하지만 기준집합, 참조집합은 저희가 임의로 이야기 하는것이지 내부 코드적으로는 문제가없습니다 (옵티마이저가 알아서 해줄테니깐요...) 

 

이렇게까지 해서 기준집합과 참조집합의 구분은 어느 집합(또는 테이블)이 결과집합의 로우 수를 결정하는 기준이 되느냐 입니다. '업무 요건'이라는 근본적 요인에 의해, 주로 '관계'와 '조인의 종류' 기준집합과 참조집합을 결정합니다.

 

 

조인의 핵심원리 

 

SQL 에서 조인하려면 몇가지 생각을 해보는것이 좋습니다

1. 어떤 테이블 형태의 결과집합의 필요한가?

2. 조인조건을 어떻게 구성해야 하는가?

이 두가지 조건이 중요하다고 생각합니다. 이 기준집합이 결정되면 조인조건은 관계에 의해 자동으로 결정됩니다.또한 관계에 따라 참조집합에 대한 추가 작업이 필요할 수 있습니다. 추가작업이란, 관계에 의해 조인조건이 서로 맞지 않을 때, 조인조건을 맞추어주는 작업입니다.

 

자세하게 말씀 드리자면  1:1 이나 1:M 관계에서 자식테이블이 기준집합이면 참조집합인 부모테이블에 대한 추가 작업이 필요 없습니다. 그러나 1:M관계나 M:M 관계에서 부모테이블이 기준집합이면, 참조집합인 자식테이블에 대한 추가 작업이 필요합니다. 조인조건을 맞추기위해서 자식테이블을 집합변환하여 부모테이블과 1:1 관계로 만들어주는 일을 말씀드립니다.

 

표로 정리해서 보여드리겠습니다.

 

관계	기준집합	참조집합	참조집합에 대한 추가 작업
1:1	자식테이블	부모테이블	필요없음
1:M	자식테이블	부모테이블	필요없음
M:M	부모테이블	자식테이블	1) 부모테이블의 기본키 컬럼으로GROUP BY(또는 DISTINCT) 한다. 2) 자식테이블이 기본키 일부를 부모테이블에서 상속받은 경우라면 상속받은 컬럼이외의 나머지 기본키 컬럼에 = 조건을 추가한다.
	부모테이블	자식테이블	1) 부모테이블의 기본키 컬럼으로 GROUP BY(또는 DISTINCT) 한다. 2) 자식테이블이 기본키 일부를 부모테이블에서 상속받은 경우라면 상속받은 경우라면 상속받은 컬럼 이외의 나머지 기본키 컬럼에 = 조건을 추가한다. 3) 이후에 두개의 자식테이블을 조인한다. ❗️여기에서 부모테이블이란, M:M 관계에서 두 테이블의 조인조건을 기본키로 가지는 테이블을 말한다. 실제로 존재할 수도 있고, 존재하지 않을 수도 있다.조인조건으로 GROUP BY된 형태를 부모테이블이라고 보면 된다

 

설명을 다드렸으니 이제 해당 관계에 대한 예시를 함께 보시죠

 

참조집합인 자식테이블을 GROUP BY하여, 기준집합 형태로 변환하는 방법

(사원테이블의 기본키는 사원번호이며, 급여지급의 기본키는 급여월과 사원번호이다)

 

-- 참조집합인 자식테이블을 GROUP BY하여, 기준집합 형태로 변환
SELECT A.사원번호
      ,A.사원명
      ,B.최근급여월
      ,B.당해급여합
FROM 사원 A             --> 부모테이블 : 기준집합
    ,(
     SELECT B.사원번호     사원번호
           ,MAX(B.급여월)  최근급여월   --> 그룹핑에 따른 집계함수 처리
           ,SUM(B.월급여)  당해급여합   --> 그룹핑에 따른 집계함수 처리
     FROM 급여지급 B   --> 자식테이블 : 참조집합
     WHERE B.급여월 BETWEEN '201101' AND '201112'
     GROUP BY B.사원번호  --> 1) 부모테이블의 PK 컬럼로 그룹핑하여 부모테이블과 1:1로 맞춤
     ) B
WHERE B.사원번호 = A.사원번호 --> 2) 부모테이블의 PK컬럼으로 조인
;

 

사원에 대한 당 해의 급여관련 항목을 추출하는 쿼리입니다. 기준집합은 사원, 참조집합은 급여지급입니다. 사원과 급여지급은 1:M 관계이므로 참조집합인 급여지급을 사원과 1:1 관계로 맞추어주기 위해서 사원의 기본키인 사원번호로 GROUP BY 하였습니다.

 

 

참조집합이 기준집합으로부터 기본키 일부를 상속받았을 때의 상황, 참조집합의 조건에 기준집합에서 상속받은 기본키 이외의 나머지 기본키 컬럼에 = 조건을 추가하여 관계를 1:1로 만들어주는 방법입니다. 

SELECT A.사원번호
      ,A.사원명
      ,A.부서코드
      ,B.급여월
      ,B.월급여
FROM 사원     A
    ,급여지급 B
WHERE B.사원번호 = A.사원번호
AND B.급여월 = '201012'        --> '=' 연산자로 조인조건을 추가하여 1:1로 만들어 줌
;

 

가볍게 보시면 될거같습니다 CROSS JOIN을 한후 = 연산자로 조인조건을 추가 해준것 같습니다. ( 이게 많이 쓰일까요...????ㅎㅅㅎ..)

 

M:M 관계에서 부모테이블이 기준집합이 되어야하는 때

SELECT A.사원번호           사원번호
      ,A.월급여             전년급여
      ,B.월급여             당해급여
      ,B.월급여 - A.월급여  증가액
FROM 급여지급 A
    ,급여지급 B
WHERE B.사원번호 = A.사원번호
AND A.급여월 BETWEEN '201001' AND '201012'
AND B.급여월 BETWEEN '201101' AND '201112'
;

 

A,B를 서로 묶어 2010년 급여와 2011년 급여를 비교하여 증가액을 보여주려고 했지만, 의도와 다른 결과가 나옵니다.

급여지급의 기본키는 급여월과 사원번호이지만 조인조건으로 사원번호만 제공했기 때문에, M:M관계가 됩니다. M:M관계가 되는것이 왜 문제가 되나 싶으실겁니다. 하지만 큰문제들이 많아요 DB ERD 짜실때는 그냥 연결되면 육안으로는 연결된것처럼 보이지만 실제 DB에선 무조건 중간 테이블이 존재해야합니다. 그래서 저희는 중간테이블을 통해서 JOIN을 사용하여 각각 GROUP BY한 후를 보여드리겠습니다.

 

-- 수정 후

SELECT A.사원번호           사원번호
      ,A.월급여             전년급여
      ,B.월급여             당해급여
      ,B.월급여 - A.월급여  증가액
FROM (
     SELECT A.사원번호     사원번호
           ,SUM(A.월급여)  월급여
     FROM 급여지급 A
     WHERE A.급여월 BETWEEN '201001' AND '201012'
     GROUP BY A.사원번호   --> 조인 조건의 컬럼으로 GROUP BY 
     ) A
    ,(
     SELECT B.사원번호     사원번호
           ,SUM(B.월급여)  월급여
     FROM 급여지급 B
     WHERE B.급여월 BETWEEN '201101' AND '201112'
     GROUP BY B.사원번호   --> 조인 조건의 컬럼으로 GROUP BY 
     ) B
WHERE B.사원번호 = A.사원번호  --> 1:1이 되었으므로 조인 가능
;

 아마 좀 어렵게 느껴지실겁니다.  하지만 어렵지 않아요 ! 중간테이블을 사용한다고 했는데 정확힌 중간테이블인 <사원>테이블의 사원번호를 이용하여 1:M, 1:N 으로 나누어 진행하는겁니다. 각 월급여에 대한 정보를 나눈뒤 사원번호를 묶어 두 테이블을 JOIN하여
1:1 으로 만든 다음에 쿼리를 보여주는겁니다.

 

 

어때요..? 어렵지만 M:M 이란 테이블을 직면했을때 어떻게 다뤄야하는지 이해가 가시나요? 

저는 아직...잘 모른다 잘안다 뭐라할 수 없지만 조금이나마 더 전진하게되었습니다... 여러분들도 쿼리 어렵다고 생각하지마시고 공부 많이하면서 잘 다뤄봐요 다음은 아마 Outer Join을 다뤄보지 않을까 싶습니다. 모두 봐주셔서 감사합니다.