SQL - DML 데이터 조작어 서브쿼리, 셀프 조인,

서브쿼리

서브쿼리(subquery)란 다른 쿼리 내부에 포함되어 있는 SELETE 문을 의미한다.

 

 

 

단일값

 

담당 매니저(MGR)의 이름이 'KING' 인 사람의 정보를 구하려고 한다.

이 때 KING의 사번을 모르기 때문에 KING의 사번을 구하는 쿼리를 서브쿼리로 두고

SELECT empno, ename
FROM emp
WHERE ename='KING';

다음

SELECT * 을 통해 정보를 구한다.

이 때 서브쿼리는 () 으로 감싼다.

SELECT * FROM emp
WHERE mgr=
(SELECT empno
FROM emp
WHERE ename='KING');

담당 매니저(MGR)의 이름이 'KING' 인 사람의 정보

 

 

 

---

다중값

IN

 

학년별 최대 몸무게를 가진 학생 정보

--서브쿼리 속성 2개

SELECT grade, MAX(weight)
FROM student
GROUP BY grade

SELECT *
FROM student
WHERE (grade,weight) IN (SELECT grade, MAX(weight)
FROM student
GROUP BY grade);

 

---

EXITES, ANY, ALL

 

EXISTS

서브쿼리에서 행의 존재를 테스트

SELECT COUNT(*)
FROM student
GROUP BY grade
HAVING COUNT(*)>4;

학생 수가 5 이상인 값들

SELECT *
FROM student
WHERE
EXISTS (SELECT COUNT(*)
FROM student
GROUP BY grade
HAVING COUNT(*)>4);

 

 

---

ANY

조건에서
만족하는 값이 하나라도 있으면 만족

SELECT sal FROM emp WHERE job='SALESMAN';

1600, 1250, 1500 값 중 어느 하나라도 작은 값이 있으면 조건을 만족하게 된다.

SELECT ename, job, sal
FROM emp
WHERE sal < ANY(SELECT sal FROM emp WHERE job='SALESMAN');

 

 

 

---

ALL

모두 만족하는 것

SELECT sal
FROM emp
WHERE deptno=30;

 

서브쿼리의 sal 값 중 모든 값을 만족하는 즉 950보다 작은 값이 조건에 만족한다.

SELECT  ename, job, sal
FROM emp
WHERE sal < ALL(SELECT sal
FROM emp
WHERE deptno=30);

 

 

 

---

 

셀프 조인

자신의 속성 참조하고 싶을 때

SELECT tableA.col, tableB.col
FROM empolyees AS tableA
JOIN employees AS tableB ON
tableA.some_col = tableB.other_col

 

 

 

SELECT empno, ename, job, mgr
FROM emp;

매니저의 이름이 'KING' 인 사원의 정보 구하기

SELECT E.ename, E.JOB
FROM emp E, emp M
WHERE E.mgr=M.empno AND M.ename='KING';

SELECT E.ename, E.JOB
FROM emp E
JOIN emp M
ON E.mgr=M.empno 
WHERE M.ename='KING'

emp M 을 매니저 정보 테이블이라고 생각하면 편하다.

서브쿼리 이용

SELECT ename, job FROM emp
WHERE mgr=
(SELECT empno
FROM emp
WHERE ename='KING');

 

 

 

---

 

 

인라인 뷰

테이블 내 데이터 규모가 너무 크거나 현재 작업에 불필요한 열이 너무 많아 일부 행과 열만 사용하고자 할 때 유용
조인시 테이블의 필요한 부분만 가져와 계산 효율 상승

서브쿼리 안해도됨

 

 

 

부서별 연봉을 가장 많이 받는 사원의 번호, 부서명, 연봉 출력

 

 

우선 부서별 최대 연봉 출력

SELECT deptno, MAX(sal) sal
FROM emp 
GROUP BY deptno

emp 테이블에서 필요한 정보만 가져온다.

부서별 최대 연봉

부서 정보 출력

select * from dept;

부서 정보

필요한 정보는 사원의 번호(DEPTNO), 부서명(DNAME), 연봉 출력(LOC) 이기 떄문에

deptno 를 기준으로 조인하는데 인라인 뷰를 이용해서

emp 테이블에서 필요한 정보만 가져오게 하는 것이다.

 

마지막으로 조인

SELECT E.deptno, D.dname, E.sal
FROM (SELECT deptno, MAX(sal) sal
        FROM emp 
        GROUP BY deptno) E, dept D
WHERE E.deptno=D.deptno;

인라인 뷰 결과

 

 

 

---

 

 

순번 컬럼 생성

ROWNUM 컬럼명으로 순번을 나타내는 열 생성

SELECT ROWNUM, E.*
FROM (SELECT *
FROM emp
ORDER BY ename DESC) E;