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
from A, (select b.col1 from B where a.col1 = b.col) => X
where a.col1 = b.col1
반면 서브쿼리는 종속적 관계가 나타난다고 했다.
쿼리의 결과는 1 : N 관계에도 언제나 1 이다.
종속적 관계라서 서브쿼리에 A 의 컬럼을 사용할수 있다.
select a.col1
from A
where exists (select '1' from B where a.col1 = b.col1 )
스칼라서브쿼리도 동일한 성질을 갖는다. ( 결과row 가 변하지 않는다, 종속적 관계이다.)
# 서브쿼리 역할 : 확인 or 공급 1. 확인 : 데이터 증가 X. 감소만. 2. 공급 : 주쿼리로 공급
# 선/후 수행 서브쿼리 - 선수행 서브쿼리 : 서브쿼리 테이블이 driving, 모든 값에 엑세스. unique 정렬 수행. 메모리에 임시 집합 구성. - 후수행 서브쿼리 : driving 에서 조건에 만족하는 한건의 데이터에 대해 서브쿼리를 한번 수행. 만족하면 결과로 추출. 주 쿼리의 데이터 건수 만큼 반복 수행.
# 서브쿼리 실행계획 제어
1. NL join -> Hash join
select colC, sum(colD) from tab a where colA in (select col from tab b where colB = 'X') and colB between '20190101' and '20191010' group by colC; =>
1-1) 힌트
select /*+ USE_HASH(a,@SQ) */ colC, sum(colD) from tab a where colA in (select /*+ QB_NAME(SQ) */ col from tab b where colB = 'X') and colB between '20190101' and '20191010' group by colC;
1-2) 인덱스 사용 못하게
select colC, sum(colD) from tab a where trim(colA) in (select trim(col) from tab b where colB = 'X') and colB between '20190101' and '20191010' group by colC;
1-3) from 절에 풀기
select /*+ ORDERED USE_HASH(A,B) */ a.colC, sum(a.colD) from tab a, tab b where a.colA = b.col and b.colB = 'X' and a.colB between '20190101' and '20191010' group by a.colC;
## 메인쿼리 : 서브쿼리 = 1:M 이면 데이터가 증가. => 인라인 뷰로 1:1 관계
select /*+ USE_HASH(A,B) */ a.colC, sum(a.colD) from tab a, ( select col from tab b where b.colB='X' group by col ) where a.colA = b.col and a.colB between '20190101' and '20191010' group by a.colC;
2. 조인 순서 변경
- QB_NAME 힌트 => 선수행 서브쿼리
select /*+ PUSH_SUBQ(@SQ) */ colC, sum(colD) from tab a where colA in (select /*+ QB_NAME(SQ) */ col from tab b where colB = 'X') and colB between '20190101' and '20191010' group by colC;
- 일반 조인으로 변경
select /*+ ORDERED USE_HASH(A,B) */ a.colC, sum(a.colD) from tab a, ( select col from tab b where b.colB='X' group by col ) where a.colA = b.col and a.colB between '20190101' and '20191010' group by a.colC;