2장_1절. 표준 조인(Standard Join)

1. Standard Sql 개요

-1970 E.F Codd 관계형 RDBMS 논문발표

-1974 IBM SQL 개발

-1980 Sysbase SQL Server 발표

-1983 IBM DB2 발표

-1986 ANSI/ISO SQL 표준 최초 제정(SQL1)

-1992 ANSI/ISO SQL 표준 제정(SQL2)

-1993 MS SQL Server 발표

-1999 ANSI/ISO SQL 표준 제정(SQL3)

-2003 ANSI/ISO SQL 표준 제정(SQL2003)

-2008 ANSI/ISO SQL 표준 제정(SQL2008)

#E.F Codd의 논문에 언급된 8가지 관계형 대수 = 4개의 일반 집합연산자 + 4개의 순수 관계 연산자

가. 일반 집합 연산자

1.합집합 연산(Union) : 교집합의 중복을 제거하는 연산 // 나중에 Union All 기능 추가(중복도 보여줌)

2.교집합 연산(Intersection) : 교집합 추출

3.차집합 연산(Difference) : 차집합 추출

4.곱집합 연산(Product) : cartesian 연산, M*N건의 데이터 조합 발생

나. 순수 관계 연산자

5. Select 연산(행선택) : Where절로 구현

6. Project 연산(열선택) : Select절의 칼럼선택 기능으로 구현

7. (Natural) Join 연산 : 여러가지 Join 기능으로 구현(Natural Join, Inner Join,Outer Join, Using,On)

8. Divide 연산(나눗셈연산) : 현재 사용되지 않음.

#RDBMS의 경우 데이터 정합성과 데이터 저장공간의 절약을 위해 엔터티를 최대한 분리하는 정규화 과정을 거치는데 일반적으로 3차 정규형이나 보이스코드 정규형가지 진행한다. 이런 정규화 과정을 거치면 하나의 주제에 관련된 엔터티가 여러개로 나누어지고, 이렇게 흩어진 데이터를 연결해서 원하는 데이터를 가져오는 작업이 바로 Join작업니다.

#따라서 RDBMS에 있어서 Join은 SQL의 가장 중요한 기능이다

2.From 절 Join 형태

=>ANSI / ISO SQL에서 규정한 join문법은 where절을 사용하던 기존 join방식과 차이가 있다. 새로운 join방식중 가장 중요하게 기억해야 하는 것은 On 조건절이다. 

=>과거에는 where절에서 join조건과 select연산(데이터 검증 조건)이 같이 사용되어 구분되지 않았다. 따라서  where 절의 join조건을 from절의 on조건절로 분리하여 표시함으로써 가독성을 높였다.

(1)inner join : where절에서 사용하던 join의 default 옵션임. join조건에서 동일한 값이 있는 행만 반환. cross join, outer join과 같이 사용할 수 없다.

(2)natural join : inner join의 하위개념이다. 두 테이블 간의 동일한 이름을 갖는 !!!모든 칼럼들!!!에 대해 Equi join을 수행한다???? natural inner join으로도 표시할 수 있다.

ex)테이블1의 컬럼(player_name, team_id, height, weight)

테이블2의 컬럼(height, weight, team_name, stadium_id)라면

두 테이블간의 동일한 이름의 모든 컬럼은 height, weight이므로 height와 weight가 일치하는 행들만 출력함.

(3)using 조건절 : natural join처럼 사용하되 Equi join을 수행할 칼럼을 선택할 수 있다.

(4)on 조건절 : join서술부(on절)와 비 join서술부(where절)를 구분할 수 있음. 칼럼명이 다르더라도 join조건을 사용할 수 있음

(5)cross join : M * N건의 데이터를 조합함

(6)outer join(left outer / right outer / full outer) : inner join과 대비해 outer join으로 부른다. Equi join을 수행하되 동일한 값이 없는 행은 NULL을 넣어 출력한다. 

left outer는 왼쪽 테이블을 모두출력, 오른쪽테이블에 NULL을 입력하고, 

right outer는 오른쪽 테이블을 모두출력, 왼쪽 테이블에 NULL을 입력한다. 

full outer는 left outer를 수행한 결과left outer와 right outer를 수행한 두 테이블의 합집합의 결과와 같다.

3. Inner Join

->inner join은 join조건에서 동일한 값이 있는 행만 반환한다. inner join 표시는 그 동안 where 절에서 사용하던 join 조건을 from절에서 정의하겠다는 뜻이므로, from~on조건절 이나 from~using조건절을 반드시 사용해야 에러가 안남.

ex)emp테이블과 dept테이블에서 사원번호가 일치하는 모든 행의

사원 번호, 사원 이름, 소속부서코드, 소속부서이름을 출력해라

1)과거방식 : where절에 join조건 정의

SQL> select emp.deptno, empno, ename, dname

  2  from emp, dept

  3  where emp.deptno = dept.deptno;

2)ANSI / ISO SQL표준방식 : from절에 join조건정의

SQL> select emp.deptno, empno, ename, dname

  2  from emp inner join dept

  3  on emp.deptno = dept.deptno;

3)inner는 join의 디폴트 옵션이므로 생략가능하다.

SQL> select emp.deptno, empno, ename, dname

  2  from emp join dept

  3  on emp.deptno = dept.deptno;

4. Natural Join

->natural join은 두 테이블간 동일한 이름을 갖는 !!모든 칼럼들!!에 대해 EQUI(=) join을 수행한다. natural join이 명시되면 추가로 using조건, on조건, where절에서 join조건을 정의할 수 없다.(why??? : 모든 칼럼을 이미 선택했으므로 사용자가 join할 행을 따로 추가하거나 제거할 수 없음)

->SQL Server에서는 지원안한다

ex)emp테이블과 dept테이블에서 사원번호, 사원이름, 소속부서코드, 소속부서이름이 모두 일치하는 사원 번호, 사원 이름, 소속부서코드, 소속부서이름을 출력해라.

1)올바른 사용

SQL> select empno, ename, deptno, dname

  2  from emp natural join dept;

2)잘못된 사용 : natural join은 alias나 테이블명과같은 접두사를 붙일 수 없다(why? 모든 열을 비교하므로 접두사 사용할 필요X),따라서 다른테이블의 동일한 칼럼명을 사용한다면, 동일한 데이터 유형을 사용해야 한다.

SQL> select empno, ename, emp.deptno, dname

  2  from emp natural join dept;

select empno, ename, emp.deptno, dname

ERROR at line 1:

ORA-25155: column used in NATURAL join cannot have qualifier

#접두사를 붙일 수는 없지만, 테이블명의 alias는 사용가능

SQL> select deptno, empno, ename, dname

  2  from dept d natural join emp e;

3)natural join은 와일드카드(*)사용시 칼럼순서를 지정하지 않으면, join의 기준이 되는 칼럼들이 먼저 하나로 출력된다. inner join은 테이블순서대로 칼럼들이 중복 출력된다.

ex1)join의 기준이 되는 deptno칼럼 출력 -> emp테이블 출력 -> dept테이블 출력

SQL> select *

  2  from emp natural join dept;<-emp를 dept에 자연조인하면 emp의 칼럼부터 나온다

ex2)join의 기준이 되는 deptno칼럼 출력 -> dept테이블 출력 -> emp테이블 출력 

SQL> select *

  2  from dept natural join emp;<-dept를 emp에 자연조인하면 dept의 칼럼부터 나온다

ex3)inner join은 칼럼의 순서대로 출력한다(join 칼럼은 중복됨) : emp테이블출력 -> dept테이블출력

SQL> select *

  2  from emp inner join dept

  3  on emp.deptno = dept.deptno;

ex4)inner join은 칼럼의 순서대로 출력한다(join 칼럼은 중복됨) : dept테이블출력 -> emp테이블출력

SQL> select *

  2  from dept inner join emp

  3  on emp.deptno = dept.deptno;

5. Using 조건절

->natural 조건절은 모든 일치되는 칼럼들에 대해 join이 이루어지지만, from절에 using조건절을 사용하면 같은 이름을 가진 칼럼중 원하는 칼럼을 선택하여 Euqi Join을 할수 있다.

ex)세 개의 칼럼명이 모두 같은 dept와 dept_temp 테이블을 deptno칼럼을 이용한 inner join의 using조건절로 수행한다.

1)using조건절 사용 : using조건절 사용시 조건절에 이용된 컬럼(deptno)이 먼저 출력된다

SQL> select *

  2  from dept inner join dept_temp

  3  using(deptno);

2)잘못된 사용 : using조건절도 natural join과 마찬가지로 alias나 테이블 이름을 접두사로 붙일수 없다. why? ???using사용시에 칼럼을 이미 알고있어서??

SQL> select dept.deptno

  2  from dept join dept_temp

  3  using (deptno);

select dept.deptno

       *

ERROR at line 1:

ORA-25154: column part of USING clause cannot have qualifier(식별자를 가질 수 없다)

3)using 2개이상 칼럼 선택 : 선택된 순서대로 출력(deptno->loc)되고 선택되지 않은 게 중복출력됨(dept테이블의 dname->dept_temp테이블의 dname)

SQL> select *

  2  from dept inner join dept_temp

  3  using(deptno,loc);

6. On 조건절 

->기존의 where조건에서 수행하던 join연산을 select연산(행조건구분)과 분리하기 위해서 from~on조건절을 추가하여 join서술부(on조건절)와 join비서술부(where조건절)를 구분하기 쉬워졌다(가독성)

->on조건절에서는 alias나 테이블명등 접두사를 명확하게 지정해야 오류가 안난다.

->using조건절에서는 alias나 테이블명등을 접두사로 쓰면 오류가 난다.

ex)사원 테이블과 부서테이블에서 사원번호,사원이름, 부서코드,부서이름을 출력

SQL> select e.empno, e.ename, e.deptno, d.dname

  2  from emp e join dept d

  3  on e.deptno = d.deptno;

가. where절과의 혼용

ex)부서코드30인 부서의 사원이름, 부서코드, 부서코드, 부서이름을 출력하라

방법1)where절에 join조건 서술

SQL> select e.ename, e.deptno, d.deptno, d.dname

  2  from emp e inner join dept d

  3  on e.deptno = d.deptno

  4  where e.deptno = 30;

방법2)on절에 join조건 서술

SQL> select e.ename, e.deptno, d.deptno, d.dname

  2  from emp e inner join dept d

  3  on e.deptno = d.deptno

  4  and e.deptno = 30;

나. on조건절 + 데이터 검증 조건추가 : 검색조건은 where에 사용하는것이 속도빠르다. 다만 outer join의 경우 join의 대상을 제한하기 위한 목적의 경우에는 on절에 검색조건을 추가하여야한다.

ex)사원번호가 7698인 사원들의 이름, 부서코드, 부서이름을 출력한다.

방법1)on조건절에 데이터검증조건추가

SQL> select e.ename, e.mgr, d.deptno, d.dname

  2  from emp e join dept d

  3  on e.deptno = d.deptno

  4  and e.mgr = 7698;

방법2)where조건절에 데이터검증조건추가

SQL> select e.ename, e.mgr, d.deptno, d.dname

  2  from emp e join dept d

  3  on e.deptno = d.deptno

  4  where e.mgr = 7698;

다. on조건절과 using조건절 natural조건절의 차이

ex)스타디움테이블과 팀테이블에서 팀id가 같은 팀이름, 팀id, 스타디움이름을 출력하라

스타디움테이블에는 팀id가 hometeam_id로 되있고, 팀테이블에는 팀id가 team_id로 되있다.

방법1)on조건절 사용

SQL> select team_name, team_id, stadium_name

  2  from team join stadium

  3  on team.team_id = stadium.hometeam_id;

방법2)using조건절 사용 : 두 테이블의 컬럼명이 다르므로 사용할 수 없다.

SQL> select team_name, team_id, stadium_name

  2  from team join stadium

  3  using(team_id);

using(team_id)

      *

ERROR at line 3:

ORA-00904: "STADIUM"."TEAM_ID": invalid identifier

방법3)natural조건절 사용 : 공통된 컬럼이 없으므로 아무것도 출력되지 않는다.

SQL> select team_name, team_id, stadium_name

  2  from team natural join stadium;

 

라. on 조건절을 사용한 다중 테이블 join

->1번째 join~on절을 사용한 결과테이블에 2번째 join~on절을 사용한다

ex)사원, dept테이블, dept_temp테이블의 바뀐 부서명 정보를 출력하라

방법1) on절에 inner join 2번사용 : 출력순서가 empno로 정렬??why??

SQL> select e.empno, d.deptno, d.dname, t.dname New_dname

  2  from emp e inner join dept d

  3  on e.deptno = d.deptno

  4  inner join dept_temp t

  5  on d.deptno = t.deptno;

방법2)where절에 and를 사용하여 join조건 2번 사용 : 출력순서가 deptno로 정렬??why??

SQL> select e.empno, d.deptno, d.dname, t.dname New_dname

  2  from emp e, dept d, dept_temp t

  3  where e.deptno = d.deptno

  4  and e.deptno = t.deptno;

7.Cross Join

->E.F Codd박사가 언급한 곱집합 개념, 테이블간 join조건이 없는 경우 생길수 있는 모든 M * N건의 데이터 조합.

->where절에 join조건을 추가 가능하다. 그러나 inner join과 같은결과를 얻으므로 쓸필요가 없다

ex)사원번호사원이름, 부서이름을 출력

SQL> select ename, dname

  2  from emp cross join dept;

8.Outer Join 

->inner join과 대비하여 outer(외부) join이라하며, join조건에서 동일한 값이 없을때 NULL을 추가하여 행을 출력한다.

->outer join 역시 join조건을 from절에서 사용하겠다는 표시이므로 using이나 on조건절을 필수적으로 사용해야한다(안하면 오류남. 문법오류)

(1)Stadium 테이블 : 스타디움이 없는 팀이 있다.(부산,일산,마산,안양)

가. left outer join

SQL> select stadium_name, stadium.stadium_id, seat_count, hometeam_id, team_name

  2  from stadium left outer join team

  3  on stadium.hometeam_id = team.team_id;

outer를 생략가능하다.

SQL> select stadium_name, stadium.stadium_id, seat_count, hometeam_id, team_name

  2  from stadium left join team <-outer생략

  3  on stadium.hometeam_id = team.team_id;

나.right outer join

SQL> select stadium_name, stadium.stadium_id, seat_count, hometeam_id, team_name

  2  from stadium right outer join team

  3  on stadium.hometeam_id = team.team_id;

outer를 생략가능하다.

SQL> select stadium_name, stadium.stadium_id, seat_count, hometeam_id, team_name

  2  from stadium right join team<- outer생략

  3  on stadium.hometeam_id = team.team_id;

=>stadium테이블에는 홈구장이 없는 구단도 나오기때문에 left outer join의 경우 모든 구단을 출력하지만, right outer join의 경우 team테이블 기준이므로 홈구장이 없는 구단은 출력되지 않는다.

다.full outer join

->left outer join과 right outer join의 합집합과 같은 결과이다.

(1)dept테이블과 dept_temp테이블

ex1)dept를 기준으로 full outer join

->공통된거출력(1,2행)

->left join출력(5,6행)

->right join출력(3,4행) 

SQL> select *

  2  from dept full outer join dept_temp

  3  on dept.deptno = dept_temp.deptno;

ex2)dept_temp를 기준으로 full outer join

->공통된거출력(3,4행)

->left join출력(1,2행)

->right join출력(5,6행) 

SQL> select *

  2  from dept_temp full outer join dept

  3  on dept.deptno = dept_temp.deptno;

9. INNER vs OUTER vs CROSS JOIN 비교

• INNER JOIN 결과
• 양쪽 테이블 모두에 존재하는 값인 ​B - B, C - C 2건 출력
• LEFT OUTER JOIN 결과
• 왼쪽 테이블인 TAB1을 기준으로 B - B, C - C, D - NULL, E - NULL 4건 출력
• RIGHT OUTER JOIN 결과
• 오른쪽 테이블인 TAB2를 기준으로 NULL - A, B - B, C - C 3건 출력
• FULL OUTER JOIN 결과
• 양쪽 테이블을 기준으로 NULL - A, B - B, C - C, D - NULL, E - NULL 5건 출력
• CROSS JOIN 결과
• JOIN 가능한 모든 경우를 표시하므로(단, OUTER JOIN은 제외) 양쪽 테이블 TAB1과 TAB2의 데이터를 곱한 개수인 4 * 3 = 12건이 출력된다.
• B - A, B - B, B - C, C - A, C - B, C - C, D - A, D - B, D - C, E - A, E - B, E - C 12건 출력