[Oracle] 오라클 JOIN

-

JOIN

JOIN이란?

  • 하나의 SQL 명령문에 의해 디스크의 여러개의 테이블에서 사용자가 요청한 데이터를 메모리로 복사해서 가지고 와 조회도 하고 변경도 할 수 있는 기능
  • 메모리는 작업을 하는 공간, 디스크는 저장을 하는 공간

JOIN의 종류

  • 카티션곱
  • INNER JOIN
  • OUTER JOIN
  • SELF JOIN

Cartesian Product(카티션 곱)

-- INNER JOIN
SELECT empno,ename,job,mgr,hiredate,sal,comm,e.deptno,dname,loc 
FROM emp e,dept d WHERE e.deptno=d.deptno 
ORDER BY ename;

-- 카디션 곱
SELECT empno,ename,job,mgr,hiredate,sal,comm,e.deptno,dname,loc 
FROM emp e,dept d 
ORDER BY ename;

INNER JOIN

  • 교집합
  • 다른 테이블에서 데이터를 가져와서 연결
  • NULL값일때 처리가 불가능
    • NULL값이 있는 경우에는 처리하지 않는다.
    • NULL값 처리를 위해서 기능을 확장한 것이 OUTER JOIN
  • 종류 : EQUI Join(등가 조인) , Non-Equi Join(비등가 조인)

EQUI_JOIN : 등가조인

  • 가장 많이 사용되는 기술
  • 연산자 = : 같은 값일때 가져오기
  • (예시) EMP테이블의 deptno와 DEPT테이블의 deptno가 같을 때, DEPT테이블의 loc, dept컬럼을 가져와라
    • DEPT테이블의 deptno는 Primary Key (기본키)
    • EMP테이블의 deptno는 Foreign Key (참조키)
  • 등가조인의 형식
  • (참고) 테이블 별칭
    • 테이블명이 길 때 사용
    • as 안붙여줘도 됨
table : emp,dept
SELECT 컬럼명....
FROM emp e, dept d;

오라클 조인 : 컬럼명이 다를 수도 있다 , 오라클에서만 사용하는 쿼리문장

SELECT empno,ename,job,mgr,hiredate,sal,comm,e.deptno,dname,loc FROM emp e,dept d WHERE e.deptno=d.deptno ORDER BY ename;

SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,dname,loc FROM emp e,dept d
                                             *
ERROR at line 1:
ORA-00918: column ambiguously defined

SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,dname,loc FROM emp e,dept d WHERE e.deptno=d.deptno
                                             *
ERROR at line 1:
ORA-00918: column ambiguously defined

-- 이름이 SCOTT인 사원의 이름, 입사일, 부서명, 근무지 출력
SELECT ename, hiredate, dname, loc 
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND ename='SCOTT';

ANSI 조인 : 컬럼명이 다를 수도 있다 , 전체 데이터베이스에서 사용하는 쿼리

  • JOIN 2번 걸기 : 오라클조인 형식처럼 AND사용 불가 , JOIN~ON을 여러번 사용해줘야함
SELECT empno,ename,job,mgr,hiredate,sal,comm,e.deptno,dname,loc FROM emp e JOIN dept d ON e.deptno=d.deptno;

-- 이름이 SCOTT인 사원의 이름, 입사일, 부서명, 근무지 출력
SELECT ename, hiredate, dname, loc 
FROM emp
JOIN dept 
ON emp.deptno=dept.deptno 
WHERE emp.ename='SCOTT';

NATURAL 조인 : 반드시 같은 컬럼명이 존재해야함

  • 별칭을 안줘도 됨
SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,dname,loc FROM emp NATURAL JOIN dept;

JOIN-USING : 반드시 같은 컬럼명이 존재해야함

SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,dname,loc 
FROM emp JOIN dept USING(deptno);

NON_EQUI_JOIN

  • EQUI_JOIN과의 차이점
    • EQUI_JOIN은 = (등위연산자)
    • NATURAL-JOIN과 JOIN~USING은 사용하지 못한다(컬럼명이 무조건 같아야하니까…?)
    • 연산자를 사용하되, =이 아닌 연산자를 주로 사용한다
    • 비교연산자 , BETWEEN~AND 연산자를 주로 사용한다.
-- emp 테이블의 sal컬럼이 salgrade테이블의 losal과 hisal사이에 있을때 join시켜라?
SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,grade 
FROM emp,salgrade 
WHERE sal BETWEEN losal AND hisal;

// ORACLE-JOIN
-- JOIN 2번 걸기(emp + dept + salgrade)
SELECT empno,ename,job,mgr,hiredate,sal,comm,emp.deptno,dname, loc,grade     // deptno에 별칭 꼭 작성해줘야함
FROM emp,dept, salgrade 
WHERE emp.deptno=dept.deptno  
AND sal BETWEEN losal AND hisal;  // 조건을 여러개 걸때는 AND로 연결

// ANSI-JOIN
-- JOIN 2번 걸기(emp + dept + salgrade)
SELECT empno,ename,job,mgr,hiredate,sal,comm,emp.deptno,dname, loc,grade 
FROM emp 
JOIN dept
ON emp.deptno=dept.deptno 
JOIN salgrade 
ON sal BETWEEN losal AND hisal;

OUTER JOIN

  • UNION과 유사, 합집합
  • 다른 테이블에서 데이터를 가져와서 연결
  • INNER JOIN값 + @ (NULL)
  • NULL값일때도 처리할 수 있음
  • (예시) 이름, 부서위치를 출력하는데 BOSTON도 출력되도록 하시오
    • 없는 쪽에 (+)붙이면 null값도 출력됨
    • emp.deptno에 없는데 dept.deptno에 있는 값을 가져와야하니까 Right에 (+)붙여주면 됨
-- Right OUTER JOIN
SELECT ename,loc FROM emp,dept WHERE emp.deptno(+)=dept.deptno;

OUTER JOIN의 종류 (NULL의 위치의 반대?)

1. LEFT OUTER JOIN : INTERSECT + MINUS

  • 오라클조인
SELECT A.col , B.col
FROM A , B
WHERE A.col=B.col(+)   // INNER JOIN +(A-B)
  • ANSI조인
SELECT A.col , B.col
JOIN A LEFT OUTER JOIN B
ON A.col=B.col   // A-B

2. RIGHT OUTER JOIN : INTERSECT + MINUS

  • 오라클조인
SELECT A.col , B.col
FROM A , B
WHERE A.col(+)=B.col   // INNER JOIN+(B-A)
  • ANSI조인
SELECT A.col , B.col
JOIN A RIGHT OUTER JOIN B
ON A.col=B.col   // A-B

3. FULL OUTER JOIN : UNION

  • ANSI조인
SELECT A.col , B.col
JOIN A FULL OUTER JOIN B
ON A.col=B.col   // A-B

INNER JOIN과 OUTER JOIN 연습

SQL> select * from A;

        NO TEXT
---------- --------------------
         1 aaa
         2 bbb
         3 ccc
         4 ddd

SQL> select * from B;

        NO TEXT
---------- --------------------
         2 eee
         3 fff
         4 ggg
         5 hhh
  • INNER JOIN : (A ∩ B) (2, 3, 4)
SELECT A.no,A.text,B.no,B.text
FROM A,B
WHERE A.no=B.no;
  • LEFT OUTER JOIN : (A-B) (1) + (A ∩ B) (2, 3, 4)
SELECT A.no,A.text,B.no,B.text
FROM A,B
WHERE A.no=B.no(+);
  • RIGHT OUTER JOIN : (A ∩ B) (2, 3, 4) + (B-A) (5)
SELECT A.no,A.text,B.no,B.text
FROM A,B
WHERE A.no(+)=B.no;
  • FULL OUTER JOIN : (A ∪ B) (1,2,3,4,5)
SELECT A.no,A.text,B.no,B.text
FROM A FULL OUTER JOIN B
ON A.no=B.no;
  • B집합이 A집합의 부분집합일때 OUTER JOIN의 결과는?
SELECT ename, job,hiredate,sal,dname,loc 
FROM emp,dept 
WHERE emp.deptno=dept.deptno;

SELECT ename, job,hiredate,sal,dname,loc 
FROM emp,dept 
WHERE emp.deptno=dept.deptno(+);

// 결과값 같음

SELF JOIN

  • 같은 테이블에서 데이터 연결
  • NULL값은 출력하지 않음
-- emp 테이블 내에서 사수가 같은 사람들의 이름을 출력하시오
empnomgrename
77887566SCOTT
empnomgrename
75667839JONES
// null값이 있는 것은 검색하지 못함

SELECT e1.ename "본인" , e2.ename "사수" 
FROM emp e1,emp e2    // 같은 테이블 사용할 경우 별칭 사용 필수!
WHERE e1.mgr=e2.empno;

// null값까지 검색하고 싶을 때 => OUTER JOIN 사용해야함

-- LEFT OUTER JOIN
SELECT e1.ename "본인" , e2.ename "사수" 
FROM emp e1,emp e2 
WHERE e1.mgr=e2.empno(+);

-- RIGHT OUTER JOIN
SELECT e1.ename "본인" , e2.ename "사수" 
FROM emp e1,emp e2 
WHERE e1.mgr(+)=e2.empno;

-- FULL OUTER JOIN
SELECT e1.ename "본인" , e2.ename "사수" 
FROM emp e1 FULL OUTER JOIN emp e2 
ON e1.mgr=e2.empno;




테이블 예시

emp : 사원정보
= empno : 사번
= ename : 이름
= job : 직위
= mgr : 수사번호
= hiredate : 입사일
= sal : 급여
= comm : 성과급
= deptno : 부서번호

dept : 부서정보
= deptno : 부서번호
= dname : 부서명
= loc : 근무지

SELECT empno, ename, job, mgr, hiredate, sal, comm, emp.dept, dname, loc 
FROM emp, dept
WHERE emp.dept = dept.dept

컬럼명이 같은 경우 어떤 테이블의 컬럼명인지 특정해줘야한다.



테이블 예시를 통한 JOIN 사용 형식 알아보기

--Oracle JOIN
SELECT empno, ename, job, mgr, hiredate, sal, comm, emp.deptno, dname, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno;

--Oracle JOIN 별칭사용 e, d
SELECT empno, ename, job, mgr, hiredate, sal, comm, e.deptno, dname, loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;

--ANSI JOIN
SELECT empno, ename, job, mgr, hiredate, sal, comm, e.deptno, dname, loc
FROM emp e JOIN dept d
ON e.deptno = d.deptno;

--NATURAL JOIN
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno, dname, loc 
FROM emp 
NATURAL JOIN dept;

--JOIN~USING
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno, dname, loc 
FROM emp 
JOIN dept USING(deptno);


















댓글

댓글 쓰기