[SQL 기초 - 9장] 오라클 SQL OUTER JOIN, 서브쿼리

 

안녕하세요!

 

오늘은 OUTER JOIN 에 대해 알아보려고 합니다.

저도 공부하면서 많이 헷갈리더라구요..

혹여나 보다가 틀린 부분이 있다면 과감하게 지적해주세요!

그럼 시작하겠습니다!

 

시작하기에 앞서 무슨 말 할 지 아시죠??

복습이 가장 중요합니다 여러분!

오라클 SQL JOIN 이란?

OUTER JOIN

OUTER JOIN 의 반대 개념인 INNER JOIN 도 있지만

INNER JOIN은 우리가 지난번에 배웠던 평 JOIN 문을 뜻하기 때문에

OUTER JOIN만 보도록 하겠습니다.

​

OUTER JOIN 은 조인 조건을 만족하지 않더라도 (조인에 실패하더라도)

기준이 되는 테이블 쪽의 데이터(컬럼)은 조회가 되도록 하는 조인 방식 입니다.

​

OUTER JOIN의 종류에는 세가지가 있습니다.

1. LEFT OUTER JOIN : 조인 키워드의 왼쪽에 위치하는 테이블을 기준삼아 OUTER JOIN 시행

2. RIGHT OUTER JOIN : 조인 키워드의 오른쪽에 위치하는 테이블을 기준삼아 OUTER JOIN 시행

3. FULL OUTER JOIN : LEFT + RIGHT OUTER JOIN - 중복되는것 제외

OUTER JOIN 형식​

형식은

ANSI OUTER JOIN

ORACLE OUTER JOIN

위와 같습니다.

예시를 통해 더 알아보도록 하겠습니다.

 

 

지난번에 봤던 문제를 기억하시나요?

사원번호, 사원이름, 사원의 상사 사원번호, 사원의 상사 이름을 조회하고 싶으면

어떻게 해야 할까요? 라는 질문에서 SELF-JOIN 을 이용했었죠.

​

위 쿼리의 결과입니다.

KING 의 m.empno 와 m.ename 값이 null 이죠?

KING 은 mgr 즉, 상사가 없기 때문입니다.

그런데 KING 의 이름과 사번은 조회가 됬네요?

무엇을 뜻하는 것일까요?

다음을 한 번 보도록 하죠

​

​

이번에는 기준이 되는 컬럼을 바꿔서 RIGHT OUTER JOIN 으로 바꿔줬습니다.

결과가 다르게 나왔네요.

근데 중간에 TURNER 부터 그 이후에는 사번과 사원이름이 나오지 않았습니다.

​

위의 두 예시를 보고 감이 오셨나요?

이 처럼 OUTER JOIN 은 조인 조건에 만족하지 않더라도

기준이 되는 컬럼의 데이터는 나오게 됩니다.

​

그렇다면 첫 번째 예시의 쿼리를

ORACLE SQL 로 바꾸면 어떻게 될까요?

 

위 처럼 바꿀 수 있습니다.

저렇게 조회를 하면 첫 번째 예시처럼 결과가 동일하게 나타나죠.

해석을 해보자면,

emp e가 기준이되고 그 e의 empno와 ename 을 조회하기 위해서 m 쪽에 (+)를 붙여주었습니다.

즉, 기준이 되는 테이블의 반대쪽에 (+)를 붙이면 됩니다.

​

만약 (+)가 없다면 아까 위의 첫 번째 결과에서 KING 의 데이터는

조회되지 않겠죠?

KING의 mgr 값과 그에 맞는 사번을 가진 사람이 있어야

조인조건을 만족하는 거니까요

​

그런데 만약 저 (+)가 e.mgr 쪽에 붙어있으면 어떻게 될까요?

두 번째 결과처럼 나오게 되겠죠.

기준이 m 쪽으로 바뀌게 된것이니까요.

그런데 TURNER 부터는 사원의 사번과 이름이 null로 나오게 된것을 보면

그들은 누군가의 상사가 아닌 가장 아래 직원이라는 것을 알 수 있습니다.

​

정리해서 쉽게 말하면,

데이터가 없지만 나와야 하는 쪽에 (+)를 붙여주면 됩니다!!!

OUTER JOIN 사용시 주의점

하지만 이런 OUTER JOIN 을 사용할 때 주의할 점이 있습니다.

OUTER JOIN 시 ANSI SQL로 작성할 때

WHERE 절에 별도의 다른 조건을 기술할 경우 원하는 결과가 안나올 수 있습니다.

OUTER JOIN의 결과가 무시가 되는것이죠.

그러니 가급적이면 ORACLE SQL 로 작성하는 것을 추천드립니다!

​

하나 더 예시를 보겠습니다.​

위의 쿼리를 보면 지금 m 쪽에 (+)가 있으니 e 쪽이 기준이 되어 있죠?

그래서 m.empno 와 m.ename 이 null 이어도

e.empno 와 e.ename 에 대한 데이터는 나오게 된것이죠

그리고 오른쪽이 널인 사원들은 조인 조건을 만족하지 않는다는 사실도 알 수 있겠습니다.

FULL OUTER JOIN

다음으로 FULL OUTER JOIN 에 대해 알아보겠습니다.

사실 FULL OUTER JOIN 은 그냥..

LEFT OUTER JOIN 과 RIGHT OUTER JOIN 을 합친것 입니다.

​

FULL OUTER = LEFT OUTER + RIGHT OUTER - 중복

위와 같이 중복 값들이 제거 되고 하나만 남게 됩니다.

집합의 개념이라고 생각하시면 될 것 같습니다.

​

그리고 ORACLE SQL 에서는 FULL OUTER JOIN 문법을 제공하지 않습니다.

아까 기준이 되었던 e, m 을 예로 들면

e.mgr(+) = m.empno(+) 와 같이 쓸 수 없다는 말입니다.

​

FULL OUTER JOIN 을 검증하는 방법으로

UNION 과 MINUS 를 사용할 수 있습니다.​

FULL OUTER JOIN 검증

위와 같이 쿼리를 작성하여 검증 할 수 있습니다.

UNION 은 테이블을 합쳐주고

MINUS 는 테이블의 교집합을 빼주는 기능을 합니다.

그렇다면 위의 결과는 어떻게 나올까요??

아까 제가 말한 개념을 생각해보시면 답이 나올겁니다 ㅎㅎ

결과는 스스로 생각해 보세요!

CROSS JOIN​

마지막으로 CROSS JOIN 이 있는데요,

이 것은 말로만 설명하겠습니다.

왜냐, 여러분이 공부를 하면서 이미 은연중에 사용해 보셨을 겁니다.

 

위와 같은 형식인데요, 조인을 하려다가 실수(?)로 WHERE 절을 빼먹었는데도

조회가 되는 경우가 있었을 겁니다.

그 때의 경우가 바로 CROSS JOIN 입니다.

묻지마 조인(?)이라고도 하며 조인 조건이 없기 때문에

테이블1의 1행이 테이블2의 모든행과 조인되고

테이블1의 2행이 테이블2의 모든행과 조인되고

테이블1의 3행이 테이블2의 모든행과 조인되고

.

.

.

.

.

결국 조회되는 테이블은

테이블1의 행과 테이블2의 행을 곱한 수 만큼 나오게 됩니다.

따라서 테이블간 적용하는 경우보다는

데이터를 복제할 때 많이 사용한다고 합니다.

서브쿼리​

다음으로 아주아주 중요한 서브쿼리에 대해서 배워보려고 합니다!

집중하세요!

서브쿼리란 SQL 내부에서 사용된 SQL 입니다.

사용 위치에 따라서 분류할 수 있는데요,

SELECT 절에서는 스칼라 서브쿼리,

FROM 절에서는 인라인뷰,

WHERE 절에서는 그냥 서브쿼리

라고 부릅니다.

​

문제를 해결하면서 익히는게 좋겠죠?

일반 서브쿼리

만약에 emp 테이블에서 SMITH 사원이 속한 부서에 속하는 사람들은 누가 있을까?

라는 질문이 있다면 여러분들은 문제를 어떻게 접근 하실건가요?

​

먼저 SMITH 사원이 속한 부서를 알아내야하고,

그 데이터를 이용해서 해당 부서에 속한 사람들을 찾아내실 겁니다.

​

하나씩 살펴보면

위의 쿼리로 SMITH 사원이 속한 부서를 알아낼 수 있습니다.

20번 부서네요. 그럼 다음으로

​

위의 쿼리로 20번 부서에 속한 사람들을 알아낼 수 있겠습니다.

저렇게 두 개의 과정을 통해서 문제를 해결할 수 있습니다.

하지만 여기서 만약 SMITH 의 부서가 다른 부서로 옮겨진다면

그에 따른 데이터도 함께 변해야 하죠.

​

따라서 우리가 원하는 것은 고정된 부서번호로 사원 정보를 조회하는 것이 아니라

SMITH가 속한 부서가 바뀌더라도 쿼리를 수정하지 않도록 하는 것 입니다.

​

이 때 사용하는 것이 바로 서브쿼리로,

위의 두 쿼리를 하나로 합칠 수 있습니다.

 

WHERE 절에서 deptno = 20 이라는 고정 상수를 쓰지 않고

또 하나의 쿼리를 () 안에 작성 해줌으로써,

SMITH 의 부서가 바뀌어도 우리는 쿼리를 수정할 필요가 없게 되는 것이죠.

​

여기서 알 수 있는 서브 쿼리의 특징!!

​

1. 데이터가 변경 되더라도 우리가 원하는 데이터 셋을 쿼리 수정 없이 조회할 수 있다!

2. 코드 변경이 필요 없다!

3. 유지보수가 편하다!

​

서브쿼리, 언제 사용하는지 아시겠죠?

​

​

그렇다면 주의할 점을 알려드리겠습니다.

​

위의 쿼리는 논리적으로 봤을 때 맞는 쿼리일까요?

​

아닙니다. 서브쿼리의 구문을 살펴보면

ename 이 SMITH 이거나 ALLEN 인 deptno 를 조회하고 있죠?

그런데 전체 쿼리에서 WHERE 절과 연결시켜보면

deptno = X OR Y 처럼 해석되게 되는데

이것이 가능한 쿼리인지 생각해보시면 될 것 같습니다.

​

따라서 위의 쿼리는

 

이와 같이 IN 연산자를 사용하여 작성 해주셔야 합니다.

​

정리 하자면, 연산자와, 서브쿼리의 반환 행수에서

= 연산자를 사용시 서브쿼리에서 여러개 행(값)을 리턴하면 논리적으로 맞지 않다.

IN 연산자를 사용해야 서브쿼리에서 리턴하는 여러개 행(값)과 비교가 가능하다.

스칼라 서브쿼리

위에서 한 것들은 그냥 서브쿼리였습니다.

WHERE 절에서 사용한 서브쿼리죠.

그리고 아까 말 한 FROM 절에서 사용하는 인라인뷰는

전에 많이 사용했으므로 넘어가고

SELECT 절에서 사용하는 스칼라 서브쿼리에 대해서 알아보겠습니다.

​

스칼라 서브쿼리는 사용시에 제약사항이 있습니다.

반드시 서브쿼리가 하나의 행, 하나의 컬럼을 반환해야 하는 것이죠.

무슨 말이냐?

​

위의 사진은 스칼라 서브쿼리를 작성한 예 입니다.

위에 제가 블록 지정해 놓은 곳만 쿼리를 실행 시켜 보면

 

위와 같이 나오게 되는데요,

이것은 다중행 복수컬럼이죠?

​

​

또 다른 예로

위와 같이 스칼라 서브쿼리를 작성하고 결과를 실행해 봤더니

단일행 복수컬럼이 나왔습니다.

​

위와 같은 쿼리들은 메인쿼리(전체쿼리)를 실행했을 때 오류가 발생하게 됩니다.

​

위와 같이 스칼라 서브쿼리의 결과가

무조건!! 단일행 단일컬럼의 결과가 나오는 메인쿼리만 조회가 가능해집니다!​

​

오늘 내용은 여기까지 입니다.

내일 또 서브쿼리에 대해서 추가적인 내용을 다룰 예정이니

다시 방문해주세요!

오늘도 끝까지 봐주셔서 감사합니다!