Oracle 기초 : 실전(6) Join - ANSI Standard, Oracle Standard (update: 2020/06/17)

codePark·2020년 6월 15일
oracle
0

Oracle

목록 보기
8/23

Introduction: Joins

table, relation, join:

entity(개체), relation(테이블의 집합, 특히 RDBMS에서*) RDBMS에서 테이블들은 어떤 식으로든 테이블간 관계를 가질 수 밖에 없고 이를 기반으로 relation이라는 용어를 사용한다. 여기서 테이블의 합은 행과 행간의 조합(join), 또는 열과 열간의 조합인 union(연합) 이렇게 2가지가 존재한다.

이 중 두 table의 공통된 column 또는 값을 기준으로 row와 row를 합쳐 하나의 가상 테이블을 생성하는 것이 바로 join의 의미이다. 예를 들어 특정 사원의 부서명(dept_code)을 알고 싶은 경우: 해당 사원의 정보가 등록된 테이블과 부서명이 기록된 테이블이 다르다면 1. 먼저 해당 사원의 부서 코드를 employee table에서 찾아야 할 것이고 2. 조회된 부서 코드를 가지고 department table에서 해당 부서 코드의 부서명 조회를 하는 순서로 이루어져야 할 것이다.

즉 별개의 테이블을 검색해서 결과를 도출해야 하는 번거로움을 해결하기 위해 join을 사용할 수 있다.

Precondition/Remark of joins

join의 전제조건
1. Tab1과 Tab2의 Col1과 Col2의 값이 동일하거나 일부 포함관계에 있어야 한다.
2. 각 Col의 행의 수가 달라도 공통된 값이 있다면 join이 가능하다.
3. 이는 임의의 가상 테이블과 같이 작동한다.

join시 각 다른 Tab에서 기준 Col이 같은 경우
join한 상태에서 Column명이 같은 경우 어느 col이 어느 tab 소속인지 확인할 수 없기 때문에 반드시 Alias를 선언해야 하며, 선언하지 않는 경우 ORA_00918 "column ambiguously defined" 가 발생하게 된다. 마찬가지로 select 절에서 Alias과 Col을 함께 선언하지 않은 경우 또한 어느 Tab 소속의 Col을 조회하는 것인지 알 수 없기 때문에 같은 에러가 발생하게 된다. 즉, 경우 불문하고 join시에는 반드시 Alias를 명시적으로 선언해 주는 것이 좋다.

선언한 Alias의 활용
명시적으로 선언한 Alias는 select, where, from(join시)에 사용이 가능하다. 이 Alias를 선언할 때는 as, ""를 사용하지 않은 상태에서 하나의 단어로 선언하는 것이 권장된다.

using의 활용
ANSI 문법을 기준으로 기준 Column명이 동일한 경우에 using을 사용할 수 있다.

--syntax of using:
from Tab1 AliasTab1 join Tab2 AliasTab2
using(commonColName)

단, 이 경우에 using()이 인자로 받는 commonColName에는 TabAlias를 함께 선언할 수 없다.
(TabAlias와 함께 선언하는 경우 ORA-01748: only simple column names allowed here 발생.)

Difference between ANSI Std. and Oracle Std. (Syntax)

join과 on을 사용해서 join에 대입할 Table 및 그의 Alias를 선언하고, 선언 순서에 따라서 기준 테이블과 부차 테이블을 구분하는 ANSI Standard와 달리 Oracle Standard는 comma(,)와(+) 기호를 통헤 기준 Column과 부차 Column을 구분한다.

이 때 Oracle Standard에서 (+)기호를 붙인 Column을 부차 Column으로 선언하게 되며, (+)가 붙지 않은 기준 컬럼에 해당 Column을 붙인다는 의미가 된다. 따라서 Oracle Standard에서 Column의 선언 순서는 중요하지 않으며, 어떤 Column에 기호 (+)를 추가하여 기준 컬럼과 부가 컬럼을 구분하는지에 따라 결과값이 달라지게 된다.

또한 full outer join은 Oracle Standard에선 존재하지 않는다.

Outline: Types of join

Tab1과 Tab2가 각각 대응되지 않는 데이터 또한 갖고 있다고 할 때,

  1. 대응되는 모든 데이터만 반환하려면 inner-join
  2. Tab1의 모든 데이터 반환 및 Tab2의 미대응 데이터는 제외한다고 할 때 left-outer join
  3. Tab1의 모든 데이터 반환 및 Tab2의 미대응 데이터는 제외한다고 할 때 right-outer join
  4. 대응 관계 상관없이 모든 데이터를 반환 받고자 할 때 full-outer join을 사용하면 된다.

이 때, 대응되는 값이 없는 데이터 영역은 (null)로 공간만 채워진다.

Types of join


형식에 따른 구분

  • Equi-join: 동등 비교(=)를 통한 join
  • Non-Equi join: 동등 비교(=) 외의 방식을 통한 join

문법에 따른 구분

  • ANSI Standard: 모든 DBMS에서 사용 가능한 표준 문법. 키워드 join과 on을 사용.
  • Oracle Standard: Oracle DBMS에서만 사용 가능. comma(,)를 사용한 join을 지원.

기준 Table을 통한 구분

  • (inner) join
  • left (outer) join
  • right (outer) join
  • full (outer) join
  • Cross join (Cartesian Product/Cartesian join)

그 외의 경우

  • Self-join
  • join with multiple Columns

Syntax: inner join (ANSI Std./Oracle Std.)

ANSI Standard:
from Table1 Table1Alias join Table2 Table2Alias
on Tab1Alias.Col1 = Tab2Alias.Col2;
(add'l Condition) where Alias.Col = 'target'...

Oracle Standard:
from Table1 Table1Alias, Table2 Table2Alias
where Table1Alias.Col1 = Table2Alias.Col2;
(add'l Condition) and Alias.Col = 'target' ...

-- ANSI Std.
from employee E join department D
on E.dept_code = D.dept_id;

--Oracle Std.
from employee E, department D
where E.dept_code = D.dept_id;

가장 기본이 되는 join의 형태.
Table1과 Table2의 공통된 값/Column이 있을 때 이들의 교집합을 리턴한다.

Syntax: left outer join (ANSI Std./Oracle Std.)

ANSI Standard:
from Tab1 Tab1Alias left outer join Tab2 Tab2Alias
on Tab1Alias.Col1 = Tab2Alias.Col2

Oracle Standard:
from Tab1 Tab1Alias, Tab2 Tab2Alias
where Tab1Alias.Col1 = Tab2Alias.Col2(+)

-- ANSI Std.
from employee E left outer join department D
on E.dept_code = D.dept_id

--Oracle Std.
from employee E, department D
where E.dept_code = D.dept_id(+);

먼저 선언된 테이블(ANSI Std.) 또는 (+)기호가 붙지 않은 테이블(Oracle Std.)을 기준 테이블로 잡고 타 테이블과의 교집합 값을 결합한다.

Syntax: right outer join (ANSI Std./Oracle Std.)

ANSI Standard:
from Tab1 Tab1Alias right outer join Tab2 Tab2Alias
on Tab2Alias.Col2 = Tab1Alias.Col1

Oracle Standard:
from Tab1 Tab1Alias, Tab2 Tab2Alias
where Tab1Alias.Col1(+) = Tab2Alias.Col2

-- ANSI Std.
from employee E right outer join department D
on D.dept_id = E.dept_code;

--Oracle Std.
from employee E, department D
where E.dept_code(+) = D.dept_id;

left outer join과 마찬가지로 먼저 선언된 테이블(ANSI Std.) 또는 (+)기호가 붙지 않은 테이블(Oracle Std.)을 기준 테이블로 잡고 타 테이블과의 교집합 값을 결합한다.

Syntax: full outer join (ANSI Std.)

ANSI Standard:
from Tab1 Tab1Alias full outer join Tab2 Tab2Alias
on Tab2Alias.Col2 = Tab1Alias.Col1

-- ANSI Std.
from employee E full outer join department D
on D.dept_id = E.dept_code;

각 테이블에 매칭되지 않는 행을 포함하여 Table/Coumn 선언 순서와 관계 없이 join한 Table의 모든 데이터를 반환한다. (어차피 합집합의 값을 리턴할 것이므로)
또한 full outer join은 ANSI Standard에만 존재한다.

Syntax: Cross join/Cartesian Product (ANSI Std./Oracle Std.)

ANSI Standard:
from Tab1 Tab1Alias cross join Tab2 Tab2Alias

Oracle Standard:
from Tab1 Tab1Alias, Tab2 Tab2Alias

-- ANSI Std.
from employee E full outer join department D

--Oracle Std.
from employee E, department D

모든 경우의 수를 구하고, 결과행은 두 테이블 행의 수를 곱한 결과가 리턴된다. ANSI Standard, Oracle Standard 두 경우 모두 다른 join문들과 다르게 on(ANSI Std.) where(Oracle Std.)절에 조건을 대입하지 않는 것이 특징이다.

Syntax: Self-join (ANSI Std./Oracle Std.)

ANSI Standard:
from Tab1 Tab1Alias(1) join Tab1 Tab1Alias(2)
on Tab1Alias(1).Col1 = Tab1Alias(2)Col2

Oracle Standard:
from Tab1 Tab1Alias, Tab2 Tab2Alias
where Tab1Alias(1).Col1 = Tab1Alias(2)Col2

-- ANSI Std.
from employee E1 join employee E2
on E1.dept_code = E2.manager_id;

--Oracle Std.
from employee E1, employee E2
where E1.dept_code = E2.manager_id;

같은 Table내에서 공통된/대응되는 값이 있는 경우 작성한다.

Syntax: join with Multiple Columns (ANSI Std./Oracle Std.)

ANSI Standard:
from Tab1 Tab1Alias join Tab2 Tab2Alias
on Tab2Alias.Col2 = Tab1Alias.Col1
join Tab3 Tab3Alias
on Tab3ALias.Col3
join Tab4 Tab4Alias
on Tab4Alias.Col4
...

Oracle Standard:
from Tab1 Tab1Alias, Tab2 Tab2Alias, Tab3 Tab3Alias, Tab4 Tab4Alias
where Tab1Alias.Col1 = Tab2Alias.Col2
and Tab2Alias.Col2 = Tab3Alias.Col3
and Tab3Alias.Col3 = Tab4Alias.Col4
...

-- ANSI Std.
from employee E join department D
	 on D.dept_id = E.dept_code
	 join job J
     on J.job_code
     join nation N
     N.national_code
     ...;
     
--Oracle Std.
from employee E, department D, job J, nation N
where E.dept_code = D.dept_id
	  and D.job_num = J.job_code
	  and J.local_code = N.local_id
	  ...;

보다 많은 Table과 Column을 join한다. 반드시 모든 join을 inner-join하지 않아도 되나 (즉 join의 혼용이 가능하나) inner join이 아닌 left outer join/right outer join등을 선언하는 경우 마지막 Table/Column까지 같은 종류의 join을 유지해야 결과값의 누락이 없다.

Syntax: Non-Equi join (ANSI Std./Oracle Std.)

ANSI Standard:
from Tab1 Tab1Alias join Tab2 Tab2Alias
on Tab2Alias.Col2 between Tab1Alias.Col(1) and Tab1Alias.Col1(2)

Oracle Standard:
from Tab1 Tab1Alias, Tab2 Tab2Alias
on Tab2Alias.Col2 between Tab1Alias.Col(1) and Tab1Alias.Col1(2)

-- ANSI Std.
from employee E join salary S
on E.salary between S.min_sal and S.max_sal;

--Oracle Std.
from employee E,salary S
on E.salary between S.min_sal and S.max_sal;

동등 비교(=) 외의 방식을 통한 join으로, 위의 예시 코드에서 선언된 between and외에도 in, not in등의 비교조건문이 대입될 수 있다. 선언된 조건에 해당하는 Column이 있으면 그 값을 리턴한다.
단! non-equi join에서는 오직 inner join만이 허용된다.

profile
codePark
아! 응애에요!
이전 포스트

Oracle 기초 : 실전(5) group by/ having clauses, grouping+decode

다음 포스트

Oracle 기초 # 스피드 퀴즈: DB 기초 이론~Join

0개의 댓글