Oracle Join이란 무엇인가? : Outer Join에서 (+)가 없다면?

내가 지금 진행하는 프로젝트에서
ASIS 에서 사용하던 oracle 쿼리를 postgreSQL 쿼리로 변경하는 작업을 하고 있는데

사용하는 쿼리 중의 많은 양이 오라클 조인을 사용하고 있다.
그래서 Oracle Join에 대해 알아보려고 한다.

 

오라클 조인이란?

다른 DBMS에서 Join을 위해서는 INNER JOIN, LEFT OUTER JOIN, CROSS JOIN 등과 같은 
ANSI JOIN을 사용할 것이다.

하지만 Oracle에서는 Oracle Join이라는
조인을 위한 한가지 방법이 더 있다.

ANSI JOIN은 JOIN과 ON구문으로 명시하여 조인컬럼을 구분하지만
오라클 조인은 WHERE 절에 조인 컬럼과 조회 조건이 공존한다.

예시를 들어보자.

학생 ID와 이름이 있는 학생 테이블과
과제ID, 학생 ID, 점수가 있는 과제 테이블이 있다고 한다.

데이터는 위와 같이 들어있다.

 

INNER JOIN

-- ANSI JOIN
select a.student_id
	, b.student_id
    , b.score
from student a		-- main 테이블
INNER JOIN ASSIGNMENT  b   -- join 테이블 
ON a.student_id = b.student_id		-- join 조건

ANSI JOIN에서는 INNER JOIN과 ON 키워드로
Join 테이블과 Join 조건을 작성한다.

Join 시 두 테이블에 공통적으로 있는 데이터가 표시된다.

Oracle Join에서는 ,(콤마)로 조인할 테이블을 연결하고
where 절에 조인 조건을 작성한다.

-- 오라클 조인
select a.student_id
	, b.student_id
    , b.score
from student a		-- main 테이블
	, ASSIGNMENT b	-- join 테이블
where a.student_id = b.student_id	-- join 조건

해당 쿼리는 INNER JOIN 쿼리가 된다.

 

 

Oracle Join 과 Outer Join

그럼 Oracle Join에서 Outer Join은 어떻게 사용하는가?

바로 (+) 기호를 사용한다.
Oracle에서 (+)를 Join 조건의 컬럼에 붙이면
해당 컬럼에 데이터가 부족하면 NULL을 보이라는 의미이다.

즉, Outer join 인 것이다.

 

LEFT OUTER JOIN

예시를 들어보자.
먼저 ANSI JOIN으로 LEFT OUTER JOIN을 생성하면 아래와 같다.

-- ANSI JOIN
select a.student_id
	, b.student_id
    , b.score
from student a		-- main 테이블
LEFT OUTER JOIN ASSIGNMENT  b   -- join 테이블 
ON a.student_id = b.student_id		-- join 조건

LEFT OUTER JOIN 키워드와 ON을 사용하여 JOIN한다.

 

다음으로 Oracle Join을 살펴보자

-- 오라클 조인
select a.student_id
	, b.student_id
    , b.score
from student a		-- main 테이블
	, ASSIGNMENT b	-- join 테이블
where a.student_id = b.student_id(+)	-- join 조건

LEFT OUTER JOIN을 위해
NULL이 와도 되는 컬럼 b.student_id 컬럼에 (+) 기호를 작성했다.

 

Right Outer Join

Right Outer Join도 동일하다 Null이 되도 되는 조인 조건에 (+)를 붙이면 된다.

먼저 ANSI JOIN을 보면 아래와 같다.

-- [ANSI 표준]
SELECT a.STUDENT_NAME, b.SCORE
FROM STUDENT a 		-- main 테이블
RIGHT OUTER JOIN ASSIGNMENT b 	-- join 테이블
ON a.STUDENT_ID = b.STUDENT_ID;		-- join 조건

RIGHT OUTER JOIN과 ON 키워드로
JOIN 테이블을 연결하였다.

 

그럼 다음으로 Oracle Join을 보겠다

-- [Oracle 구문]
SELECT a.STUDENT_NAME, b.SCORE
FROM STUDENT a 	-- main 테이블
	, ASSIGNMENT b 	-- join 테이블
WHERE a.STUDENT_ID(+) = b.STUDENT_ID;	-- Join 조건

Oracle Right outer join은 ,로 테이블을 연결하고 
main 테이블의 컬럼에 (+)를 붙여 Student_Name에 Null이 올 수 있도록 하였다.

 

특이 케이스 : 만약 (+)와 일반 조인 조건이 섞인다면?

오라클 조인은 Where 절에 조인조건이 함께 있다.
그런 데 만약 조인 테이블에 어떤 컬럼에는 (+)를 넣고 어떤 컬럼에는 (+)를 안넣으면 어떻게 될까?

오류가 날까?

정답은
(+)가 있으면 outer join 조건으로 받아들이고
(+)가 없으면 where 조건으로 인지한다.

예를 들어서 보겠다.

먼저 모든 쿼리에 (+)가 있는 쿼리를 보자.

SELECT a.STUDENT_NAME, b.SCORE
FROM STUDENT a, ASSIGNMENT b
WHERE a.STUDENT_ID = b.STUDENT_ID(+)
  AND b.SCORE(+) > 60;               -- ✅ 일반 조건에도 (+) 부착!

이러한 쿼리에서는
아래와 같이 b.score > 60도 on절 안에 있는것으로 인식한다.

ANSI 조인으로 표현하면 아래와 같다.

FROM STUDENT a 
LEFT JOIN ASSIGNMENT b 
ON (
    a.STUDENT_ID = b.STUDENT_ID 
    AND b.SCORE > 60
);

 

그렇다면 특정 컬럼에만 (+)가 있는 경우에는 어떨까?
아래 쿼리는 student_id로 left outer join을 하였지만 b.score에는 (+)가 없다.

SELECT a.STUDENT_NAME, b.SCORE
FROM STUDENT a			-- main 테이블
	, ASSIGNMENT b		-- join 테이블
WHERE a.STUDENT_ID = b.STUDENT_ID(+) -- 조인은 아우터 조인
  AND b.SCORE > 60;                  -- ⚠️ 일반 조건에 (+) 누락!

 

이런 경우에 oracle은
ANSI JOIN의 아래 쿼리와 동일하다고 인식한다.

FROM STUDENT a 
LEFT JOIN ASSIGNMENT b 
ON (a.STUDENT_ID = b.STUDENT_ID)
WHERE b.SCORE > 60;

 

(+) 혼합시 실행계획

실행 계획을 살펴볼까?
먼저 처음 쿼리처럼 모든 OUTER JOIN 컬럼에 (+)가 붙는 경우에는

SELECT a.STUDENT_NAME, b.SCORE
FROM STUDENT a, ASSIGNMENT b
WHERE a.STUDENT_ID = b.STUDENT_ID(+)
  AND b.SCORE(+) > 60;               -- ✅ 일반 조건에도 (+) 부착!

 

아래와 같은 실행 계획이 나오는데

 

STUDNET 전체와
b.score > 60으로 검색한 ASSIGNMENT의 2건을
조회한다.

 

그래서 아래와 같은 결과가 나오는 것이다.

 

그럼 b.score에 (+)가 안붙는 경우에는 어떨까?

SELECT a.STUDENT_NAME, b.SCORE
FROM STUDENT a			-- main 테이블
	, ASSIGNMENT b		-- join 테이블
WHERE a.STUDENT_ID = b.STUDENT_ID(+) -- 조인은 아우터 조인
  AND b.SCORE > 60;                  -- ⚠️ 일반 조건에 (+) 누락!

뭔가 더 복잡한 실행계획이 나왔다.

이건 INNER JOIN의 실행계획과 동일하고

SELECT a.STUDENT_NAME, b.SCORE
FROM STUDENT a, ASSIGNMENT b
WHERE a.STUDENT_ID = b.STUDENT_ID
  AND b.SCORE > 60;

결과적으로 해당 쿼리는 INNER JOIN처럼 조회했다는 것을 알 수 있다.

 

마치며

이번에는 Oracle의 Oracle Join에 대해 알아봤다.
프로젝트를 하면서 이런저런 쿼리를 보니..
현장은 학교에서 배운거랑은 많이 다르다는걸 많이 느끼는 요즘이다.

다음에는 Oracle Optimizer가 왜 ANSI 조인을 오라클 표현식으로 바꿔서 해석하는지,
Optimizer가 어떻게 쿼리를 최적화하는지에 대해 공부해 보고싶다.

참고 사이트

Oracle FreeSQL: Learn and practice SQL with the worksheet

 

Outerjoins in Oracle | optimizer