[과목 II 1장 8절] 표준 조인

1. FROM 절 조인 형태

• ANSI/ISO SQL에서 표시하는 FROM 절의 조인 형태는 다음과 같음
  • INNER JOIN
  • NATURAL JOIN
  • USING 조건절
  • ON 조건절
  • CROSS JOIN
  • OUTER JOIN

 

• ANSI/ISO SQL에서 규정한 조인 문법은 WHERE 절에 조인 조건을 기술하는 전통적인 방식의 조인 무법과 차이가 있음
  • 사용자는 기준 WHERE 절의 검색 조건과 테이블 간의 조인 조건을 구분 없이 사용하던 방식을 그대로 사용할 수 있으면서
  • 추가된 선택 기능으로 테이블 간의 조인 조건을 FROM 절에서 명시적으로 정의할 수 있게 됨

 

• INNER JOIN은 조인의 DEFAULT 옵션으로 조인 조건을 만족하는 행들만 반환
  • DEFAULT 옵션이므로 생략 가능
  • CROSS JOIN, OUTER JOIN과는 같이 사용할 수 없음

 

• NATURAL JOIN은 INNER JOIN의 하위 개념으로 볼 수 있으며, 두 테이블 간에 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN을 수행
  • NATURAL INNER JOIN이라고도 표시할 수 있으며, 결과는 NATURAL JOIN과 같음

 

• ANSI/ISO SQL 표준 방식의 JOIN 문법에서 가장 두드러진 특징은 ON 조건절을 통해 JOIN 조건과 데이터 제한 조건을 분리해 기술하는 것
  • 전통적인 방식의 JOIN 문법에서는 WHERE 절에 JOIN 조건과 데이터 제한 조건이 같이 사용돼 용도가 불분명할 수 있었는데
  • WHERE 절의 JOIN 조건을 FROM 절의 ON 조건절로 분리해 표시함으로써 사용자가 이해하기 쉬워짐

 

• ON 조건절의 경우 NATURAL JOIN처럼 JOIN 조건이 숨어 있지 않고, 명시적으로 JOIN 조건을 구분할 수 있음
  • NATURAL JOIN이나 USING 조건절처럼 칼럼명이 똑같아야 된다는 제약 없이 칼럼명이 상호 다르더라도 JOIN 조건으로 사용할 수 있으므로 다양한 요구 사항에 대해 유연하게 사용 가능
  • 다만, FROM 절에 테이블이 많이 사용될 경우 다소 복잡하게 보여 가독성이 떨어지는 단점 존재

 

2. INNER JOIN

• INNER JOIN은 조인 조건을 만족하는 행들만 반환함
• INNER JOIN 표시는 전통적인 방식의 조인 문법에서는 WHERE 절에 기술하던 조인 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 함

 

 

3. NATURAL JOIN

• NATURAL JOIN은 두 테이블 간에 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN 수행
• NATURAL JOIN이 명시되면 추가로 USING 조건절, ON 조건절, 그리고 WHERE 절에서 조인 조건을 정의할 수 없음
• SQL Server에서는 지원하지 않는 기능
• NATURAL JOIN은 조인이 되는 테이블의 데이터 성격과 칼러명 등이 동일해야 하는 제약조건이 있음
  • 전통 모델링 상의 부주의로 인해 동일한 칼럼명이더라도 다른 용도의 데이터를 저장하는 경우도 있으므로 주의해서 사용해야 함

 

• '*' 에스터리스크처럼 별도의 칼럼 순서를 지정하지 않으면 NATURAL JOIN의 기준이 되는 칼럼들이 다른 칼럼보다 먼저 출력되며 이때 NATURAL JOIN은 조인에 사용된 같은 이름의 칼럼을 하나로 처리함
  • 반면, INNER JOIN의 경우 첫 번째 테이블, 두 번째 테이블의 칼럼 순서대로 데이터가 출력되며 INNER JOIN은 NATURAL JOIN과 달리 별개의 칼럼으로 표시함

 

4. USING 조건절

• NATURAL JOIN에서는 같은 이름을 가진 모든 칼럼들에 대해 조인이 이루어지는 반면 FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있음
  • 해당 기능 또한 SQL Server에서 지원하지 않는 기능

 

• '*' 에스터리스크처럼 별도의 칼럼 순서를 지정하지 않으면 USING 조건절의 기준이 되는 칼럼이 다른 칼럼보다 먼저 출력되며 이때 USING JOIN은 조인에 사용된 같은 이름의 칼럼을 하나로 처리

 

5. ON 조건절

• ON 조건절은 칼럼명이 다르더라도 조인 조건을 사용할 수 있는 장점이 있음
• NATURAL JOIN의 조인 조건은 기본적으로 같은 이름을 가진 모든 칼럼들에 대한 동등 조건
  • 하지만 임의의 조인 조건을 지정하고, 이름이 다른 칼럼명을 조인 조건으로 사용하고, 조인 칼럼을 명시하기 위해서는 ON 조건절을 사용해야 함

 

• using 조건절을 이용한 조인에서는 조인 칼럼에 대해서 alias나 테이블 명과 같은 접두사를 사용하면 syntax 에러가 발생하는 반면 on 조건절을 사용한 조인의 경우는 이름이 같은 칼럼들에 대해 alias나 테이블 명과 같은 접두사를 명확하게 지정해주어야 함 i.g. DEPTNO -> E.DEPTNO
• ON 조건절은 WHERE 절의 조인 조건과 같은 기능을 하면서도, 명시적으로 조인의 조건을 구분할 수 있음
  • 하지만 FROM 절에 테이블이 많이 사용될 경우 다소 복잡하게 보여 가독성이 떨어지는 단점이 있음

 

가. WHERE 절과의 혼용

 

나. ON 조건절 + 데이터 검증 조건 추가

• ON 조건절에 JOIN 조건 외에도 데이터 검색 조건을 추가할 수는 있지만 검색 조건 목적인 경우는 WHERE 조건절을 사용하는 것을 권고함
  • 단, OUTER JOIN에서 조인의 대상을 제한하기 위한 목적으로 사용되는 추가 조건의 경우는 ON 절에 표기되어야 함

 

다. ON 조건절 예제

 

라. 다중 테이블 조인

 

6. CROSS JOIN

• CROSS JOIN은 일반 집합 연산자의 PRODUCT의 개념으로 테이블 간 조인 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 지칭
• 두 가지 테이블에 대한 CARTESIAN PRODUCT 또는 CROSS PRODUCT와 같은 표현으로, 결과는 양쪽 집합의 M * N 건의 데이터 조합이 발생함
• 정상적인 데이터 모델이라면 CROSS PRODUCT가 필요하는 경우는 많지 않지만, 간혹 튜닝이나 리포트를 작성하기 위해 고의적으로 사용하는 경우가 있을 수 있음
• 또한, 데이터 웨어하우스의 개별 DIMENSION을 FACT 칼럼과 조인하기 전에 모든 DIMENSION의 CROSS PRODUCT를 먼저 구할 때 유용하게 사용할 수 있음
  • 여기서 FACT 테이블은 거래나 사건과 같이 실제 측정치나 수치 데이터를 포함하고
  • DIMENSION 테이블은 시간, 제품, 지역 등 컨텍스트를 제공하는 설명적 데이터를 보관
  • 모든 DIMENSION의 크로스 프로덕트를 구한 후, 이 결과를 FACT 테이블과 조인하면, 사실 데이터가 없는 조합도 결과 셋에 포함되어 분석 시 누락된 항목 없이 전체적인 패턴을 파악할 수 있음
  • ex) 기간별, 지역별, 제품별로 매출 또는 거래 현황을 집계할 때 유용

 

7. OUTER JOIN

• 전통적인 방식의 조인 문법에서 Oracle은 조인 칼럼 뒤에 '(+)'를 표시했고, Sybase는 비교 연산자의 앞이나 뒤에 '(+)'를 표시했었음
  • 조인 조건과 WHERE 절 조건의 구분이 불명확하다는 단점, IN이나 OR 연산자 사용 시 에러 발생, '(+)' 표시가 누란된 조인 및 검색 조건 존재 시 OUTER JOIN이 아닌 INNER JOIN으로 수행, FULL OUTER JOIN 미지원 등 불편함이 많았음

 

• ANSI/ISO SQL 표준 방식의 OTER JOIN 문법을 사용하면 위에서 언급한 문제점들을 해결할 수 있으며 대부분의 관계형 DBMS에서 지원하므로 높은 호환성을 가지는 장점이 있음
• OUTER JION 역시 조인 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 함

 

가. LEFT OUTER JOIN

• 조인 수행 시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 조인 대상 데이터를 읽어 옴
  • 즉 Table A와 B가 있을 때 A가 기준이 되며 A와 B를 비교해서 B의 조인 칼럼에 조인 조건을 만족하는 값이 있으면 해당 로우의 데이터를 가져오고, 조인 조건을 만족하는 값이 없는 경우에는 B 테이블에서 가져오는 칼럼들은 NULL 값으로 채움
  • LEFT JOIN으로 OUTER 키워드 생략 가능

 

부연 설명

• INNER JOIN이라면 홈팀이 배정된 경기장만 출력됐겠지만
• LEFT OUTER JOIN을 사용했기 때문에 홈팀이 없는 경기장 정보까지 추가로 출력

 

나. RIGHT OUTER JOIN

• 조인 수행 시 LEFT JOIN과 반대로 우측 테이블 기준이 돼 결과를 생성함
• RIGHT JOIN 또한 OUTER 키워드를 생략 가능

 

다. FULL OUTER JOIN

• 조인 수행 시 좌측, 우측 테이블의 모든 데이터를 읽어 조인해 결과를 생성함
  • 즉 TABLE A와 B가 있을 때 A와 B가 모두 기준이 되며 RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일 (단, 조인에 성공한 행들은 한 번만 표시)
  • FULL JOIN으로 OUTER 키워드 생략 가능

 

8. INNER vs OUTER vs CROSS JOIN 비교

 

https://planharry.tistory.com/50

 

부연 설명

• 첫 번째 INNER JOIN의 결과는 양쪽 테이블에 모두 존재하는 키 값이 B-B, C-C인 2건을 출력
• 두 번째, LEFT OUTER JOIN의 결과는 TAB1을 기준으로 키 값 조합이 B-B, C-C, D-NULL, E-NULL인 4건을 출력
• 세 번째, RIGHT OUTER JOIN의 결과는 TAB2를 기준으로 키 값 조합이 NULL-A, B-B, C-C인 3건을 출력
• 네 번째, FULL OTER JOIN의 결과는 양쪽 테이블을 기준으로 키 값 조합이 NULL-A, B-B, C-C, D-NULL, E-NULL인 5건을 출력
• 다섯 번째, CROSS JOIN의 결과는 양쪽 테이블 TAB1과 TAB2의 데이터를 곱한 개수인 4 * 3 = 12건을 출력
  • 키 값 조합이 B-A, B-B, B-C, C-A, C-B, C-C, D-A, D-B, D-C, E-A, E-B, E-C인 12건 출력

 

참고

SQL 전문가 가이드 2020 개정판 - 한국데이터산업진흥원