PLT 5.7 Join

PLT 5.7 Join

하나 이상의 테이블로부터 자료를 검색하기 위하여 조인을 사용합니다. 일반적으로Primary Key(이후 PK로 사용)와 Foreign Key(이후 FK로 사용)을 사용하여 Join하는 경우가 대부분이지만 때로는 논리적인 값들의 연관으로 Join하는 경우도 있습니다.

 

SELECT table1.column1 [,table2.column2, . . . . .] FROM  table1, table2   WHERE table1.column1 = table2.column2;

l         WHERE 절에 조인 조건을 기술한다.

l         테이블을 조인하는 SELECT 문장을 작성할 경우 명확성을 위하여 또는 데이터베이스의 Performance 향상을 위하여 열 이름 앞에 테이블 명을 붙인다.

l         똑 같은 열 이름이 존재하는 테이블이 있을 경우는 반드시 열 이름 앞에 테이블 명을 붙인다.

l         n개의 테이블을 조인 하려면 최소한 n-1번의 조인 조건 문이 필요하다.

 

Join의 종류

Join 방법	설    명
Cartesian Product	모든 가능한 행들의 Join
Equijoin	Join조건이 정확히 일치하는 경우 사용(일반적으로PK와 FK사용)
Non-Equijoin	Join조건이 정확히 일치하지 않는 경우에 사용(등급,학점)
Outer Join	Join조건이 정확히 일치하지 않는 경우에도 모든 행들을 출력
Self Join	하나의 테이블에서 행들을 Join하고자 할 경우에 사용
Set Operators	여러 개의 SELECT문장을 연결하여 작성한다.

 

Cartesian Product

l        모든 가능한 행들의 Join으로 다음과 같은 경우에 발생한다.

l        조인 조건이 생략된 경우

l        조인 조건이 잘못된 경우

l        첫 번째 테이블의 모든 행이 두 번째 테이블의 모든 행과 두 번째 테이블의 모든 행이 첫 번째 테이블의 모든 행과 조인되는 경우.

l        양쪽 ROW의 개수를 곱한 결과

 

Cartesian Product는 많은 수의 행을 생성하는 경향이 있고 결과도 거의 유용하지 못하다. 그러므로 모든 테이블로부터 모든 행을 조합할 필요가 없을 경우 WHERE절에 조인 조건을 명확히 기술하여야 한다.

 

SQL> SELECt empno,ename,job,dept.deptno,dname,loc   2  FROM dept,emp   3  ORDER BY empno;

 

Equijoin

Equijoin 이란 조인 조건에서 “=”을 사용하여 값들이 정확하게 일치하는 경우에 사용하는 조인을 말합니다. 대부분 PK와 FK의 관계를 이용하여 조인 합니다. Equijoin은 다른 말로 단순 조인 또는 내부 조인 이라고도 합니다.

 

SELECT table1.column1 [,table2.column2, . . . . .]   FROM  table1, table2   WHERE table1.column1 = table2.column2;

table1.column1 조회할 자료가 있는 테이블과 열 이름을 기술

table1.column1=table2.column2 두 테이블들간에 논리적으로 연결하는 조인 조건 기술

 

l         SELECT절은 검색할 열 이름을 명시

l         FROM절은 데이터베이스가 Access해야 하는 두 개의 테이블을 명시

l         WHERE절은 테이블의 조인 조건을 명시

l         양쪽 테이블에 공통으로 존재하는 열 이름은 모호함을 피하기 위하여 열 이름 앞에 테이블명을 기술함

 

Equijoin 의 방법

종업원의 부서 이름을 결정하기 위해 EMP Table의 DEPTNO와 DEPT Table의 DEPTNO와 값을 비교하여야 합니다. EMP Table과 DEPT Table 사이의 관계는 양쪽 테이블의 DEPTNO열이 같아야 합니다. 이들이 PK와 FK로 연결되어 있습니다.

 

 

 

예) EMP 테이블에서 사원번호,이름,업무,EMP 테이블의 부서번호,DEPT 테이블의 부서번호,부서명,근무지를 출력하여라.

SQL> SELECT empno,ename,job,emp.deptno,dept.deptno,dname,loc   2  FROM dept,emp   3  WHERE dept.deptno = emp.deptno   4  ORDER BY dept.deptno;

 

EMPNO	ENAME	JOB	DEPTNO	DEPTNO	DNAME	LOC
7782	CLARK	MANAGER	10	10	ACCOUNTING	NEW YORK
7839	KING	PRESIDENT	10	10	ACCOUNTING	NEW YORK
7934	MILLER	CLERK	10	10	ACCOUNTING	NEW YORK
7369	SMITH	CLERK	20	20	RESEARCH	DALLAS
7876	ADAMS	CLERK	20	20	RESEARCH	DALLAS
7902	FORD	ANALYST	20	20	RESEARCH	DALLAS
7788	SCOTT	ANALYST	20	20	RESEARCH	DALLAS
7566	JONES	MANAGER	20	20	RESEARCH	DALLAS
7499	ALLEN	SALESMAN	30	30	SALES	CHICAGO
7698	BLAKE	MANAGER	30	30	SALES	CHICAGO
7654	MARTIN	SALESMAN	30	30	SALES	CHICAGO
7900	JAMES	CLERK	30	30	SALES	CHICAGO
7844	TURNER	SALESMAN	30	30	SALES	CHICAGO
7521	WARD	SALESMAN	30	30	SALES	CHICAGO

 

Table에 Alias사용

l        테이블 별칭을 사용하여 긴 테이블 명을 간단하게 사용한다.

l        테이블 이름 대신에 Alias를 사용한다.

l        SQL 코드를 적게 사용하여 코딩 시간이 절약되고 메모리를 보다 적게 사용한다.

 

SQL> SELECT e.empno,e.ename,e.job,e.deptno,   2  d.deptno,d.dname,d.loc   3  FROM dept d,emp e   4  WHERE d.deptno = e.deptno 5  ORDER BY d.deptno;

l        테이블 Alias는 30자까지 사용 가능하지만 짧을수록 더 좋다.

l        FROM절에서 Alias가 사용되면 SELECT문 전체에서 사용 가능하다.

l        테이블의 Alias에 가급적 의미를 부여

l        테이블은 현재 SELECT문장에서만 유용

 

AND연산자를 사용하여 추가적인 검색 조건

조인 이외의 WHERE절에 추가적인 조건을 가질 수 있다.

 

SQL> SELECT e.empno,e.ename,e.sal,d.dname,d.loc   2  FROM dept d,emp e   3  WHERE d.deptno = e.deptno AND e.job = 'SALESMAN';

 

두개 이상의 테이블 조인

때로는 두 개 이상의 테이블을 조인 할 경우가 있다.

 

예) 고객의 TKB SPORT SHOP의 이름, 주문처, 항목 수,각 항목의 합계,각 주문의 합계를 출력하여라.

 

SQL> SELECT name,custid   2  FROM customer;   NAME              CUSTID ----------------          ------- JOCKSPORTS          100 TKB SPORT SHOP      101 VOLLYRITE           102 . . . . . . . . .     9 rows selected.	SQL> SELECT ordid,itemid   2  FROM item;       ORDID    ITEMID ---------         ---------      610         3      611         1      612         1 . . . . . . . . . . 64 rows selected.	SQL> SELECT custid,ordid    2  FROM ord;      CUSTID     ORDID ---------          ---------       101       610       102       611       104       612 . . . . . . . . . . 21 rows selected.
SQL> SELECT c.name,o.ordid,i.itemid,i.itemtot,o.total   2  FROM customer c,ord o,item i   3  WHERE c.custid = o.custid AND o.ordid = i.ordid   4  AND c.name = 'TKB SPORT SHOP';   NAME                     ORDID    ITEMID   ITEMTOT     TOTAL -------------------- --------- --------- --------- --------- TKB SPORT SHOP             610         3        58     101.4 TKB SPORT SHOP             610         1        35     101.4 TKB SPORT SHOP             610         2       8.4     101.4

 

Non-Equijoin

EMP와 SALGRADE 사이의 관련성은 EMP 테이블의 어떠한 column도 직접적으로 SALGRADE 테이블의 한 column에 상응하지 않기 때문에 Non-Equijoin이다. 두 테이블 사이의 관련성은 EMP 테이블의 SAL열이 SALGRADE 테이블의 LOSAL과 HISAL열 사이에 있다는 것이다. 조인 조건은 등등(=) 이외의 연산자(BETWEEN ~ AND ~)를 갖는다.

 

예) EMP 테이블에서 사원번호,이름,업무,급여,급여의 등급,하한값,상한값을 출력하여라.

SQL> SELECT e.empno,e.ename,e.job,e.sal,s.grade,s.losal,s.hisal   2  FROM salgrade s,emp e   3  WHERE e.sal BETWEEN s.losal AND s.hisal AND e.deptno = 10;

 

위 질의가 실행될 때 한번만 조인되는 것을 알 수 있다. 이에 대한 두 가지 이유가 있다.

①       SALGRADE 테이블에서 중복되는 등급을 포함하는 행이 없다.

②       EMP 테이블에 있는 SAL의 값은 SALGRADE 테이블에서 제공하는 값 범위에 있다.

 

<= 및 >= 같은 다른 연산자를 사용 가능하나 BETWEEN이 가장 단순하다. 또한 테이블에 Alias를 사용하였는데 이는 모호성 때문이 아니라 성능 때문에 사용하였다. BETWEEN 사용시 하한 값을 먼저 명시하고 상한 값을 나중에 명시한다는 것을 명심하라.

 

Outer Join

행이 조인 조건을 만족하지 않으면, 행은 질의 결과에 나타나지 않을 것입니다. 예를 들어  EMP와DEPT테이블의 equijoin조건에서 부서OPERATIONS(40번 부서)는 해당 부서에 아무도 없기 때문에 나타나지 않습니다. 이런 경우 모든 행을 전부 출력하고자 할 경우 Outer Join을 사용한다. 즉 정상적으로 조인 조건을 만족하지 못하는 행들을 보기 위해 Outer join을 사용한다.

 

l        Outer join 연산자를 조인 조건에 사용시 조인 조건을 만족하지 않는 행들도 결과에 나타날 수 있다.

l        연산자는 괄호로 묶인 플러스 기호(+)이며 조인 시킬 값이 없는 조인 측에 "(+)"를 위치 시킨다.

l        (+)연산자는 한 개 이상의 NULL 행을 생성하고 정보가 충분한 테이블의 한 개 이상의 행들이 이런 NULL 행에 조인된다.

l        Outer join 연산자는 표현식의 한 편에만 올 수 있다.

l        Outer join을 포함하는 조건은IN 연산자, OR 연산자를  사용하여 다른 하나의 조건에 연결될 수 없다.

 

SELECT table1.column1 [,table2.column2, . . . . .] FROM table1, table2   WHERE table1.column1 = table2.column2(+);   SELECT table1.column1 [,table2.column2, . . . . .] FROM table1, table2   WHERE table1.column1(+) = table2.column2;

 

table1.column : 테이블을 함께 조인(또는 관련)시키는 조건입니다.

table2.column(+) : (+)는 outer join기호입니다. WHERE절 조건의 양쪽이 아니라 어느 한쪽에 둘 수 있습니다. 즉 양측 모두에는 올 수 없습니다. 일치하는 행이 없는 테이블의 열 이름 뒤에 outer join연산자를 사용합니다.

 

Outer Join제약 사항

①       Outer Join연산자는 정보가 부재하는 쪽의 표현식 한 쪽에만 둡니다. 다른 테이블의 어떠한 열과도 직접적으로 일치하는 것이 없는 한 테이블의 행을 리턴합니다.

②       Outer Join을 포함하는 조건은 IN연산자를 사용할 수 없고, OR연산자에 의해 다른 조건과 연결될 수 없습니다.

 

예) EMP 테이블과 DEPT 테이블에서 DEPT 테이블에 있는 모든 자료를 사원번호,이름,업무,EMP 테이블의 부서번호,DEPT 테이블의 부서번호,부서명,근무지를 출력하여라

SQL> SELECT e.empno,e.ename,e.job,e.deptno,   2  d.deptno,d.dname,d.loc   3  FROM dept d,emp e   4  WHERE d.deptno = e.deptno(+);

 

Self Join

때때로 자체적으로 테이블을 조인할 필요가 있습니다. 각 종업원의 관리자 명을 알기 위해서 자체적으로 EMP테이블을 조인하는 것이 필요합니다.

 

l         Self join을 사용하여 한 테이블의 행들을 같은 테이블의 행들과 조인한다.

l         같은 테이블에 대해 두 개의 alias를 작성(테이블 구분)함으로 FROM절에 두개의 테이블을 사용하는 것과 같이 한다.

l         Column에 대해서도 어떤 테이블에서 왔는지 반드시 Alias명을 기술하여야 한다.

l         테이블 하나를 두개 또는 그 이상으로 Self join할 수 있다.

 

예) EMP 테이블에서 Self join하여 관리자를 출력하여라.

SQL> SELECT worker.ename || '의 관리자는 ' || manager.ename || '이다'   2  FROM emp worker, emp manager   3  WHERE worker.mgr = manager.empno;

 

Set Operators

하나 이상의 테이블로부터 자료를 검색하는 또 다른 방법은 SET연산자를 이용하는 방법이 있다. 즉 SET연산자를 이용하여 여러 개의 SELECT문장을 연결하여 작성할 수 있다.

 

SELECT * | column1[, column2, column3, . . . . ] FROM table1 . . . . . . . . .   SET operator   SELECT * | column1[, column2, column3, . . . . ] FROM table2 . . . . . . . . .   [ORDER BY column | expression];

l         첫 번째 SELECT 구문에서 기술된 열과 두 번째 SELECT 구문에서 기술된 열들은 좌측부터 1대1 대응하며 그 개수와 타입이 일치해야 한다.

l         FROM절 뒤에 기술되는 테이블은 같을 수도 있고 다를 수도 있다.

l         출력되는 HARDING을 첫 번째 SELECT구문에서 기술된 열이 출력된다.

l         ORDER BY는 단 한번만 기술 가능하고 SELECT 구문의 마지막에 기술한다.

l         SELECT문장은 위에서 아래로 수행되고 이를 변경하고자 할 경우는 괄호를 사용한다.

 

SET 연산자의 종류

종 류	설    명
UNION	각 결과의 합(합집합:중복되는 값은 한번 출력)
UNION ALL	각 결과의 합(합집합:중복되는 값이 여러 번 출력될 수 있음.)
INTERSET	각 결과의 중복되는 부분만 출력(교집합)
MINUS	첫 번째 결과에서 두 번째 결과를 뺌(차집합)

 

UNION과 UNION ALL

SQL> SELECT deptno 2  FROM dept 3  UNION 4  SELECT deptno 5  FROM emp;   SQL> SELECT deptno 2  FROM dept 3  UNION ALL 4  SELECT deptno 5  FROM emp;

 

INTERSECT 연산자

양쪽에서 검색된 자료만 출력한다.

SQL> SELECT deptno 2  FROM dept 3  INTERSECT 4  SELECT deptno 5  FROM emp;

 

MINUS 연산자

두 번째 SELECT문장에서 검색되지 않았던 값을 첫 번째 SELECT문장에서 출력한다. 즉 첫 번째 SELECT문장에서 두 번째 SELECT문장에의 값을 뺀 것을 출력한다.

SQL> SELECT deptno 2  FROM dept 3  MINUS 4  SELECT deptno 5  FROM emp;

[출처] PLT 5.7 Join|작성자 후루꾸