국비 TIL(Today I Learned) 20220706 오라클 조인(INNER JOIN, OUTER JOIN)

JOIN : 데이터를 이어준다.

 

INNER JOIN

: 조건이 TRUE인 것만 남기겠다. A와 B가 모두 조건에 부합하는 것만 남음

A INNER JOIN B ON 조건

 

OUTER JOIN

: 해당 방향의 모든 데이터와 반대쪽의 경우 조건이 TRUE인것만 가져옴, 반대쪽 조건이 성립하지 않으면 NULL로 채움

A LEFT OUTER JOIN B ON 조건

A RIGHT OUTER JOIN B ON 조건

 

 

 

PRIMARY KEY: 주키, 데이터를 구분 지음, 주로 숫자로 되어있음, 주키가 있는곳이 부모데이터

FOREIGN KEY: 외래키, 다른데이터를 참조

1: N 직접참조: 데이터 간 직접적인 관계를 가지는 것, 조건의 ex) 부서번호 = 부서번호

N:1 간접참조: 데이터 간 범위나 특정 조건으로 간접적인 관계를 가지는 것,

조건의 ex) 급여 BETWEEN 최저급여 AND 최대급여

1: N 자가참조 : 자기 자신의 데이터와 관계를 갖는 것, ex) 사원번호 = 상급자번호

 

 

 

계층형 쿼리 잠깐 애기, 이런게 있다. 오라클 제외 다른 DBMS에서는 안됨

https://blog.naver.com/javaking75/220010288704

[오라클] 계층형 쿼리 ( START WITH ... CONNECT BY )

 

 

 

UNION: VIEW의 결과를 중첩

주의사항: 위의 쿼리와 아래 쿼리의 컬럼 개수와 타입이 동일해야 함. 컬럼명은 위의 쿼리를 기준으로 제공

 

쿼리

UNION : 중복을 제거하고 합침

쿼리

 

쿼리

UNION ALL : 중복을 제거하지 않고 함침

쿼리

 

JOIN 은 옆으로 붙은거고 (가로)

UNION은 밑으로 붙은거고 (세로)

 

 

-- 직접참조 + INNER JOIN
SELECT E.ENAME, D.DNAME
FROM EMP E INNER JOIN DEPT D 
ON E.DEPTNO = D.DEPTNO;

-- 직접참조 + OUTER JOIN, 왼쪽은 데이터 모두 나옴, 조건에 만족하지 않으면 NULL
SELECT D.DNAME, E.ENAME 
FROM DEPT D LEFT OUTER JOIN EMP E
ON D.DEPTNO = E.DEPTNO;

-- 간접참조 + INNER JOIN
SELECT E.ENAME, E.SAL, SG.GRADE, SG.LOSAL, SG.HISAL
FROM EMP E INNER JOIN SALGRADE SG ON -- 1 =1; 1=1 은 조건없이 조인하겠다. 단순 곱연산(테이블 각 행 * 행)
E.SAL BETWEEN SG.LOSAL AND SG.HISAL;

-- 자가참조 + INNER JOIN
SELECT E1.ENAME, E1.MGR, E2.EMPNO, E2.ENAME
FROM EMP E1 INNER JOIN EMP E2   -- E1 : 부하직원, E2 : 상급자
ON E1.MGR = E2.EMPNO;

SELECT E1.ENAME, E1.MGR, E2.EMPNO, E2.ENAME
FROM EMP E1, EMP E2   -- E1 : 부하직원, E2 : 상급자
WHERE E1.MGR = E2.EMPNO;

-- 자가참조 + LEFT OUTER JOIN : KING 까지 다 보기위해 OUTER JOIN
SELECT E1.ENAME, E1.MGR, E2.EMPNO, E2.ENAME
FROM EMP E1 LEFT OUTER JOIN EMP E2   -- E1 : 부하직원, E2 : 상급자
ON E1.MGR = E2.EMPNO;

-- 오라클에서 OUTER JOIN 쓸때 끝에 (+), 다른 DBMS는 다름, 이게 쉬운것 같지만 테이블이 더 추가되면, 데이터 돌아가는 측면에서 위에가 효율적
SELECT E1.ENAME, E1.MGR, E2.EMPNO, E2.ENAME
FROM EMP E1, EMP E2   -- E1 : 부하직원, E2 : 상급자,  -- 여기서 테이블이 더 추가되면, 전부의 곱연산후 WHERE절이 돌아가기 때문에
WHERE E1.MGR = E2.EMPNO(+);


SELECT ENAME AS ABC FROM EMP UNION SELECT ENAME FROM EMP;
SELECT ENAME AS ABC FROM EMP UNION ALL SELECT ENAME FROM EMP;
-- 중복 데이터 처리하기
-- DISTINCT : SELECT 뒤에 바로 작성, 중복제거, 보여질 내용에서 제거
SELECT DISTINCT E.ABC FROM (SELECT ENAME AS ABC FROM EMP UNION ALL SELECT ENAME FROM EMP) E;


-- DALLAS에 근무하는 사원들의 급여 순위를 구하시오.
SELECT E.ENAME, E.SAL, D.LOC,
RANK() OVER(ORDER BY SAL DESC) AS RNK
FROM  DEPT D LEFT OUTER JOIN EMP E
ON D.DEPTNO = E.DEPTNO
WHERE D.LOC = 'DALLAS';


-- 급여 등급이 2등급인 사원들의 업무별 총 인원수를 구하시오.
-- 2등급인 사원 먼저 구하기
SELECT E.ENAME, E.SAL, E.JOB, SG.GRADE, SG.LOSAL, SG.HISAL
FROM EMP E INNER JOIN SALGRADE SG ON 
E.SAL BETWEEN SG.LOSAL AND SG.HISAL
WHERE SG.GRADE = '2';
-- 내 풀이
SELECT JOB, COUNT(*) 
FROM EMP E INNER JOIN SALGRADE SG ON 
E.SAL BETWEEN SG.LOSAL AND SG.HISAL WHERE SG.GRADE = '2'
GROUP BY JOB;
-- 강사님 풀이
SELECT JOB, COUNT(*) AS CNT
FROM  EMP E INNER JOIN SALGRADE SG ON 
E.SAL BETWEEN SG.LOSAL AND SG.HISAL AND SG.GRADE = '2'
GROUP BY JOB;

-- CHICAGO에 근무하는 급여순위 2등의 급여보다 많이 받는 사원들을 구하시오.
SELECT E.SAL,
RANK() OVER(ORDER BY SAL DESC) AS RNK
FROM  DEPT D LEFT OUTER JOIN EMP E
ON D.DEPTNO = E.DEPTNO
WHERE D.LOC = 'CHICAGO';
-- CHICAGO에 근무하는 급여순위 2등의 급여
SELECT SAL
FROM (SELECT E.SAL,
RANK() OVER(ORDER BY SAL DESC) AS RNK
FROM  DEPT D LEFT OUTER JOIN EMP E
ON D.DEPTNO = E.DEPTNO
WHERE D.LOC = 'CHICAGO'
) WHERE RNK = '2';

SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT SAL 
FROM (SELECT E.SAL,
RANK() OVER(ORDER BY SAL DESC) AS RNK
FROM  DEPT D LEFT OUTER JOIN EMP E
ON D.DEPTNO = E.DEPTNO
WHERE D.LOC = 'CHICAGO'
) WHERE RNK = '2')
ORDER BY SAL ASC
;

SELECT ENAME, SAL, (SELECT SAL 
FROM (SELECT E.SAL,
RANK() OVER(ORDER BY SAL DESC) AS RNK
FROM  DEPT D LEFT OUTER JOIN EMP E
ON D.DEPTNO = E.DEPTNO
WHERE D.LOC = 'CHICAGO'
) WHERE RNK = '2') SEC
FROM EMP
WHERE SAL > (SELECT SAL 
FROM (SELECT E.SAL,
RANK() OVER(ORDER BY SAL DESC) AS RNK
FROM  DEPT D LEFT OUTER JOIN EMP E
ON D.DEPTNO = E.DEPTNO
WHERE D.LOC = 'CHICAGO'
) WHERE RNK = '2')
ORDER BY SAL ASC
;


-- 강사님 풀이
SELECT E.ENAME, E.SAL, C.SAL AS SEC
FROM EMP E INNER JOIN(SELECT E.SAL, 
RANK() OVER(ORDER BY SAL DESC) AS RNK
FROM EMP E INNER JOIN DEPT D
ON  D.DEPTNO = E.DEPTNO AND D.LOC = 'CHICAGO') C
ON E.SAL > C.SAL
AND C.RNK = 2;



-- 연도별 입사자수가 가장 많은 부서를 출력하시오

-- 내가 푼거
SELECT TO_CHAR(HIREDATE, 'YYYY'), D.DNAME, COUNT(*) AS CNT,
        RANK() OVER(PARTITION BY TO_CHAR(HIREDATE, 'YYYY') ORDER BY COUNT(*) DESC) AS RNK
FROM EMP E INNER JOIN DEPT D 
ON E.DEPTNO = D.DEPTNO
GROUP BY TO_CHAR(HIREDATE,'YYYY'), D.DNAME;


SELECT S.YEAR, S.DNAME
FROM (SELECT TO_CHAR(HIREDATE, 'YYYY') AS YEAR , D.DNAME, COUNT(*) AS CNT,
        RANK() OVER(PARTITION BY TO_CHAR(HIREDATE, 'YYYY') ORDER BY COUNT(*) DESC) AS RNK
FROM EMP E INNER JOIN DEPT D 
ON E.DEPTNO = D.DEPTNO
GROUP BY TO_CHAR(HIREDATE,'YYYY'), D.DNAME
) S
WHERE S.RNK = '1';

-- 팀원이 푼거
SELECT D.DNAME, F.YEAR
FROM DEPT D INNER JOIN 
(SELECT D.DNAME,D.DEPTNO, TO_CHAR(HIREDATE,'YYYY') AS YEAR , COUNT(*) AS CNT , RANK() OVER(PARTITION BY TO_CHAR(HIREDATE,'YYYY') ORDER BY COUNT(*) DESC ) AS RNK
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO =D.DEPTNO
GROUP BY TO_CHAR(HIREDATE,'YYYY'),D.DNAME,D.DEPTNO
)F
ON D.DEPTNO=F.DEPTNO
AND F.RNK=1
ORDER BY YEAR;

-- 강사님 풀이
-- 연도별 부서별 입사자수
SELECT TO_CHAR(HIREDATE, 'YYYY') AS YY, DEPTNO, COUNT(*) AS CNT
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY'), DEPTNO
;
-- 연도별 부서의 입사자수 순위
SELECT TO_CHAR(HIREDATE, 'YYYY') AS YY, DEPTNO, COUNT(*) AS CNT,
RANK() OVER(PARTITION BY TO_CHAR(HIREDATE, 'YYYY') ORDER BY COUNT(*) DESC) AS RNK
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY'), DEPTNO
;

-- 연도별 입사자수가 가장 많은 부서를 출력하시오
SELECT E.YY, D.DNAME
FROM (SELECT TO_CHAR(HIREDATE, 'YYYY') AS YY, DEPTNO, COUNT(*) AS CNT,
RANK() OVER(PARTITION BY TO_CHAR(HIREDATE, 'YYYY') ORDER BY COUNT(*) DESC) AS RNK
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY'), DEPTNO) E
INNER JOIN DEPT D ON E.DEPTNO= D.DEPTNO
WHERE E.RNK=1
ORDER BY E.YY;

 

 

* 내가 모르겠는거!

- 서브쿼리랑 조인이랑 뭐가 더 효율적인지

- where이랑 on이 왜 헷갈리지, 둘다 조건 같아