📚Study Note/ORACLE
[ ORACLE ] JOIN(조인) Equi join Non Equi join CROSS/INNER/OUTER JOIN 부모자식테이블 SELF JOIN
칠칠라
2021. 4. 2. 12:03
---------------------------------------------------------------------------
--■■■ JOIN(조인) ■■■
--관계형 데이터베이스에서 큰테이블은 바람직하지않다.
--나누고분리한테이블 원래형태로보이게하려면 결합필요 그게조인이다
-- ●1. SQL 1992 CODE
-- ●2. SQL 1999 CODE (92년 99년 으로 표준된 코드 두 개로 나뉜다. 실무에서는 둘다쓰인다)
-- ●1. SQL 1992 CODE
SELECT *
FROM EMP,DEPT;
--> 수학에서 말하는 데카르트 곱(CATERSIAN PRODUCT)
-- 두 테이블을 합친(결합한) 모든 경우의 수
-- ●Equi join : 서로 정확히 일치하는 데이터들끼리 연결시키는 결합
SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
-- ●Non Equi join : <범위> 안에 적합한 데이터들끼리 연결시키는 결합
SELECT *
FROM SALGRADE;
SELECT *
FROM EMP ; --스미스는800
SELECT *
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
-- Equi Join 시 + 활용한 결합 방법
SELECT *
FROM TBL_EMP E, TBL_DEPT D
WHERE E.DEPTNO = D.DEPTNO;
--> 총 14건의 데이터가 결합되어 조회된 상황
SELECT *
FROM TBL_EMP;
--==>> 여기에는 19건의 데이터가 있는데?
-- 부서번호가 없는 5명이 결합에서 누락된 것이다. 즉 부서번호를 갖지 못한
-- 사원들은 모두 누락
--[복붙]
SELECT *
FROM TBL_EMP E, TBL_DEPT D
WHERE E.DEPTNO = D.DEPTNO(+) ; --(우선 19개를 나열하고 부서번호를 끼워넣기하는식이면 다볼수있음 덧셈연산자가하는역할이다)
--==>> 총 19건의 데이터가 결합되어 조회된 상황,
-- 즉, 부서번호를 갖지 못한 사원들도 모두 조회된 상황
SELECT *
FROM TBL_EMP E, TBL_DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO ;
--> 총 16건의 데이터가 결합되어 조회된 상황
-- 즉, 부서에 소속된 사원이 아무도 없는 부서도 모두 조회된 상황.
-- +가 눈에 띄기는 하지만 그 반대가 주인공이다. + 붙어있는 쪽이 거들어주는 상황 주의!
-- ※ (+) 가 없는 쪽 테이블의 데이터를 모두 메모리에 적재한 후
-- (+) 가 있는 쪽 테이블의 데이터를 하나하나 확인하여 결합시키는 형태로
-- JOIN 이 이루어진다.
SELECT *
FROM TBL_EMP E, TBL_DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO(+) ;
--> 위와 같은 이유로 이러한 형식의 JOIN 구문은 존재하지 않는다.
--불편한점? 을 보완하기 위해 1999 코드가 등장
--조인하는방법 92 에서는 WHERE 절에다가 하는데 이것이 결합조건인지 테이블 내 선택조건인지 애매모호
-- ●2. SQL 199 CODE (92년 99년 으로 표준된 코드 두 개로 나뉜다. 실무에서는 둘다쓰인다)
--『JOIN 이라는 키워드가 등장, 이를 활용해 조인의 유형을 명시하게 됐다.
-- 결합조건은 WHERE 대신에 ON 으로 바뀜
-- ●CROSS JOIN
SELECT *
FROM EMP,DEPT;
--이게 아니라
SELECT *
FROM EMP CROSS JOIN DEPT;
--> 이렇게 바뀌었다. 위와 결과는 같다.
EQUI 와 NON EQUI 조인이라는 표현을 안쓰게 됐고
--●1. INNER JOIN
--●2. OUTER JOIN
--●1. INNER JOIN
SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
--> 위에서 했던 이런 방식이 아니라
SELECT *
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO; --이렇게 쓰게 된다.
-- ※ INNER JOIN 시 INNER는 생략 가능하다.
SELECT *
FROM EMP E JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL;
--●2. OUTER JOIN
SELECT *
FROM TBL_EMP E, TBL_DEPT D
WHERE E.DEPTNO = D.DEPTNO(+);
--> 위에서 했던 방식
SELECT *
FROM TBL_EMP E LEFT OUTER JOIN TBL_DEPT D
ON E.DEPTNO = D.DEPTNO;
--==>> 19행
-- ※ 방향이 지정된 쪽 테이블(→ LEFT) 의 데이터를 모두 메모리에 적재한 후
-- 방향이 지정되지 않은 쪽 테이블들의 데이터를 각각 확인하여 결합시키는 형태로
-- JOIN 이 이루어진다.
SELECT *
FROM TBL_EMP E RIGHT OUTER JOIN TBL_DEPT D
ON E. DEPTNO = D.DEPTNO;
--===>> 16행
SELECT *
FROM TBL_EMP E FULL OUTER JOIN TBL_DEPT D
ON E. DEPTNO = D.DEPTNO;
--==>> 21행
-- OUTER JOIN 에서 OUTER는 생략 가능
-- 그럴때는 LEFT RIGHT FULL 이 붙어있는지 즉 방향이 지정되어 있는지 여부에 따라 구분가능하다
SELECT *
FROM TBL_EMP E LEFT JOIN TBL_DEPT D --OUTER JOIN
ON E. DEPTNO = D.DEPTNO;
SELECT *
FROM TBL_EMP E RIGHT JOIN TBL_DEPT D --OUTER JOIN
ON E. DEPTNO = D.DEPTNO;
SELECT *
FROM TBL_EMP E FULL JOIN TBL_DEPT D --OUTER JOIN
ON E. DEPTNO = D.DEPTNO;
SELECT *
FROM TBL_EMP E JOIN TBL_DEPT D --INNER JOIN
ON E. DEPTNO = D.DEPTNO;
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
SELECT *
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
-- 이 결과에서 직종이 CLERK 인 사원들만조회하려고 할때
SELECT *
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
AND JOB = 'CLERK';
-- 이렇게 쿼리문을 구성해도 조회하는데는 문제가 없다
SELECT *
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE JOB = 'CLERK';
-- 하지만, 이와 같이 구성하여 조회할 수 있도록 권장한다.
-- 92코드에서는 선택의 여지없이
SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND JOB = 'CLERK';
----------------------------------------------------------------------------------
SELECT *
FROM EMP;
/*
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/07/13 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 87/07/13 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10*/
SELECT *
FROM DEPT;
/*
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON*/
-- ● EMP 테이블과 DEPT 테이블을 대상으로 직종이 MANAGER 와 CLERK 인사원들만
-- 부서번호, 부서명, 사원명, 직종명, 급여 항목을 조회한다.
SELECT E.DEPTNO "부서번호", DNAME "부서명",ENAME "사원명", JOB "직종명", SAL "급여"
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE JOB IN ('CLERK','MANAGER');
/*
10 ACCOUNTING CLARK MANAGER 2450
10 ACCOUNTING MILLER CLERK 1300
20 RESEARCH ADAMS CLERK 1100
20 RESEARCH JONES MANAGER 2975
20 RESEARCH SMITH CLERK 800
30 SALES BLAKE MANAGER 2850
30 SALES JAMES CLERK 950
*/
-- "column ambiguously defined"
--(DEPTNO 의 경우 두 테이블에 모두 있기 때문에 어떤 테이블에 있는 DEPTNO를 표시할 것인지 명시를 해줘야함 E.DEPTNO 이런식으로)
-- 두 테이블 간 중복되는 컬럼에 대한 소속 테이블을 정해줘야( 명시해 줘야) 한다.
--> 두 테이블 간 중복되는 컬림이 존재하지 않는 조회 구문은 에러 발생하지 않는다. (DEPTNO SELECT문에서 빼주면 된다는 말)
--[선생님풀이]
SELECT D.DEPTNO, DNAME, DNAME, JOB, SAL
-- ★--------
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
--> 두 테이블 간 중복되는 컬럼에 대해 소속 테이블을 명시하는 경우 부서(DEPT), 사원(EMP)중 어떤 테이블을 지정해도
-- 쿼리문 수행에 대한 결과 반환에 문제가 없다. 하지만 두 테이블 간 중복되는 컬럼에 대해 소속 테이블을 명시하는 경우
-- 부모 테이블의 컬럼을 참조할 수 있도록 해야 한다.
--부모테이블이란??
SELECT *
FROM DEPT; -- 부모 테이블
SELECT *
FROM EMP; -- 자식 테이블
-- ※ 부모 자식 테이블 관계를 명확히 정리할 수 있도록 한다.
-- EMP테이블에는 부서번호가 여러번 등장할 수 있지만 부서테이블에 하나의 부서번호는 단 하나이다.
-------------------------------두개차이비교
SELECT D.DEPTNO, DNAME, DNAME, JOB, SAL
FROM EMP E RIGHT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
SELECT E.DEPTNO, DNAME, DNAME, JOB, SAL
FROM EMP E RIGHT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
--------------------------------------------
--최종 쿼리
SELECT D.DEPTNO, D.DNAME, D.DNAME, E.JOB, E.SAL <-- E. D. 으로 모든 소속 테이블 명시를 권장한다.
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
-- 중복된 컬럼이 아니더라도
-- 소속 테이블을 명시할 수 있도록 권장한다.
-- ● SELF JOJN(자기 조인)
-- EMP 테이블의 정보를 다음과 같이 조회할 수 있도록 한다.
-----------------------------------------
-- 사원번호 사원명 직종명 관리자번호 관리자명 관리자직종명
------------------------------------------------
SELECT *
FROM EMP;
/*
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 140030
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/07/13 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 87/07/13 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10*/
-- 사원번호 사원명 직종명 관리자번호 관리자명 관리자직종명
--[나의풀이]
SELECT E.EMPNO"사원번호", E.ENAME"사원명",E.JOB"직종명", E.MGR"관리자번호", S.ENAME"관리자명", S.JOB"관리자직종명"
FROM EMP E, EMP S
WHERE E.MGR = S.EMPNO;
/*
7902 FORD ANALYST 7566 JONES MANAGER
7788 SCOTT ANALYST 7566 JONES MANAGER
7844 TURNER SALESMAN 7698 BLAKE MANAGER
7499 ALLEN SALESMAN 7698 BLAKE MANAGER
7521 WARD SALESMAN 7698 BLAKE MANAGER
7900 JAMES CLERK 7698 BLAKE MANAGER
7654 MARTIN SALESMAN 7698 BLAKE MANAGER
7934 MILLER CLERK 7782 CLARK MANAGER
7876 ADAMS CLERK 7788 SCOTT ANALYST
7698 BLAKE MANAGER 7839 KING PRESIDENT
7566 JONES MANAGER 7839 KING PRESIDENT
7782 CLARK MANAGER 7839 KING PRESIDENT
7369 SMITH CLERK 7902 FORD ANALYST*/
--==>> 총 13행
SELECT E.EMPNO"사원번호", E.ENAME"사원명",E.JOB"직종명", E.MGR"관리자번호", S.ENAME"관리자명", S.JOB"관리자직종명"
FROM EMP E JOIN EMP S
ON E.MGR = S.EMPNO;
--==>> 총 13행
SELECT E.EMPNO"사원번호", E.ENAME"사원명",E.JOB"직종명", E.MGR"관리자번호", S.ENAME"관리자명", S.JOB"관리자직종명"
FROM EMP E LEFT JOIN EMP S
ON E.MGR = S.EMPNO;
--==>> 총 14행(E 테이블에 관리자번호가 없는 사원이 출력된다. )