그룹화
- 결과 값을 원하는 열로 묶어 출력
- 그룹 별로 하나씩 결과 출력
- GROUP BY 절 → 명시하지 않은 열 사용 불가
SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO ASC, JOB ASC;
- HAVING절
- GROUP BY 절에서 조건 사용
- GROUP BY ~ HAVING
-- 평균 급여 2000 이상 => 전체 사원 14명을 가지고 그룹화
SELECT DEPTNO, JOB, AVG(SAL) FROM EMP
GROUP BY DEPTNO, JOB HAVING AVG(SAL) >= 2000
ORDER BY DEPTNO ASC, JOB ASC;
- 그룹화와 관련된 여러 함수 → ROLLUP, CUBE, GROUPING SETS
- 그룹화 데이터의 합계 출력:
- ROLLUP(A, B) : 열의 개수 + 1 개의 결과 출력 → A+B, A, TOTAL
-- ROLLUP: 열의 개수 + 1 개의 결과 출력
-- 1) A + B = 부서번호 + 직책으로 GROUP BY
-- 2) A = 부서번호로 GROUP BY
-- 3) 전체
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL) FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
- CUBE(A, B) : 2의 열의 개수 제곱 개의 결과 출력
→ A+B, A, B, TOTAL
-- ROLLUP: 열의 개수 + 1 개의 결과 출력
-- 1) A + B = 부서번호 + 직책으로 GROUP BY
-- 2) A = 부서번호로 GROUP BY
-- 3) 전체
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL) FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
- CUBE(A, B) : 2의 열의 개수 제곱 개의 결과 출력 → A+B, A, B, TOTAL
-- CUBE: 2의 열의 개수 제곱 개의 결과 출력
-- ROLLUP: 열의 개수 + 1 개의 결과 출력
-- 1) A + B = 부서번호 + 직책으로 GROUP BY
-- 2) A = 부서번호로 GROUP BY
-- 3) B = 직책으로 GROUP BY
-- 4) 전체
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL) FROM EMP
GROUP BY CUBE(DEPTNO, JOB);
- 그룹화 데이터의 합계 출력
- A, ROLLUP(B)
→ A+B, A
-- ROLLUP 을 일부 열만 가지고 사용
-- 1) A + B = 부서번호 + 직책으로 GROUP BY
-- 2) A = 부서번호로 GROUP BY
-- 전체는 나오지 않음
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL) FROM EMP
GROUP BY DEPTNO, ROLLUP(JOB);
- 열을 따로 그룹화하여 합계 출력
- GROUPING SETS(A, B) → A, B
-- GROUPING SETS
-- 1) A = 부서번호로 GROUP BY
-- 2) B = 직책으로 GROUP BY
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL) FROM EMP
GROUP BY GROUPING SETS(DEPTNO, JOB);
- LISTAGG( ) : 그룹화 속해 있는 데이터를 가로로 나열 (파이썬의 join과 비슷)
-- 부서 별로 사원 이름을 급여가 높은 순서대로 나열한 다음 이름과 이름 사이에 ', '로 연결시켜 출력
SELECT DEPTNO
, LISTAGG(ENAME, ', ')
WITHIN GROUP(ORDER BY SAL DESC) AS ENAME
FROM EMP
GROUP BY DEPTNO;
- PIVOT( ) : 행을 열로 바꿔서 출력
-- 부서번호를 행에서 열로 바꿈
SELECT * FROM (SELECT DEPTNO, JOB, SAL FROM EMP)
PIVOT(MAX(SAL) FOR DEPTNO IN (10, 20, 30))
ORDER BY JOB;
- UNPIVOT( ) : 열을 행으로 바꿔서 출력
-- UNPIVOT
-- PIVOT 테이블을 다시 UNPIVOT
SELECT * FROM(
SELECT DEPTNO
, MAX(DECODE(JOB, 'ANAYLYST', SAL)) AS ANALYST
, MAX(DECODE(JOB, 'CLERK', SAL)) AS CLERK
, MAX(DECODE(JOB, 'MANAGER', SAL)) AS MANAGER
, MAX(DECODE(JOB, 'PRESIDENT', SAL)) AS PRESIDENT
, MAX(DECODE(JOB, 'SALESMAN', SAL)) AS SALESMAN
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO)
UNPIVOT(SAL FOR JOB IN (ANALYST, CLERK, MANAGER, PRESIDENT, SALESMAN))
ORDER BY DEPTNO, JOB;
4. JOIN
- 크로스 조인 = 교차 조인
- 조인 결과 모든 데이터 조합으로 출력
- 조인 조건이 없어서 생긴 문제
- 등가조인 = 내부조인 = 단순조인
- 여러 테이블과 조인 조건으로 연결
- 테이블 별칭 설정 가능
- 테이블 2개이면 최소 조건 1개 필요(3개면 2개 등등)
-- 열 이름을 구체화하여 작성할 것!
-- 테이블 이름 명시
SELECT A.EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM
, B.DEPTNO, B.DNAME, B.LOC
FROM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPTNO
- 비등가조인
- 등가 조인 이외의 방식
- BETWEEN A AND B
- 비등가조인
- 등가 조인 이외의 방식
- BETWEEN A AND B
- 자체 조인
- 같은 테이블끼리 조인
SELECT A.EMPNO, A.ENAME, A.MGR AS MGR_EMPNO, B.ENAME AS MGR_ENAME
FROM EMP A, EMP B
WHERE B.MGR = A.EMPNO;
- 외부 조인
- 왼쪽 외부 조인
-- 외부조인
-- 기준이 되는 테이블의 행은 모두 다 나와야 함 & 기준이 되지 않는 테이블은 교집합
-- LEFT OUTER JOIN
-- 왼쪽에 있는 테이블이 기준
-- 조건절에서 오른쪽에 있는 테이블.열이름 뒤에 (+)
-- 직속상관 + 직속상관이 없는 사람도 나옴
SELECT A.EMPNO, A.ENAME, A.MGR AS MGR_EMPNO, B.ENAME AS MGR_ENAME
FROM EMP A, EMP B WHERE A.MGR = B.EMPNO (+);
- 오른쪽 외부 조인
-- RIGHT OUTER JOIN
-- 오른쪽에 있는 테이블이 기준
-- 왼쪽에 있는 테이블 이름 뒤에 (+)
-- 조건절에서 왼쪽에 있는 테이블 이룸, 열 이름 뒤에 (+)
-- 부하직원 + 부하직원 없는 사람도 나옴
SELECT A.EMPNO, A.ENAME, A.MGR AS MGR_EMPNO, B.ENAME AS MGR_ENAME
FROM EMP A, EMP B WHERE A.MGR (+) = B.EMPNO;
- NATURAL JOIN
- 등가조인과 같음
- 공통열 명시 x
- 공통열의 테이블 출처 명시 x
-- SQL 99문법으로 조인
-- 등가조인과 결과는 같음
-- 1) NATURAL JOIN
-- 같다는 조건 x & 공통된 열 이름의 테이블 이름 x
SELECT A.EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM
, DEPTNO, B.DNAME, B.LOC
FROM EMP A NATURAL JOIN DEPT B;
- JOIN ~ USING
- 등가조인과 같음
- 공통열 명시해야 함
- 공통열의 테이블 출처 명시 x
-- 2) JOIN USING
-- 같다는 조건 => USING 공통된 열 명시 & 공통된 열 이름의 테이블 이름 x
SELECT A.EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM
, DEPTNO, B.DNAME, B.LOC
FROM EMP A JOIN DEPT B USING (DEPTNO);
- JOIN ~ ON
- 등가조인과 같음
- 공통열 명시
- 공통열 같다는 조건 필요
- 공통열의 테이블 출처 명시
-- 3) JOIN ON
-- 같다는 조건 => USING 공통된 열 명시 & 공통된 열 이름의 테이블 이름 x
SELECT A.EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM
, B.DEPTNO, B.DNAME, B.LOC
FROM EMP A JOIN DEPT B ON (A.DEPTNO = B.DEPTNO);
결과는 모두 같다 !!
- LEFT OUTER JOIN
-- LEFT OUTER JOIN ON
SELECT A.EMPNO, A.ENAME, A.MGR AS MGR_EMPNO, B.ENAME AS MGR_ENAME
FROM EMP A LEFT OUTER JOIN EMP B ON ( A.MGR = B.EMPNO);
2. RIGHT OUTER JOIN
-- RIGHT OUTER JOIN ON
SELECT A.EMPNO, A.ENAME, A.MGR AS MGR_EMPNO, B.ENAME AS MGR_ENAME
FROM EMP A RIGHT OUTER JOIN EMP B ON ( A.MGR = B.EMPNO);
3. FULL OUTER JOIN
-- FULL OUTER JOIN ON
SELECT A.EMPNO, A.ENAME, A.MGR AS MGR_EMPNO, B.ENAME AS MGR_ENAME
FROM EMP A FULL OUTER JOIN EMP B ON ( A.MGR = B.EMPNO);
- 3개 이상의 테이블의 조인
# 기존 조인 방식
FROM TABLE1, TABLE2, TABLE 3
WHERE TABLE1.COL = TABLE2.COL
AND TABLE2.COL = TABLE3.COL
---------------------------------------------
# SQL-99 방식
FROM TABLE JOIN TABLE2 ON (TABLE1.COL = TABLE2.COL)
JOIN TABLE3 ON (TABLE2.COL = TABLE3.COL)
5. 서브쿼리
- 쿼리 안의 쿼리
- 특징
- 연산자와 같은 비교 또는 조회 대상의 오른쪽에, 소괄호로 묶어서 사용
- 대부분의 서브쿼리에서 ORDER BY 절 사용할 수 없음
- 서브쿼리의 열 개수는 메인 쿼리의 비교대상과 같은 자료형과 같은 개수로 지정
- 단일 행 서브쿼리
-- 서브쿼리
-- WHERE 절 뒤에서 사용하는 서브쿼리
-- 단일행 서브쿼리 = 결과가 하나 = 행이 1개
SELECT SAL FROM EMP WHERE ENAME = 'JONES';
SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'JONES')
- 다중 행 서브쿼리
-- 다중행 서브쿼리
SELECT DISTINCT DEPTNO FROM EMP;
-- IN 연산자 = OR 조건 여러 개 = 합집합
SELECT * FROM EMP WHERE DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP);
-- ANY / SOME = 여러 결과 중에 하나라도 만족하면 TRUE
-- = 조건 => OR 조건 여러 개 = 합집합
SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO;
SELECT * FROM EMP WHERE SAL = ANY(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
SELECT * FROM EMP WHERE SAL = SOME(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
-- ALL = 여러 결과 중에서 모두 만족해야 TRUE
-- = 조건 => AND 조건 여러 개이므로 사용 불가, 예) 한 사람이 하나의 급여를 가지고 있음
-- 부서별로 최대 급여 3개 값이 한 사람의 급여와 모두 같다는 조건은 성립할 수 없음
