[DBP] CH1-1. Join

join

• 하나 이상의 테이블을 연결하여 데이터를 검색하는 방법
  • 보통 두 개 이상의 행들의 공통된 값(기본키 및 외래키 값)을 사용하여 조인 실행
• ex)

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

Cartesian Product (카티션 곱)

• 모든 가능한 행들의 조인으로, 검색하고자 했던 데이터뿐 아니라 조인에 사용된 테이블들의 모든 데이터 반환
• 발생하는 경우
  • 조인 조건을 정의하지 않았을 경우 (Where 절 x)
  • 조인 조건이 잘못된 경우
  • 첫번째 테이블의 모든 행들이 두번째 테이블의 모든 행과 조인이 되는 경우

사원 번호와 사원 이름, 업무, 소속 부서명 출력

• ex1) 조인 조건 명시 X

    SELECT empno, ename, job, dname
    FROM emp, dept;
  

  

  • emp, dept 테이블의 모든 행 출력

• ex2) 조인 조건 명시 O

    SELECT empno, ename, job, dname
    FROM emp e, dept d
    WHERE e.empno > d.deptno;
  

  

  • empno는 7000번대, deptno는 10번대로 모든 튜플들이 조건 만족 -> 모든 행 출력

• Cartesian Product
  = Cross Join
  = Cross Product

    SELECT empno, ename, job, dname
    FROM emp, dept;
  
    SELECT empno, ename, job, dname
    FROM emp cross join dept;
  

Equi Join (동등 조인, 내부 조인)

• 조인 조건에서 Equality Condition(=)을 사용하여 값들이 정확하게 일치하는 경우에 사용되는 조인
• 대부분 기본키와 외래키 관계 이용

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

• ex) 급여가 2000 이상인 사원에 대해 사원 번호, 사원 이름, 업무, 부서명, 위치 검색

    SELECT empno, ename, job, dname, loc
    FROM emp, dept
    WHERE emp.deptno = dept.deptno 
    and sal >= 2000;
  

    SELECT empno, ename, job, dname, loc
    FROM emp inner join dept
    ON emp.deptno = dept.deptno 
    WHERE sal >= 2000;
  

    SELECT empno, ename, job, dname, loc
    FROM emp inner join dept
    USING (deptno)   
    WHERE sal >= 2000;
  

  EMPNO	ENAME	JOB	DNAME	LOC
  7782	CLARK	MANAGER	ACCOUNTING	NEW YORK
  7839	KING	PRESIDENT	ACCOUNTING	NEW YORK
  7566	JONES	MANAGER	RESEARCH	DALLAS
  7902	FORD	ANALYST	RESEARCH	DALLAS
  7788	SCOTT	ANALYST	RESEARCH	DALLAS
  7698	BLAKE	MANAGER	SALES	CHICAGO

• emp.deptno : 외래키, dept.deptno : 기본키
• USING : 속성 이름이 같은 경우에만 사용 가능
  • () 없으면 에러
  • 속성명에 테이블명 명시 X
• ON 조건절을 사용한 JOIN의 경우 ALIAS나 테이블 명과 같은 접두사를 사용하여 SELECT 에 사용되는 칼럼을 명확하게 지정해주어야 함

• 다중 테이블 JOIN

SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME New_DNAME 
FROM EMP E 
JOIN DEPT D 
ON (E.DEPTNO = D.DEPTNO) 
JOIN DEPT_TEMP T 
ON (E.DEPTNO = T.DEPTNO);

• 테이블 별칭 (table alias) 이용
  • SELECT 문에서 유용
  • FROM 절에 별칭이 사용되면 SELECT 문 전체에서 사용 가능

      SELECT empno, job, loc
      FROM emp e, dept d
      WHERE e.deptno = d.deptno;
    

Non-Equijoin

• 테이블의 어떤 컬럼도 조인할 테이블의 칼럼에 일치하지 않는 경우에 사용
• 조인 조건은 동등(=) 이외의 연산자 사용
  • BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN 등
  • NOT 연산자, 크기 비교, NULL 등

• ex) 급여가 3000과 4000 사이의 사원 이름과 부서명 출력

    SELECT e.ename, d.dname
    FROM emp e, dept d
    WHERE e.sal BETWEEN 3000 AND 4000;
  

  ENAME	DNAME
  SCOTT	ACCOUNTING
  FORD	ACCOUNTING
  SCOTT	RESEARCH
  FORD	RESEARCH
  SCOTT	SALES
  FORD	SALES
  SCOTT	OPERATIONS
  FORD	OPERATIONS

  • 문제점) 카티션 곱에서 WHERE 조건 적용 -> 가짜 튜플 생성!
  • 이 예제의 경우 부서 번호를 비교하지 않아 WHERE 조건에 맞는 사원이 모든 부서에 연결되어 출력

Self Join

• 자체적으로 테이블 조인하는 경우
• ex) 각 사원의 관리자 출력 -> 둘 다 emp 테이블에 존재

    SELECT e1.empno, e1.ename, e2.empno, e2.ename
    FROM emp e1, emp e2
    WHERE e1.mgr = e2.empno;
  

  EMPNO	ENAME	MGRNO	MGRNAME
  7788	SCOTT	7566	JONES
  7902	FORD	7566	JONES
  7499	ALLEN	7698	BLAKE
  7521	WARD	7698	BLAKE
  7900	JAMES	7698	BLAKE
  7844	TURNER	7698	BLAKE
  7654	MARTIN	7698	BLAKE
  7934	MILLER	7782	CLARK
  7876	ADAMS	7788	SCOTT
  7566	JONES	7839	KING
  7782	CLARK	7839	KING
  7698	BLAKE	7839	KING
  7369	SMITH	7902	FORD

Outer Join (외부 조인)

• 정상적으로 조인 조건을 만족하지 못하는 행들을 보기 위해 사용
• Equi Join은 조인하는 테이블의 두 개의 컬럼에서 공통된 값이 없다면 행을 반환하지 않음
• 조인시킬 값이 없는 조인 측에 (+)
• Outer Join 연산자는 표현식의 한 편에만 사용 가능 (왼 / 오 / full)

-- 왼쪽 Outer Join
SELECT table.column1 [, table.column2, ...]
FROM table1, table2
WHERE table1.column1 = table2.column2(+);

-- 오른쪽 Outer Join
SELECT table.column1 [, table.column2, ...]
FROM table1, table2
WHERE table1.column1(+) = table2.column2;

• ex1) 일반 조인

    SELECT DISTINCT(a.deptno), b.deptno
    FROM emp a, dept b
    WHERE a.deptno, b.deptno;
  

  DEPTNO	DEPTNO
  10	10
  20	20
  30	30

  • 내부 조인 -> 조건에 해당되는 행들만 출력

• ex2) Outer Join

    SELECT DISTINCT(a.deptno), b.deptno
    FROM emp a, dept b
    WHERE a.deptno(+) = b.deptno;
  
    -- 같은 표현
    SELECT a.deptno, b.deptno
    FROM emp a RIGHT OUTER JOIN dept b
    ON a.deptno = b.deptno;
  
    SELECT deptno, deptno
    FROM emp a RIGHT OUTER JOIN dept b
    USING <deptno>;
  

  DEPTNO	DEPTNO
  10	10
  20	20
  30	30
  	40

  • 오른쪽 조인 -> 오른쪽 테이블(dept table)의 모든 행 출력

• ex3) full outer join은 양쪽에 (+)가 아니라, FULL OUTER JOIN 표현식 이용

    SELECT deptno, deptno
    FROM emp a FULL OUTER JOIN dept b
    ON a.deptno = b.deptno;
  
    SELECT deptno, deptno
    FROM emp FULL OUTER JOIN dept
    USING <deptno>;