06-29(화) 오라클조인

※ 개발 수업 정리글입니다. 꾸준히 정리할 예정이고 틀린 부분이 있다면 언제든지 댓글 환영입니다.

---

※익숙해지기※
날짜 뽑아올때 
STUBSTR(컬럼, 몇번째부터, 몇개) 도 알고 있어도 좋지만,
TO_CHAR( 컬럼, 'YYYY') 이거랑 익숙해지도록 노력하기

 

그룹함수의  HAVING 절

② WHERE은 무조건 TABLE에서 골라내고 HAVING은 GROUP BY된 거에서 골라낸다

⑤ SELECT 컬럼, 그룹함수, 그룹함수 ·····
① FROM TABLE
② WHERE ~
③ GROUP BY  [ SELECT 위의 컬럼 중 하나 ]
④ HAVING ~
⑥ ORDER BY ~ ;

-계속 예제 위주로 했음

 

★★★GROUP BY는 앞에 SELET~에 그룹함수가 2개면 그대로 가져다가 2개 이상도 쓸 수 있음★★★

---

 JOIN 함수  테이블 합치기

<종류 2개>

오라클조인		ANSI조인
조인조건 생략or잘못된 경우	cartesian product	Cross 조인	조인조건 생략or잘못된 경우
반드시 일치하는 데이터만 조회	Equi 조인	Natural 조인	반드시 일치하는 데이터만 조회
		USING조인	명시적으로 일치하는 데이터만 조회
일치하지 않아도 범위 포함되면 조회	Non-Equi 조인	ON조인	일치하지 않아도 범위 포함되면 조회
일치하지 않아도 전부 표시하는 조인	Outer 조인	LEFT | RIGHT | FULL OUTER 조인	일치하지 않아도 전부 표시하는 조인
자신의 테이블과 조인	Self 조인	Self 조인	자신의 테이블과 조인

-문법만 다르고 동작방식 비슷

-JOIN함수도 NULL값이면 제외됨

-여러테이블이 있어서 
  중복컬럼이 있을 땐, 테이블이름.컬럼명 =>반드시 컬럼명 앞에 테이블명 지정
  중복컬럼이 아니라면, 굳이 테이블명. 을 안해도 되지만, 명확하게 하려면 적어도 됨

-테이블이 길어지면 별칭 사용해서 별칭.컬럼명

-오라클 조인에서는 WHERE절에 AND OR 연산자 사용가능

   [ 순서: 조인조건 먼저-검색조건 나중 ] =>이렇게 해야만 가독성 좋음 습관들이기

-★★★★조인함수할땐 

SELECT * FROM으로 뒤에 먼저 작성해서 결과값이 나오는지 보고,

나중에 컬럼 고쳐주는게 나음★★★★

 

 

 

오라클조인 cartesian product

-조인 조건이 생략 or 잘못 됐을때

-각 테이블의 행 개수를 서로 곱한 결과가 반환 따라서 올바른 데이터가 아니기 때문에 거의 안씀

테이블1* 테이블2

 

 

■ 오라클조인 Equi 조인

    -테이블들 공통컬럼이 반드시 일치하는 행을 WHERE절에 사용하는 조인

 

③ SELECT 컬럼, 컬럼
① FROM 테이블1, 테이블2
② WHERE 테이블1.공통컬럼=테이블2.공통컬럼;

 

*컬럼 앞에 테이블 붙일때, 처음부터 FROM 테이블 별칭 주지 말고,
  그냥 FROM 테이블만 적고 다른 컬럼 앞엔 테이블 풀넴 적음
  ex) SELECT *
  FROM EMP, DEPT
  WHERE EMP.DEPTNO=DEPT.DEPTNO ;

 

 

 

 

■오라클조인 Non-Equi 조인

    -테이블들 공통컬럼이 반드시 일치하지 않는, 즉 값이 다를때

     WHERE절에 연산자(BETWEEN ~ AND~ , >  >=  <  <=)를 사용하는 조인

 

③ SELECT 컬럼, 컬럼, ...
① FROM 테이블1, 테이블2
② WHERE 테이블1. 컬럼 BETWEEN 테이블2.컬럼a AND 테이블2.컬럼b
        또는 테이블1. 컬럼     >=    조건값          이런식으로도 가능

 

ex) FROM EMP E, DEPT D, SALGRADE S

    WHERE E.DEPTNO = D.DEPTNO              =>여기는 동등연산자니까, 오라클조인의 Equi조인

    AND SAL BETWEEN S.LOSAL AND S.HISAL  ; =>여긴 동등연산자 외의 연산자니까, Non-Equi조인

 

★3개 이상의 테이블 조인도 가능★

SELECT 컬럼, 컬럼, ...

FROM 테이블1 1, 테이블2 2, 테이블3 3

WHERE  테이블1.공통컬럼 = 테이블2.공통컬럼   --->테이블1과 테이블2 Equi조인했음

AND     테이블2.공통컬럼 = 테이블3.공통컬럼 ;  --->테이블2과 테이블3 Equi조인했음

Non-Equi조인이든 SELF조인이든 OUTER조인이든 뭐든가능

 

 

 

■오라클조인  self 조인

    ㄴ하나의 테이블만 사용->자기 자신을 FROM 테이블에서 복제하고 WHERE절에서 조인

 

③ SELECT  a .컬럼,  b .컬럼 ....
① FROM 테이블1  a   , 테이블1  b     ------테이블1을 한번 더 써서 별칭으로 나눠줌
② WHERE  a .관리자사번 =  b .일반사번..... ;

 

 

■오라클조인  outer 조인  (+) 사용

    ㄴ조인 조건에 만족하지 않아도 결과 값에 포함. 값이 없는 한쪽에만  (+) 연산자 사용

ex) EMP테이블의 KING의 관리자는 없음. 그럼 조인시켰을때 아무도 없음(IS NULL)

     JOIN함수도 NULL값이면 제외되어서 안 뜨는데 이때 (+) 를 쓰면

 

* 예제 더보기 누르기

SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE SAL>800
GROUP BY DEPTNO
HAVING SUM(SAL)>9000; ----->이거 내가 틀린게 아니었음.. ㅈㅃㄱ-)

 

SELECT JOB, AVG(SAL), SUM(SAL)
FROM EMP
GROUP BY JOB
HAVING AVG(SAL)>=3000;

 

SELECT JOB, SUM(SAL)PAYROLL
FROM EMP
WHERE JOB!='SALESMAN'
GROUP BY JOB
HAVING SUM(SAL)>5000
ORDER BY 2 DESC;

SELECT DEPTNO, COUNT(*)
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*)>=6;

SELECT TO_CHAR(HIREDATE, 'YYYY') 년 , 
TO_CHAR(HIREDATE, 'MM') 월 , SUM(SAL)
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY'), 
TO_CHAR(HIREDATE, 'MM')
ORDER BY 1 ASC;

3.★

SELECT
    SUM(CASE JOB WHEN 'CLERK' THEN 1 ELSE 0 END) "CLERK",
    SUM(CASE JOB WHEN 'SALESMAN' THEN 1 ELSE 0 END) "SALESMAN",
    SUM(CASE JOB WHEN 'MANAGER' THEN 1 ELSE 0 END) "MANAGER",
    SUM(CASE JOB WHEN 'ANALYST' THEN 1 ELSE 0 END) "ANALYST",
    SUM(CASE JOB WHEN 'PRESIDENT' THEN 1 ELSE 0 END) "PRESIDENT",
    COUNT(*)  --GROUP BY 없으면 EMP의 전체레코드 수/ 그룹하면 각각 업무의 총 레코드수
FROM EMP
GROUP BY JOB;

 

=SELECT 
SUM(DECODE(JOB, 'CLERK', 1,0)) CLERK,
SUM(DECODE(JOB, 'SALESMAN', 1,0) ) SALESMAN,
SUM(DECODE(JOB, 'MANAGER', 1,0)) MANAGER,
SUM(DECODE(JOB, 'ANALYST', 1,0)) ANALYST,
SUM(DECODE(JOB, 'PRESIDENT', 1,0))PRESIDENT,
COUNT(*)
FROM EMP
GROUP BY JOB;

책
SELECT COUNT(*) "총인원수",
    SUM(DECODE(TO_CHAR(HIREDATE, 'YYYY'),1980,1,0)) "1980",
    SUM(DECODE(TO_CHAR(HIREDATE, 'YYYY'),1981,1,0)) "1981",
    SUM(DECODE(TO_CHAR(HIREDATE, 'YYYY'),1982,1,0)) "1982"
FROM EMP;

 

SELECT*
FROM EMP, DEPT
WHERE ENAME='KING';

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

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

SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME, DEPT.LOC
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;

SELECT ENAME, E.DEPTNO, DNAME
FROM EMP E, DEPT D
WHERE E. DEPTNO=D.DEPTNO AND E.ENAME='KING';

SELECT E.ENAME,E.SAL, S.GRADE
FROM SALGRADE S, EMP E
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

SELECT E.ENAME,E.SAL, S.GRADE
FROM SALGRADE S, EMP E
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
AND E.ENAME='KING';

SELECT S.GRADE, COUNT(*)
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
--AND S.GRADE=2
GROUP BY S.GRADE;

SELECT D.DEPTNO, DNAME, ENAME
FROM EMP E, DEPT D
WHERE TO_CHAR(E.HIREDATE, 'YYYY')=1981;

SELECT DNAME , COUNT(*) 
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND TO_CHAR(E.HIREDATE, 'YYYY')<2005
GROUP BY DNAME;
SELECT ENAME, SAL, DNAME, S.GRADE FROM
EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO=D.DEPTNO ---조인 시켜주는거임
AND E.SAL BETWEEN S.LOSAL AND s.hisal;

SELECT e.ename 사원이름, E.MGR 관리자번호, M.EMPNO 관리자사번, m.ename FROM
EMP E, EMP M
WHERE E.MGR =M.EMPNO;

SELECT E.ENAME, E.MGR, M.EMPNO, M.ENAME, M.DEPTNO, D.DNAME FROM
EMP E, EMP M, DEPT D
WHERE E.MGR=M.EMPNO AND M.DEPTNO=D.DEPTNO
AND E.EMPNO=7902;

SELECT E.MGR, M.ENAME, D.DNAME FROM
EMP E, EMP M, DEPT D
WHERE E.ENAME='FORD' AND E.MGR=M.EMPNO AND E.DEPTNO=D.DEPTNO
;


SELECT E.ENAME, E.MGR, M.EMPNO, M.ENAME, M.MGR, MM.EMPNO, MM.ENAME FROM
EMP E, EMP M, EMP MM
WHERE E.MGR=M.EMPNO
AND M.MGR=MM.EMPNO
AND E.ENAME='FORD';

예제)어디서 나온걸까 이건..

select e.ename 사원명, m.ename 관리자명

from emp e, emp m

WHERE E.MGR=M.EMPNO(+);

​

SELECT E.ENAME 사원명,

E.ENAME 관리자명,

MM.ENAME "관리자의 관리자명"

FROM EMP E, EMP M, EMP MM

WHERE E.MGR=M.EMPNO(+)

AND M.MGR=MM.EMPNO(+);

​

SELECT ENAME, DNAME, E.DEPTNO

FROM EMP E  CROSS JOIN DEPT D;

​

SELECT ENAME, DNAME, E.DEPTNO

FROM EMP E NATURAL JOIN DEPT D; --이상태론 안됨 검색조건 추가해야함

​

SELECT ENAME, DNAME, DEPTNO

FROM EMP E NATURAL JOIN DEPT D

WHERE DEPTNO=30; ---E. 빼고 WHERE검색조건 추가