신기한 연구소

안녕하세요. 신기한 연구소입니다.

개발을 하다 보면 계층형 쿼리를 만나게 됩니다.

사실 계층형 쿼리를 제대로 배워서 사용하는 개발자도 있겠지만

그냥 다른 개발자가 만든 것을 복사해서 대충 쓰거나

잘 이해도 못하고 사용하는 경우도 있습니다.

필자도 사실 처음 그랬던 기억이 있습니다. ㅎㅎ

개발하다 가끔 만나는 쿼리인데 본 적 있나요?

이제 제대로 이해하고 사용해 봅시다~~

START WITH절은 루트노드(행)을 명시합니다.

무슨 의미일까요?

EMP 테이블을 확인해 보면 해당 사원의 관리자가 있습니다.

그 관리자가 최종 관리자일수도 있지만

그 관리자도 더 상위 관리자가 있을 수 있습니다.

하지만 결국 마지막 최종 관리자가 있을 것이고

그 최종 관리자는 더 이상 자신의 관리자는 없기에

관리자 컬럼은 NULL일겁니다.

START WITH

그래서 시작을 최종(최고)관리자부터 해야 하기에

START WITH에서 관리자 컬럼이 NULL인 부분을 조건식으로 넣게 됩니다.

EMP에서 관리자 컬럼 MGR을 보면 ENAME = ‘KING’이 NULL로 되어 있습니다.

최종 관리자라는 의미네요.

CONNECT BY

CONNECT BY는 연결 고리를 가지고 목록을 가져옵니다.

먼저 START WITH에서 조건에 맞는 최상위 행을 가져옵니다.

이제 최상위 행 하나를 갖게 되었습니다.

다음으로 최상위 행을 관리자로 갖는 다음 계층 데이터를 가져와야 합니다.

그럼 현재 찾아 온 최상위 관리자의 EMPID를 추출해서 다음 행들을 구해야 합니다.

그 최상위 EMPID를 MGR로 갖는 행들을 찾아야합니다.

CONNECT BY PRIOR EMPID = MGR

연결하는 방식은 미리 구한 앞 행의 EMPID와 구해야 할 MGR이 같은 행들을 구합니다.

그럼 이제 START WITH에서 구한 최상위 행과

그 행의 EMPID를 MGR로 갖는 행들을 갖게 되었습니다.

이제 다음 작업은 최상위 행으로 구한 두번째 계층의 행들을 가지고

그 계층들의 EMPID를 MGR로 갖는 다음 계층의 행들을 구합니다.

그렇게 쭉~ 끝까지 구하면 됩니다.

CONNECT BY PRIOR EMPID = MGR

이 표현식이 이해 되셨나요?

연결 방식은 바로 전에(PRIOR) 구한 계층의 EMPID를 MGR로 갖는 데이터를 구한다.

그렇게 이해하면 되겠습니다.

만약 PRIOR을 반대로 설정하면 어떻게 될까요?

CONNECT BY EMPID = PRIOR MGR

연결 방식은 바로 전에(PRIOR) 구한 계층의 MGR을 EMPID로 갖는 데이터를 구한다.

START WITH로 먼저 최상위 행을 구했는데..

그 다음 구할 계층이 최상위 행의 MGR (최상위니 NULL이죠?) 을 EMPID로 갖는 값은

없겠네요..NULL이니까요.

이해가 되셨나요?

이제 계층형 쿼리를 만들 수 있게 되었으니 더 다양한 기능을 살펴보겠습니다.

LEVEL

계층형 쿼리를 조회하면 현재 행이 계층의 몇번째 레벨인지 표시해 줍니다.

계층형 쿼리에서만 사용하는 모조 컬럼입니다. 실제 테이블에 있는 컬럼은 아니지만

SELECT 문에서 사용할 수 있는 컬럼입니다.

최상층 레벨을 1로 해서 쭉~ 2, 3, 4로 나옵니다.

이 LEVEL을 가지고 여백을 주거나 다른 용도로 활용할 수 있습니다.

CONNECT_BY_ROOT

현재 조회된 행들의 최상위 정보를 보여줍니다. 역시 모조 컬럼입니다.

CONNECT_BY_ROOT(ENAME), CONNECT_BY_ROOT ENAME

이렇게 두가지 방식으로 사용할 수 있습니다.

CONNECT_BY_ISLEAF

현재 행이 마지막 계층의 데이터인지 확인하는 기능입니다.

모조 컬럼입니다.

즉 LEAF를 만나면 1을 반환하고 아니면 0을 반환합니다.

CONNECT_BY_ISCYCLE

현재 행의 조상이기도 한 자식을 갖는 경우 1을 반환합니다.

이 모조 컬럼을 사용하기 위해서는

CONNECT BY 다음에 NOCYCLE를 사용해야 합니다.아래 쿼리를 확인해봅니다.

상위 계층의 부모가 자식이 되는 크로스 상황이 되어 있습니다.

이런 경우 부모가 잘못 설정된 부분을 찾기 위해 사용하면 되겠네요.

SYS_CONNECT_BY_PATH( 컬럼, 구분자)

루트 노드부터 해당 행까지의 경로를 입력한 컬럼 기준으로

구분자를 사용해서 보여줍니다.

지금까지 계층형 쿼리를 알아봤습니다.

(추가)

하나 더 빠진 부분인데요.

ORDER SIBLINGS BY에 대해 알아볼께요.

ORDER BY 없이 조회한 결과 입니다.

레벨 2 이후 정렬이 안된 모습을 볼 수 있습니다.

그래서 ORDER BY ENAME 을 추가했습니다.

그런데 결과가 우리가 원하는 계층별로 정렬되는 것이 아닌 

레벨과 전혀 관계없이 그냥 순차적으로 정렬이 되서 계층이 망가졌습니다.

ORDER SIBLINGS BY ENAME을 사용했습니다.

레벨 단위로 정렬이 잘 되었습니다.이제 SIBLINGS를 사용해서 깔끔한 계층형 쿼리를 만들 수 있습니다.

이제는 편하게 이용할 수 있겠네요.

잘못된 내용이나 수정이 필요하면 댓글 주세요.

아래 하트(공감) 버튼을 눌러서 더 다양한 글을 쓸 수 있게 응원 부탁드립니다. 감사합니다.

안녕하세요. 신기한 연구소입니다.

기본적으로 SQL 작업을 하는 개발자는 이 포스팅이 큰 의미가 없을 수 있어요.

하지만 처음 접하는 개발자나 오랜만에 사용해서 갑자기 생각이 안나는 경우를

대비해서 공유도 하고 나 자신도 활용하기 위해 작성해 봅니다.

이번 포스팅은 외부 조인(OUTER JOIN)입니다.

어떤 경우에 사용하는지 먼저 예를 들어봅니다. 

팀원 정보를 조회하는 화면이 있습니다.

관리자는 팀원들의 정보를 한눈에 보기를 원합니다.

하지만 테이블은 관련 정보를 기준으로 그룹화되어 여러 테이블로 분리되어 있습니다.

그 중 하나를 살펴보면..

관리자는 팀원들의 사내 동호회 활동에 대해 궁금해합니다.

하지만 모든 팀원들이 사내 동호회를 가입한 건 아닙니다.

이전 포스팅에서 배운 내부 조인(INNER JOIN)을 활용해 보면

사원 테이블과 동호회 테이블이 조인이 되어 서로 연결된 정보를

한 데이터 집합으로 화면에 조회가 됩니다.

그런데 문제가 발생합니다.

팀원이 12명인데 9명만 나오는 현상이 발생합니다.

왜 그런지 살펴보니 3명은 아직 동호회에 가입하지 않은 겁니다.

그래서 사원 정보에 동호회 코드가 NULL이기에 조인 성립이 되지 않아서

조회 대상에서 누락된 겁니다.

관리자는 팀원 전원인 12명의 정보를 원합니다.

가입이 안되었다면 ‘미가입’ 또는 빈칸으로 나오기를 바랍니다.

이런 경우 팀원 전원이 나와야 하기에 드라이빙 테이블이 됩니다.

그리고 동호회 테이블과 조인하면서 있으면 보여주고 없으면 NULL을

보여주면 됩니다.

이런 경우에 외부 조인(OUTER JOIN)을 사용하면 됩니다.

이제 원하는 데이터가 화면에 나와서 관리자는 만족할 겁니다.

그런데 쿼리를 보니 처음 보는 기호가 있습니다.

(+)인데요..

바로 이것이 외부 조인(+)에 사용하는 기호입니다.

기본 사용법은 다음과 같습니다.

주 테이블(드라이빙)은 그대로 두고 없어도 NULL을 더해서 보여주라고 (+)를

상대 테이블의 컬럼에 전부 붙여주면 됩니다.

위 쿼리를 보면 CLUBNO(+) <> ‘10’ 조건을 넣을 때 조인 조건이 아니지만

(+)를 넣었습니다.

WHERE절의 모든 조건 중 대상 테이블의 컬럼이 나오면 조인 조건이든 아니든

(+)를 붙여줘야 한다는 겁니다.

그리고 OR연산자에는 사용할 수 없습니다.

해당 쿼리는 오류가 발생합니다.

그런데 기존 버전에서는 IN 연산자는 IN의 조건 값이 하나를 초과하면

외부 조인이 안된다고 하는데 테스트해보니 잘 되네요.

개선되었나 봅니다.

다음으로 중요한 부분인데요.

대상 테이블의 컬럼에 외부 조인 (+)를 붙인 후 서브 쿼리와 사용할 수 없습니다.

이렇게 조회를 하면 오류가 발생하니 주의하세요.

이제 ANSI 표준에 대해 살펴보겠습니다.

외부 조인 사용방법은 내부 조인과 비슷합니다.

FROM절에 외부 조인을 적용한 쿼리입니다.

왼쪽 사원 테이블이 드라이빙 테이블이기에 LEFT를 사용했습니다.

기존 외부 조인에서 드라이빙 테이블은 그냥 두고 대상 테이블 컬럼에 (+) 붙인 것과 같습니다.

만약 동호회 테이블을 드라이빙 테이블로 사용하고 사원 테이블을 대상 테이블로 한다면

FROM 절에 RIGHT OUTER JOIN을 사용하면 됩니다.

오른쪽에 있는 테이블이 주 테이블(드라이빙)이라는 의미거든요.

그리고 OUTER를 생략해도 같은 의미가 됩니다.

 ANSI 기준에 따라 당연히 조인 조건은 ON, USING을 사용해야 합니다.

추가 조건은 ON, USING에 추가해도 되고

WHERE 절을 추가해서 사용해도 되지만 WHERE절에 조인 조건을 넣으면 오류가 납니다.

마지막으로 기존 조인에서 불가능한 부분을 ANSI에서 편하게 사용할 수 있는

외부 조인이 있습니다.

기존 조인은 드라이빙과 대상 테이블 두 군데 (+)를 붙일 수 없습니다.

두 번째 쿼리는 오류가 납니다.

왜 붙여야 하냐면..

만약 양쪽 테이블 정보를 서로 누락 없이 빈 곳은 NULL을 채워서

전부 보여주고 싶은 경우가 있을 겁니다.

기존 조인의 경우는 따로 조회해서 합쳐야 하는 번거로움이 있지만

ANSI를 사용하면 FULL OUTER JOIN으로 간단히 해결됩니다.

잘못된 정보나 수정이 필요하면 댓글 주세요.

아래 하트(공감) 버튼을 눌러서 더 다양한 글을 쓸 수 있게 응원 부탁드립니다. 감사합니다.