아우터 조인은 별거 없으면서도 개발자들이 종종 실수 하는 쿼리 중 하나이다.
아우터 조인의 개념과 활용을 한번 정리해 보자.
- 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
도움이 되셨다면 "공감" 마구 눌러주세요
'생계 > SQL' 카테고리의 다른 글
| [oracle]프로시져 out 파라미터 호출하기 (0) | 2022.07.13 |
|---|---|
| sql 튜닝 트레이스 (0) | 2019.10.21 |
| 서브쿼리에 대해서 알아보자 (0) | 2019.06.06 |
