국비학원 16일차) ORACLE 조인, 서브쿼리 예습

JOIN 여러 테이블을 붙여서 하나의 테이블처럼 사용하자!

SELECT *
FROM EMP, DEPT
ORDER BY EMPNO;

EMP 테이블이랑 DEPT 테이블을 합쳐줘!
EMPNO 기준으로 정렬해주고


하지만, 모든 칼럼을 경우의 수를 두고 출력해서
정확한 값을 받기 힘들 수 있음(굉장히 많은 데이터 출력되기도 함)
그래서 알맞은 조건으로 검색하는 것이 중요함
WHERE을 잘 사용해보자

테이블명.칼럼명
어느 테이블에 속한 칼럼인지 구분할 수 있다(클래스명.메소드명)



SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
ORDER BY EMPNO;

EMP와 DEPT의 모든 칼럼을 출력할 거야
근데 EMP의 DEPTNO과 DEPT의 DEPTNO 값이 같은 행만 출력해줘




테이블의 별명을 지정하자!
FROM 테이블명 별명, 테이블명 별명, ...
(클래스명 참조변수) 랑 비슷함


SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY EMPNO;

EMP를 E라고 부르고 DEPT를 D라고 부를 거야
E의 DEPTNO과 D의 DEPTNO이 겹치는 행만 출력해줘


JOIN-ON 을 사용한 조인

SELECT E.LAST_NAME, E.JOB_ID,D.DEPARTMENT_NAME, L.CITY
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
WHERE E.DEPARTMENT_ID=80
;




USING() : 공통된 칼럼명을 작성할 때

SELECT LAST_NAME, JOB_ID, DEPARTMENT_NAME, CITY
FROM EMPLOYEES JOIN DEPARTMENTS 
USING(DEPARTMENT_ID)
JOIN LOCATIONS
USING(LOCATION_ID)
WHERE DEPARTMENT_ID=80
;

별명 안 붙여도 됨
FROM 테이블1 JOIN 테이블2 USING(1,2의 공통 칼럼)
JOIN 테이블3 USING(2,3의 공통 칼럼)




조인 종류


이너 조인(내부 조인)
테이블 연결 후, 각 테이블의 칼럼에 일치한 데이터를 기준으로 행을 출력
ㄴ 위에 예제들이 이너 조인 사용


SELECT E.EMPNO, E.ENAME, D.DNAME,D.DEPTNO,D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY D.DEPTNO, E.EMPNO;

EMP의 EMPNO, ENAME
DEPT의 DNAME, DEPTNO, LOC를 출력할 건데
두 테이블의 DEPTNO이 겹치는 애들만 출력해줘
D.DEPTNO을 기준으로 정렬했으면 그 다음엔 EMPNO에 맞춰서 정렬해


SELECT E.EMPNO, E.ENAME, E.SAL, D.DNAME,D.DEPTNO,D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND SAL>=3000;

MP의 EMPNO, ENAME
DEPT의 DNAME, DEPTNO, LOC를 출력할 건데
두 테이블의 DEPTNO이 겹치는데, SAL이 3000이상인 애들만 출력해줘



아우터 조인(외부 조인)

LOJ 왼쪽 WHERE TABLE1.COL1 = TABLE2.COL1(+)
ROJ 오른쪽 WHERE TABLE1.COL1(+) = TABLE2.COL1



왼쪽 아우터 조인
왼쪽 칼럼의 데이터를 기준으로 오른쪽 칼럼에 존재 여부와 상관 없이 출력해

SELECT
E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
*WHERE E1.MGR=E2.EMPNO(+)
ORDER BY E1.EMPNO;

* E1.MGR이 NULL 이더라도, 출력값에서 빼지 말고 출력해



오른쪽 아우터 조인
오른쪽 칼럼을 기준으로, 왼쪽 칼럼에 데이터가 없어도 출력해

SELECT
E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
*WHERE E1.MGR(+)=E2.EMPNO
ORDER BY E1.EMPNO;

*EMPNO가 없어도, 출력값에서 빼지 말고 출력해





SELECT *
FROM (SELECT JOB, DEPTNO, SAL FROM EMP)
PIVOT (MAX(SAL) FOR JOB IN
('CLERK' AS CLERK,
'SALESMAN' AS SALESMAN,
'PRESIDENT' AS PRESIDENT,
'MANAGER' AS MANAGER,
'ANALYST' AS ANALYST))
ORDER BY DEPTNO;

설명이 어렵다....결과로 대신

SELECT DEPTNO,
MAX(DECODE(JOB, 'CLERK', SAL)) AS "CLERK",
MAX(DECODE(JOB, 'SALESMAN', SAL)) AS "SALESMAN",
MAX(DECODE(JOB, 'PRESIDENT', SAL)) AS "PRESIDENT",
MAX(DECODE(JOB, 'MANAGER', SAL)) AS "MANAGER",
MAX(DECODE(JOB, 'ANALYST', SAL)) AS "ANALYST"
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;


UNPIVOT <- 반대 -> PIVOT


SELECT *
FROM( SELECT DEPTNO,
MAX(DECODE(JOB, 'CLERK', SAL)) AS "CLERK",
MAX(DECODE(JOB, 'SALESMAN', SAL)) AS "SALESMAN",
MAX(DECODE(JOB, 'PRESIDENT', SAL)) AS "PRESIDENT",
MAX(DECODE(JOB, 'MANAGER', SAL)) AS "MANAGER",
MAX(DECODE(JOB, 'ANALYST', SAL)) AS "ANALYST"
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO)
UNPIVOT(SAL FOR JOB IN(CLERK, SALESMAN, PRESIDENT, MANAGER, ANALYST))
ORDER BY DEPTNO, JOB;


EXTRACT
특정 날짜, 시간이나 날짜 값 등의 표현식으로부터
지정된 날짜 영역의 값을 추출하여 반환

SELECT EXTRACT(MONTH FROM HIRE_DATE), COUNT(*)
FROM EMPLOYEES
GROUP BY EXTRACT(MONTH FROM HIRE_DATE)
ORDER BY EXTRACT(MONTH FROM HIRE_DATE);

HIRE_DATE의 MONTH를 추출해주고 그룹으로 묶어줘,
그룹당 몇개가 있는지 세줘


----------------------------------서브 쿼리

서브 쿼리
SQL문 내부에서 사용하는 SELECT 문!

WHERE에 서브쿼리를 사용한다면?
SELECT 칼럼명
FROM 테이블명
WHERE 조건식(SELECT 칼럼명
FROM 테이블명
WHERE 조건식)


SELECT *
FROM EMP
WHERE SAL>
(SELECT SAL
FROM EMP
WHERE ENAME = 'JONES');

(JONES의 SAL)보다 높은 SAL인 사람의 행만 출력해줘



단일행 서브쿼리 - 실행 결과가 하나 !
단일행 연산자를 이용합니다
>, =, <, != ...등



날짜형 데이터를 이용한 단일행 서브쿼리
SELECT *
FROM EMP
WHERE HIREDATE <
(SELECT HIREDATE
FROM EMP
WHERE ENAME = 'FORD');

FORD 보다 HIREDATE가 일찍인 사람만 출력해줘



단일형 서브쿼리+함수
SELECT
E.EMPNO, E.ENAME, E.JOB, E.SAL,
D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.DEPTNO = 20
AND E.SAL>
(SELECT AVG(SAL)
FROM EMP);

E.DEPTNO이 20인 사람 중에
EMP테이블의 SAL 평균보다 더 높은 사람만 출력해줘





다중행 서브쿼리 - 결과가 여러개로 나오는 서브쿼리!
IN : 메인쿼리 결과가 서브쿼리 결과에 하나라도 일치하면 TRUE
ANY, SOME : 메인쿼리 조건식을 만족하는 서브쿼리 결과가 하나 이상이면 TRUE
ALL : 메인 쿼리 조건식을 서브쿼리가 모두 만족하면 TRUE
EXISTS : 서브쿼리의 결과가 존재하면(행이 1개 이상) TRUE


-IN : 메인 쿼리 데이터가 서브쿼리 결과 중 하나라도 일치하면 TRUE ( 교집합이면 TRUE )
SELECT *
FROM EMP
WHERE SAL IN
(SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO);

DEPTNO을 기준으로 MAX(SAL)최고 급여를 받는 사람만 출력해줘




- ANY, SOME : 메인쿼리 조건식에 서브쿼리 값이 하나라도 맞으면 TRUE ( 교집합있으면 TRUE )


- ANY : IN이 훨씬 잘 쓰임
SELECT *
FROM EMP
WHERE SAL = ANY(SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO)
;

EMP의 SAL이
EMP의 DEPTNO을 기준으로 한 MAX(SAL)에
맞는 결과인 것만 출력해줘

- SOME
SELECT *
FROM EMP
WHERE SAL = SOME (SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO)
;

결과 상동



SELECT *
FROM EMP
WHERE SAL > ANY (SELECT SAL
FROM EMP
WHERE DEPTNO = 30)
;

DEPTNO이 30인 사람들의 최소 SAL보다
SAL이 더 높은 EMP 사람들 전부 행을 출력해줘



ALL : 서브쿼리의 모든 결과가 조건식에 맞아야지만 TRUE (합집합이어야 TRUE)

SELECT *
FROM EMP
WHERE SAL < ALL (
SELECT SAL
FROM EMP
WHERE DEPTNO=30);

DEPTNO이 30인 사람 중에 최소SAL보다 /EMP 전부에서 더 적은 SAL을 가진 사람 출력


SELECT *
FROM EMP
WHERE SAL > ALL (
SELECT SAL
FROM EMP
WHERE DEPTNO=30)
;

DEPTNO이 30인 사람 중에 최대SAL보다 /EMP 전부에서 더 큰 SAL을 가진 사람 출력



EXISTS : 서브쿼리에 결과 값이 하나 이상 존재하면 모두 TRUE, 아니면 모두 FALSE
(교집합이면 전부 TRUE, 아니면 전부 FALSE)


SELECT *
FROM EMP
WHERE EXISTS (
SELECT DNAME
FROM DEPT
WHERE DEPTNO=10)
;

DEPT 테이블에서 DEPNO이 10인 사람이 있어?
그러면 EMP의 모든 칼럼 출력해



다중 컬럼 서브쿼리
서브쿼리의 SELECT절에 비교할 데이터를 여러개 지정하자!


SELECT *
FROM EMP
WHERE (DEPTNO, SAL) IN
(SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO)
;

메인 쿼리에 있는 DEPTNO, SAL만 비교할게~
서브쿼리에 있는 DEPTNO을 그룹화 할 거고
거기서 SAL이 제일 큰 애들 있지? 걔들 정보 EMP에서 불러와~



FROM에서 사용하는 서브쿼리와 WITH
SELECT문에서 데이터 먼저 추출하고, 걔네 별명으로 뭔가 함


인라인 뷰 사용
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME, D.LOC
FROM
(SELECT * FROM EMP WHERE DEPTNO=10) E,
(SELECT * FROM DEPT) D
WHERE E.DEPTNO = D.DEPTNO;


위에 컬럼들 출력할건디
E의 DEPTNO이 10인 애들만 볼 거고
E.DEPTNO이랑 D.DEPTNO에 같은 애들은 똑같은 애들로 취급해~


WITH
E AS (SELECT * FROM EMP WHERE DEPTNO=10),
D AS (SELECT * FROM DEPT)
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME, D.LOC
FROM E, D
WHERE E.DEPTNO = D.DEPTNO
;

위와 결과 동일함
근데 얘는 헷갈림....



SELECT절에서 사용하는 서브 쿼리

SELECT EMPNO, ENAME, JOB, SAL,
(SELECT GRADE
FROM SALGRADE
WHERE E.SAL BETWEEN LOSAL AND HISAL) AS SALGRADE,
DEPTNO,
(SELECT DNAME
FROM DEPT
WHERE E.DEPTNO = DEPT.DEPTNO) AS DNAME
FROM EMP E;

EMPNO, ENAME, JOB, SAL, SALGRADE, DEPTNO 출력할 건데

1.SALGRADE는 SALGRADE테이블에 있는 SALGRADE야
E.SAL 
2.DNAME은 DEPT테이블에 있는 DNAME인데,
E.DEPTNO랑 DEPT.DEPTNO 겹치는 거 합쳐서 DNAME 출력할거야~


NOT IN 차집합 쿼리



서브쿼리 개꿀팁
서브쿼리(괄호)안에 있는 거 먼저 구할 거야
메인쿼리에서 그거에 맞는 값만 출력해~
이러면 대충 맞음