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
1. 용어 종류 : 업무/비표준전문/표준 용어 - 업무용어 : 화면등 label 용. 길이 x - 비표준전문 : 전문처리용. 길이포함. - 표준용어 : 테이블 컬럼용.
2. 단어 1) 단일어
한음절 단어는 되도록 표준에서 제외하는 것이 좋다. ex) 회식 + 비(x) , 회식 + 비용(O)
예외적으로 허용해야하는 경우는 복합어로 만드는 것이 낫다. ex) 회식 + 비(x) , 회식비(O)
2) 복합어 - 합성어 - 파생어
단어 + 단어 로 구성된 단어가 각각의 단어에서 의미가 유추되지 않으면 복합어로 구성이 낫다
3) 기타 관용어, 외래어 금칙어/유사어 동음이의/ 이음동의
3. 도메인, 인포타입 ( 코드/번호/그룹)
- 도메인 도메인은 속성이 가질 수 있는 값의 범위 특정 데이터 필드에 허용되는 값의 집합을 정의
- 인포타입 인포타입은 도메인에 대한 구체적인 데이터 타입과 길이를 지정한 것 ex) 일자 도메인 : Varchar(8) 또는 Date 인포타입을 선택 계좌 번호 속성: 'VC12' (12자리 가변 문자열) 인포타입을 지정
4. 코드 - 코드란 무엇인가 : 속성의 값을 기호로 변환한 것 - 필요성 : 프로그램 소스코드 수준에서 로직분기, 데이터를 유형화하여 조회, 짧게 압축 => 가독성, 저장공간 효율성. 본질은 분류, 범주화의 도구 - 코드인 것과 아닌 것 (상품코드 vs 상품번호) - 코드 종류 : 공통코드 : 시스템 전반에 걸쳐 공통적으로 사용되는 코드 일반적으로 공통코드 테이블에서 통합 관리 코드와 코드값만으로 서비스가 가능한 경우에 사용 개별코드 : 특정 업무 영역이나 테이블에서 개별적으로 정의되고 관리되는 코드 코드와 코드값 외에 추가적인 정보가 필요한 경우 사용 외부코드 : 외부코드는 외부 기관이나 표준에 의해 정의되어 사용되는 코드 시스템 간 호환성을 위해 외부에서 정의된 코드를 그대로 사용
