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검색조건 추가