본문 바로가기
Database/Oracle

[Oracle] 오라클 ANSI JOIN vs Oracle JOIN

DevHong 2022. 11. 20. 00:14

JOIN이란?

두 개의 연관이 있는 테이블을 연결하여 새로운 행 집합 즉, 새로운 테이블 형태의 데이터를 추출하는 기능

ANSI SQL이란?

모든 DB에 공용으로 사용할 수 있는 SQL 표준으로 테이블 조인관계는 FROM ~   ON ~ 에서 기술하고 WHERE절에서는 데이터를 필터링 하는 조건만 기술하므로 Oracle JOIN 방식보다 가독성이 좋고 SQL 표준 방식이기 때문에 Oracle에서 뿐만 아니라 모든 DB에서 사용이 가능하다.

※ Oracle 10g부터 ANSI JOIN 사용이 가능하다.

Oracle JOIN이란?

Oracle에서만 제공하는 JOIN방식으로 테이블 조인관계를 FROM절과 WHERE절에서 기술한다. 사용자마다 다르지만 웬만하면 ANSI SQL보다 쓰기 편하다.

※ Oracle 9i 까지는 Oracle JOIN만 사용 가능하다.

JOIN 종류

1. INNER JOIN : 내부 조인 (교집합)

2-1. LEFT OUTER JOIN : 왼쪽 외부 조인 (기준 테이블)

2-2. LEFT OUTER JOIN : 왼쪽 외부 조인 (차집합)

3-1. RIGHT OUTER JOIN : 오른쪽 외부 조인 (연결 테이블)

3-2. RIGHT OUTER JOIN : 오른쪽 외부 조인 (차집합)

4-1. FULL OUTER JOIN : 전체 외부 조인 (합집합)

4-2. FULL OUTER JOIN : 전체 외부 조인 (대칭 차집합)

※ Oracle JOIN에서는 FULL OUTER JOIN을 지원하지 않는다. 사용X

 

샘플 데이터

두 개의 테이블을 생성한다.

 

[mainCategory]

CREATE TABLE mainCategory(
    mainCategory_no      NUMBER           NOT NULL,
    mainCategory_name    VARCHAR2(100)    NOT NULL,
    PRIMARY KEY(mainCategory_no)
);

INSERT INTO mainCategory VALUES(10, 'FrontEnd');
INSERT INTO mainCategory VALUES(20, 'BackEnd');
INSERT INTO mainCategory VALUES(30, 'Database');

 

[subCategory]

CREATE TABLE subCategory(
    subCategory_no      NUMBER           NOT NULL,
    mainCategory_no     NUMBER,
    subCategory_name    VARCHAR2(100)    NOT NULL,
    PRIMARY KEY(subCategory_no)
)

INSERT INTO subCategory VALUES(1, 10, 'HTML');
INSERT INTO subCategory VALUES(2, 10, 'CSS');
INSERT INTO subCategory VALUES(3, 10, 'JavaScript');
INSERT INTO subCategory VALUES(4, 30, 'Oracle');
INSERT INTO subCategory VALUES(5, 30, 'MySQL');
INSERT INTO subCategory VALUES(6, 30, 'MongoDB');
INSERT INTO subCategory VALUES(7, NULL, 'Error');
INSERT INTO subCategory VALUES(8, NULL, 'QnA');

 

SELECT * FROM mainCategory

 

[결과]

mainCategory

 

SELECT * FROM subCategory

 

[결과]

subCategory

 

※ mainCategory가 BackEnd인 데이터가 없고 mainCategory가 null인 데이터도 있다.

 

1. INNER JOIN : 내부 조인 (교집합)

내부 조인은 두 테이블 중 공통적인 부분만을 가져온다.

 

INNER JOIN

 

ANSI JOIN

SELECT MC.mainCategory_name main, SC.subCategory_name sub
FROM mainCategory MC
INNER JOIN subCategory SC
ON MC.mainCategory_no = SC.mainCategory_no

FROM절에 기준이 되는 테이블을 지정하고 INNER JOIN절에 연결하려는 테이블을 지정한다.

두 테이블 간의 조인관계는 ON절에 지정한다.

 

[결과]

ANSI INNER JOIN

 

서브가 없는 메인인 BackEnd와 메인이 없는 서브 Error, QnA를 제외한 즉, 연관이 있는 데이터들만 가져오는 것을 볼 수 있다.

 

Oracle JOIN

SELECT MC.mainCategory_name main, SC.subCategory_name sub
FROM mainCategory MC, subCategory SC
WHERE MC.mainCategory_no = SC.mainCategory_no

FROM절 첫 번째 테이블(MC)은 기준이 되는 테이블, 두 번째 테이블(SC)은 연결하려는 테이블을 지정한다.

두 테이블 간의 조인관계는 WHERE절에 지정한다.

 

[결과]

Oracle INNER JOIN

 

결과는 ANSI INNER JOIN과 동일하다.

 

2-1. LEFT OUTER JOIN : 왼쪽 외부 조인 (기준 테이블)

왼쪽 외부 조인은 기준이 되는 테이블 전부와 연결된 테이블의 연관이 있는 데이터들만 가지고 온다.

 

LEFT OUTER JOIN

 

ANSI JOIN

SELECT MC.mainCategory_name main, SC.subCategory_name sub
FROM mainCategory MC
LEFT OUTER JOIN subCategory SC
ON MC.mainCategory_no = SC.mainCategory_no

JOIN부분을 LEFT OUTER JOIN으로 명시하고 두 테이블 간의 조인관계는 ON절에 지정한다.

 

[결과]

ANSI LEFT OUTER JOIN

 

메인카테고리의 모든 데이터가 나온 것을 볼 수 있다.

서브카테고리에는 BackEnd 데이터가 없기 때문에 null 형태로 나온다.

 

Oracle JOIN

SELECT MC.mainCategory_name main, SC.subCategory_name sub
FROM mainCategory MC, subCategory SC
WHERE MC.mainCategory_no = SC.mainCategory_no(+)

 

Oracle LEFT OUTER JOIN도 마찬가지로 WHERE문에 조인관계를 지정한다.

전체 데이터를 가져오는 테이블이 아닌 공통된 부분만 가져오는 테이블에 (+)를 지정해준다.

즉, 기준이 되는 테이블이 아닌 연결하려는 테이블(왼쪽 테이블)에 (+)를 붙여주면 된다.

 

[결과]

Oracle LEFT OUTER JOIN

 

2-2. LEFT OUTER JOIN : 왼쪽 외부 조인 (차집합)

차집합 데이터 즉, 연결 테이블과 연관이 없는 기준 테이블의 데이터를 가져오고 싶을 때에는 연결 테이블의 null값만을 가져오는 조건을 걸어주면 된다. 메인카테고리에서 서브카테고리가 null인 값은 연관이 없는 데이터이기 때문이다.

 

LEFT OUTER JOIN

 

ANSI JOIN

SELECT MC.mainCategory_name main, SC.subCategory_name sub
FROM mainCategory MC
LEFT OUTER JOIN subCategory SC
ON MC.mainCategory_no = SC.mainCategory_no
WHERE SC.mainCategory_no IS NULL

ANSI JOIN은 조인관계와 조건이 ON과 WHERE로 나뉘어 있어 Oracle JOIN보다 가독성 면에서는 좋다.

 

[결과]

ANSI LEFT OUTER JOIN

 

서브카테고리에는 아예 없는 BackEnd 데이터만 가져온 것을 볼 수 있다.

 

Oracle JOIN

SELECT MC.mainCategory_name main, SC.subCategory_name sub
FROM mainCategory MC, subCategory SC
WHERE MC.mainCategory_no = SC.mainCategory_no(+)
      AND SC.mainCategory_no IS NULL

Oracle JOIN은 조건을 줄 때 WHERE절에 AND를 사용해 추가해 주어야한다.

이렇듯 조인관계와 조건의 기준이 애매해져 ANSI JOIN보다 가독성 면에서는 안 좋은 것을 볼 수 있다.

 

[결과]

Oracle LEFT OUTER JOIN

 

결과는 ANSI JOIN과 같다.

3-1. RIGHT OUTER JOIN : 오른쪽 외부 조인 (연결 테이블)

오른쪽 외부 조인은 연결 테이블의 데이터 전체와 연관된 기준 테이블의 데이터를 가져온다.

 

RIGHT OUTER JOIN

 

ANSI JOIN

SELECT MC.mainCategory_name main, SC.subCategory_name sub
FROM mainCategory MC
RIGHT OUTER JOIN subCategory SC
ON MC.mainCategory_no = SC.mainCategory_no

JOIN 부분을 RIGHT OUTER JOIN으로 명시해주면 된다.

 

[결과]

ANSI RIGHT OUTER JOIN

 

메인카테고리가 없는 Error와 QnA까지 가져온 것을 볼 수 있다.

 

Oracle JOIN

SELECT MC.mainCategory_name main, SC.subCategory_name sub
FROM mainCategory MC, subCategory SC
WHERE MC.mainCategory_no(+) = SC.mainCategory_no

Oracle JOIN의 RIGHT OUTER JOIN은 왼쪽 테이블 즉, 기준 테이블에 (+)를 붙여주면 된다.

 

[결과]

Oracle RIGHT OUTER JOIN

 

결과는 ANSI RIGHT OUTER JOIN과 동일하다.

 

3-2. RIGHT OUTER JOIN : 오른쪽 외부 테이블 (차집합)

왼쪽 내부 조인 (차집합)과 비슷하다

 

RIGHT OUTER JOIN

 

ANSI JOIN

SELECT MC.mainCategory_name main, SC.subCategory_name sub
FROM mainCategory MC
RIGHT OUTER JOIN subCategory SC
ON MC.mainCategory_no = SC.mainCategory_no
WHERE MC.mainCategory_no IS NULL

메인카테고리의 데이터가 null인 값만 가져오는 조건을 명시해주면 된다.

 

[결과]

ANSI RIGHT OUTER JOIN

 

메인카테고리와 연관성이 없는 서브카테고리의 데이터들만 가지고 온 것을 볼 수 있다.

 

Oracle JOIN

SELECT MC.mainCategory_name main, SC.subCategory_name sub
FROM mainCategory MC, subCategory SC
WHERE MC.mainCategory_no(+) = SC.mainCategory_no
      AND MC.mainCategory_no IS NULL

메인카테고리의 데이터가 null인 값만 가져오는 조건을 AND를 사용해 추가해주면 된다.

 

[결과]

Oracle RIGHT OUTER JOIN

 

4-1. FULL OUTER JOIN : 전체 외부 조인 (합칩합)

전체 외부 조인은 ANSI JOIN만 사용할 수 있다. Oracle JOIN은 현재 버전(19c)에서는 지원하지 않는다.

 

FULL OUTER JOIN

 

ANSI JOIN

SELECT MC.mainCategory_name main, SC.subCategory_name sub
FROM mainCategory MC
FULL OUTER JOIN subCategory SC
ON MC.mainCategory_no = SC.mainCategory_no

JOIN부분을 FULL OUTER JOIN으로 명시해주면 된다.

 

[결과]

ANSI FULL OUTER JOIN

 

메인카테고리와 서브카테고리의 연관된 데이터와 연관되지 않은 데이터들 전부 가져온 것을 볼 수 있다.

 

Oracle JOIN

SELECT MC.mainCategory_name main, SC.subCategory_name sub
FROM mainCategory MC, subCategory SC
WHERE MC.mainCategory_no(+) = SC.mainCategory_no(+)

만약 Oracle JOIN으로 실행하면 오류가 반환되는 것을 볼 수 있다.

 

[결과]

Oracle FULL OUTER JOIN

 

'outer-join된 테이블은 1개만 지정할 수 있습니다' 라는 오류 문구를 볼 수 있다.

 

4-2. FULL OUTER JOIN : 전체 외부 조인 (대칭 차집합)

대칭 차집합은 합집합에서 교집합을 뺀 연관이 없는 데이터들을 의미한다.

 

FULL OUTER JOIN

 

ANSI JOIN

SELECT MC.mainCategory_name main, SC.subCategory_name sub
FROM mainCategory MC
FULL OUTER JOIN subCategory SC
ON MC.mainCategory_no = SC.mainCategory_no
WHERE MC.mainCategory_no IS NULL
   OR SC.mainCategory_no IS NULL

메인카테고리의 데이터가 null인 컬럼 또는 서브카테고리의 데이터가 null인 컬럼만을 가져오는 조건을 명시해주면 된다.

 

[결과]

ANSI FULL OUTER JOIN

 

연관이 없는 데이터들만 가져온 것을 볼 수 있다.

 

Oracle JOIN

SELECT MC.mainCategory_name main, SC.subCategory_name sub
FROM mainCategory MC, subCategory SC
WHERE MC.mainCategory_no(+) = SC.mainCategory_no(+)
    AND (
        MC.mainCategory_no IS NULL
     OR SC.mainCategory_no IS NULL
    )

Oracle JOIN에서 FULL OUTER JOIN은 마찬가지로 오류를 반환할 것이다.

 

[결과]

Oracle FULL OUTER JOIN

 

마무리

JOIN은 SQL의 꽃이다.

'Database > Oracle' 카테고리의 다른 글

[Oracle] Oracle LISTAGG 함수 (List Aggregate)  (0) 2022.11.18
[Oracle] Analytic Functions 3 (분석 · 집계 함수)  (0) 2022.11.18
[Oracle] Analytic Functions 2 (분석 · 집계 함수)  (0) 2022.11.17
[Oracle] Analytic Functions 1 (분석 · 집계 함수)  (0) 2022.11.17
[Oracle] Oracle 계층형 쿼리(Hierarchical Query) - START WITH, CONNECT BY  (0) 2022.11.09

'Database/Oracle' Related Articles

티스토리툴바