오라클(ORACLE) - FULL OUTER JOIN ANSI에서 오라클로 변환

ANSI FULL OUTER JOIN표현을 오라클 문법으로 표현해보자!

 

 

#테스트를 위한 데이터 준비!

WITH EMP1 AS (
SELECT 1 A, 2 B, 3 C FROM DUAL UNION ALL 
SELECT 2 A, 2 B, 3 C FROM DUAL UNION ALL 
SELECT 3 A, 2 B, 3 C FROM DUAL UNION ALL 
SELECT 5 A, 2 B, 3 C FROM DUAL
),
EMP2 AS (
SELECT 1 A, 2 B, 3 C FROM DUAL UNION ALL 
SELECT 2 A, 2 B, 3 C FROM DUAL UNION ALL 
SELECT 4 A, 2 B, 3 C FROM DUAL
)
SELECT *
  FROM EMP1
  
UNION ALL
SELECT TO_NUMBER('') AS A 
     , TO_NUMBER('') AS B 
     , TO_NUMBER('') AS C
  FROM  DUAL
UNION ALL

SELECT *
  FROM EMP2;

 

위 SQL에 대한 결과값

 

위 결과값 설명이다.

4행까지는 SELECT * FROM EMP1의 결과 집합이고 중간 5행은 EMP1과 EMP2의 UNIONALL로 합친 결과를 구분하기 위해서 넣은 DUAL의 집합이므로 신경 쓸 필요없다! 그리고 6~8 행은 EMP2의 결과집합이다!

 

 

오라클을 공부하다보니 ANSI 문법에 대해서 잘몰라서 가끔 보면 당황한다. 그래서 준비했다! 나를 위한 기록일지!

 

오라클에서는 FULL OUTER JOIN이라는 문법이 없다고 들었다. 그래서 FULL OUTER JOIN을 표현하기 위해서는 ANSI 문법으로 표현해야한다! 이제 ANSI문법 FULL OUTER JOIN을 오라클로 표현해보자!

 

 

 

---

 

 

가장 먼저 조인 INNER조인을 확인해보자 A컬럼을 PK로 생각하고 진행했다.

 

 

 

A컬럼인 PK로 INNER조인을 한 결과 집합이다! 이제 RIGHT OUTER JOIN, LEFT OUTER JOIN에 대해 이해해야한다!

기준컬럼의 반대편컬럼에 (+)기호를 줘서 OUTER JOIN을 할 수있다.

 

 

# LEFT OUTER JOIN

LEFT OUTER JOIN은  (+)기호가 없는 왼쪽 테이블의 컬럼을 기준으로 두고 조인하는 조인이다. 사진을보고 이해해보자!

 

먼저 레프트 아우터 조인된 결과이다.

EMP1의 A컬럼에서 5 와 3를 가진 레코드는 EMP2에서 조인될 컬럼(A)에서 데이터가 없으므로  조인에 성공하지 못하였다.

 

EMP1테이블에서 A컬럼의 데이터인  1과 2를 가진 데이터는 EMP2에서 1과 2를 가진 데이터를 찾아서 조인을 한다.

하지만 5와 3을 가진 데이터는 EMP2에서  없는걸 알 수 있다. 

 

5와 3을 가진 레코드는 EMP2에 조인할 값이 없는것을 확인하고 붉은색부분(OUTER) 와 NULL값이 합쳐져 아우터조인이 수행된다. 

 

아래그림은 한 건씩 조인되는 모습이다. 조인할 수 없는 레코드는 기준데이터 + NULL값으로 채워진다. 

 

 

 

조인할 데이터가 있는 지 한건씩 확인하면서 위와 같이 조인이 되고, 나머지 레코드도 조인할 데이터가 있는지 확인하면서 조인한다.

 

조인되지 못한 EMP1의 5를 가진 레코드 또한 조인할 EMP2의 조인컬럼에서 5가 있는지 확인하면서 조인, 없으므로 아우터조인을 수행 기준데이터는 테이블에 출력되고 조인되지 못하였으므로 나머지 부분은 NULL값으로 채워진다!  3을가진 레코드 또한 5와 마찬가지로 EMP2에 3을 가진 레코드가 없으므로 NULL로 채워진다.

 

 

# RIGHT OUTER JOIN 

RIGHT아우터 조인 또한 마찬가지다. RIGHT OUTER JOIN은  (+)기호가 없는 오른쪽 테이블의 컬럼을 기준으로 두고 조인하는 조인이다. 

 

A컬럼의 1, 2가진 레코드는  EMP1, EMP2에서 값은 데이터를 가지므로 조인된다.  하지만 오른쪽EMP2의 4를 가진 레코드는 OUTER조인되어 기준값인 4를 가진 레코드 + NULL값으로 채워진다.

 

 

 

# FULL OUTER JOIN

FULL아우터조인은 LEFT OUTER조인 RIGHT OUTER조인을 합친결과다

가장 먼저 집합의 특징은 중복되는 원소가 없다는 것이다.

 

그러므로  

LEFT OUTER JOIN

UNION

RIGHT OUTER JOIN 과 같다.

 

ANSI로는 아래와 같이 표현할 수 있다.

 

WITH EMP1 AS (
SELECT 1 A, 2 B, 3 C FROM DUAL UNION ALL 
SELECT 2 A, 2 B, 3 C FROM DUAL UNION ALL 
SELECT 3 A, 2 B, 3 C FROM DUAL UNION ALL 
SELECT 5 A, 2 B, 3 C FROM DUAL

),
EMP2 AS (
SELECT 1 A, 2 B, 3 C FROM DUAL UNION ALL 
SELECT 2 A, 2 B, 3 C FROM DUAL UNION ALL 
SELECT 4 A, 2 B, 3 C FROM DUAL
)

SELECT * 
  FROM EMP1 A FULL OUTER JOIN EMP2 B 
    ON (A.A = B.A)
;

 

위 ANSI표준으로 표현한 결과집합이다.

 

빨간박스는 INNER조인 , 파란박스 LEFT 아우터조인, 주황박스는 RIGHT 아우터조인의 결과다

위 결과를 오라클로 표현해보자

아래와 같이 표현 할 수 있다. 쫌 복잡하다 그냥 오라클에서도 ANSI표준 써야겠다.

 

 

 

 

+ 추가 22.09.13

다시 봤는데, 뭔가 좀 이상하다.... 조만간 수정을 해야겠다...