[oracle] 아우터 조인 outer join 제대로 이해하기

 아우터 조인은 별거 없으면서도 개발자들이 종종 실수 하는 쿼리 중 하나이다.

 아우터 조인의 개념과 활용을 한번 정리해 보자. 

 

• 1. 아우터 조인의 개념 

 

   일반 조인은 집합 A 와 B 가 있을 때 양쪽 집합 모두 조인 조건을 만족하는 rows 만 리턴한다. 

 

 

 

   반면, 아웃터 조인은 A 가 기준이라면 A 의 모든 rows 에 대하여

   집합 B 가 조인 조건에 만족하면 나오고 그렇지 않으면 null 을 리턴한다. 

 

   이때 A 와 B 의 관계가 1:1 인 경우, 결과 rows 는 A 의 갯수와 동일하지만 1:N 인 경우는 결과는 N 이 된다.  

 

 

• 2. 멀티컬럼 아우터 조인 

 

    아우터 조인 조건이 여러개인 것은 하나인 것과 다르지 않다. 

    즉, 조인조건이 '조건1' and '조건2' and '조건3' 이라면 조건을 모두 만족하는 rows 만 결과를 리턴한다. 

    나머지는 null 을 리턴한다. 

 

-- out multi col

 

 

-- multi col outer join 
drop table ot_test1;
drop table ot_test2;

create table ot_test1(
col1 int,
col2 varchar2(1),
col3 varchar2(1),
col4 varchar2(1)
);

create table ot_test2(
col1 int,
col2 varchar2(1),
col3 varchar2(1),
col4 varchar2(1)
);

insert into ot_test1 values('1','a','A','');
insert into ot_test1 values('1','a','B','!');
insert into ot_test1 values('1','b','A','@');
insert into ot_test1 values('2','a','A','#');
insert into ot_test1 values('3','a','A','');

insert into ot_test2 values('1','a','A','G');
insert into ot_test2 values('1','a','A','X');
insert into ot_test2 values('1','b','A','F');

 

select a.col1 a1, a.col2 a2, a.col3 a3, b.col4
  from ot_test1 a,
       ot_test2 b
where 1=1
and a.col1 = b.col1(+)
and a.col2 = b.col2(+)
and a.col3 = b.col3(+)
;

 

 

 

 

 

 

집합 A 에 5 개 rows 를 넣었고 집합 B 에는 3 개의 rows 를 넣었다.

아우터의 기준이 되는 A 의 5 개는 모두 나온다.

col1=1 col2=a col3=A 인 조인조건을 만족하는 B 집합이 2개가 모두 리턴된다.   

 

 

## 아우터 조인은 1:1 관계 에서는 스칼라 서브쿼리로 변경 가능하다. 

 

select a.col1 a1, a.col2 a2, a.col3 a3,
      (select b.col4
         from ot_test2 b
        where 1=1
          and a.col1 = b.col1
          and a.col2 = b.col2
          and a.col3 = b.col3
      ) b4
 from ot_test1 a
order by a1, a2, a3
;
=> ORA-001427

 

1:1 관계가 아닌 아우터 조인을 스칼라 서브쿼리로 변경하면 ORA-001427 가 뜬다.

 

이때 min, max 등의 그룹함수를 사용하면 에러를 회피할 수 있다.

 

select a.col1 a1, a.col2 a2, a.col3 a3,
      (select min(b.col4) 
         from ot_test2 b
         where 1=1
        and a.col1 = b.col1
        and a.col2 = b.col2
        and a.col3 = b.col3
      ) b4
  from ot_test1 a
order by a1, a2, a3
;
=> 스칼라서브쿼리에서 2개 이상의 행이 리턴되는 것을 막기 위해 min 함수를 썼다.

 

• 3. 연속 아우터 조인

 

중첩해서 아우터 조인을 사용할 경우도 있다.

 

이럴때는 어디에 (+) 를 표시해야 할까.

 

-- consecutive out join

drop table out_a;
drop table out_b;
drop table out_c;

create table out_a(
col1 int,
col2 varchar2(1)
);

create table out_b(
col1 int,
col2 varchar2(1)
);

create table out_c(
col1 int,
col2 varchar2(1)
);

insert into out_a values(1,'a');
insert into out_a values(2,'b');
insert into out_a values(3,'G');
insert into out_a values(4,'');

insert into out_b values(1,'a');
insert into out_b values(3,'');
insert into out_b values(5,'');
insert into out_b values(6,'d');

insert into out_c values(1,'a');
insert into out_c values(2,'a');
insert into out_c values(3,'c');
insert into out_c values(3,'f');
insert into out_c values(6,'a');

 

 

-- 올바른 중첩 아우터 조인 쿼리 

 

SELECT *
FROM OUT_A A
     ,OUT_B B
     ,OUT_C C
WHERE A.COL1 = B.COL1(+)
AND B.COL1 = C.COL1(+)
ORDER BY A.COL1
;

 

 

 

-- 잘못 작성된 아우터 조인 

 

SELECT *
  FROM OUT_A A
      ,OUT_B B
      ,OUT_C C
 WHERE A.COL1 = B.COL1(+)
   AND B.COL1 = C.COL1
ORDER BY A.COL1
;

 

 

 

 이것의 결과는  일반조인의 결과와 같다. 

 

-- 일반적인 조인  

 

SELECT *
  FROM OUT_A A
      ,OUT_B B
      ,OUT_C C
 WHERE A.COL1 = B.COL1
   AND B.COL1 = C.COL1
ORDER BY A.COL1
;

 

 

 

## A = B(+)  and B(+) = C 이런 아우터 조인은 가능할까?

 

 -- 이런 아우터 조인이 될까 

SELECT *
  FROM OUT_A A
      ,OUT_B B
      ,OUT_C C
 WHERE A.COL1 = B.COL1(+)
   AND B.COL1 (+) = C.COL1
ORDER BY A.COL1
=> 이 결과는 A 와 B 의 아우터 조인 집합과 C의 카티션 곱이다. 

 

•  4. Full outer join  

오라클은 아우터 조인되는 집합쪽에 (+) 기호를 넣는다. 

그렇다면 양쪽 아우터 조인은 양쪽 집합에 (+) 를 넣으면 될것 같은데 그렇게 하면 에러가 난다. 

 

full outer join 은 ansi 방식으로 작성하거나 왼쪽 아우터 조인 오른쪽 아우터 조인을 union 하는 방식으로 쓸수 있다. 

 

 

--  FULL OUTER JOIN

drop table out_a;
drop table out_b;

create table out_a(
col1 int,
col2 varchar2(1)
);

create table out_b(
col1 int,
col2 varchar2(1)
);

insert into out_a values(1,'a');
insert into out_a values(3,'c');
insert into out_a values(5,'');

insert into out_b values(1,'a');
insert into out_b values(4,'%');
insert into out_b values(5,'$');

 

-- 일반조인

SELECT A.COL1, B.COL1, A.COL2, B.COL2
  FROM out_a A,
       out_b B
 WHERE A.COL1 = B.COL1
;

 

 

 

 

-- 맞지않는 문법

 
SELECT A.COL1, B.COL1, A.COL2, B.COL2
  FROM out_a A,
       out_b B
 WHERE A.COL1(+) = B.COL1(+)
;
=> ORA-01468

 

 

 

-- ansi 

SELECT A.COL1, B.COL1, A.COL2, B.COL2
  FROM out_a A full outer join out_b B
    ON A.COL1 = B.COL1
ORDER BY a.COL1
;

 

 

 

 

 

 

 

 

 

 

-- union

SELECT A.COL1, B.COL1, A.COL2, B.COL2
  FROM out_a A,
     out_b B
 WHERE A.COL1 = B.COL1(+)
UNION
SELECT A.COL1, B.COL1, A.COL2, B.COL2
  FROM out_a A,
       out_b B
 WHERE A.COL1(+) = B.COL1
;

 

 

 

 

•  5. Lateral outer join  

 Lateral 구문은 오라클에서 좀 더 유연하게 아우터 조인을 사용하는 것을 지원한다.

 오라클 아우터 조인은 조인 조건에 in 이나 or 가 올 경우 에러가 난다. 

SELECT A.EMPNO, B.DNAME
  FROM EMP A,
             DEPT B
 WHERE A.DEPTNO = B.DEPTNO(+) OR A.DEPTNO = B.DEPTNO2(+)

 => 에러 

 위의 SQL 은 아래와 같이 or 조건을 union 으로 분리해서 작성해야 정상 동작한다. 

SELECT A.EMPNO, B.DNAME
  FROM EMP A,
    DEPT B
 WHERE A.DEPTNO = B.DEPTNO(+) 
UNION 
SELECT A.EMPNO, B.DNAME
  FROM EMP A,
    DEPT B
 WHERE A.DEPTNO = B.DEPTNO2(+)

해당 SQL은 lateral 을 사용하면 좀 더 간결하게 작성할수 있다. 

SELECT A.EMPNO, B.DNAME
  FROM EMP A 
           , LATERAL (
                     SELECT B.DNAME
                         FROM DEPT B
                     WHERE A.DEPTNO = B.DEPTNO OR A.DEPTNO = B.DEPTNO2 
    ) (+)B

 

 

 

도움이 되셨다면 "공감" 마구 눌러주세요