9일차(1)/DB_Oracle(5) : 단일행 서브쿼리

9일차(1)/DB_Oracle(5) : 서브쿼리

 

 

Q) JOIN 작업이 불편한데 한 테이블에 다 넣을수는 없을까?

 

A) emp와 dept 테이블을 합쳐 emp테이블에 값을 추가한다면 dname, loc라는 칼럼이 추가로 들어가야 한다.

하지만 만약 직원이 1200명이라면, 테이블이 분리되어있을때는 4개의 row만 있으면 되는데 같이 넣으려면 1200개의 row가 새롭게 생겨난다. 용량, 비용적인 면에서 분리하는 것이 효율적이다.

또, 부서가 수정된다면 dept 테이블에서는 하나의 row만 수정하면 되는데 emp에서는 수백개의 row를 수정해야 한다.

표를 합쳐놓는다면 SELECT하기엔 편하지만, 관리 차원에서는 비효율적이다.

 

---

- 서브쿼리 : 하나의 SQL 문장절에 포함된 또다른 SELECT 문장, 따라서 두번 질의를 해야 얻을수 있는 결과를 한번의 질의로 해결이 가능하게 하는 쿼리

 

- 보통 메인쿼리(=Outer Query)보다 서브쿼리(=Inner Query)가 먼저 실행된다.

 

1) 단일행 서브쿼리

- SELECT를 한 위치에는 어떤 result 테이블이 만들어진다.
 물리적으로 존재하는 테이블은 아니지만 메모리상에 잠깐 생겨나는 것. 현재 12*4짜리 테이블이 만들어진 것이다.

 

- 이 결과 테이블로부터 무언가를 다시 SELECT할 수 있다.
 FROM절 다음에 어떤 테이블명만 오는것이 아니라 어떤 SELECT문도 올 수 있다.

 

SELECT XXX
FROM (SELECT YY FROM ZZ)

SELECT a, (SELECT b)
FROM (SELECT c FROM d)
WHERE e = (SELECT f FROM g)

- SELECT문은 결과 테이블을 만들어내는 작업이므로,
 그 결과에 대해서 다시 SELECT 한 것이다. (결과 내 재검색 기능과 비슷함)

 

- 이런 SELECT문 안의 또다른 SELECT문을 서브쿼리라고 부른다.
 서브쿼리가 올 수 있는 위치는 SELECT문의 칼럼이 위치하는 곳이다.

 

 

- 결과를 = 으로 비교하기 위해서는 반드시 서브쿼리의 결과값이 1행1열 이어야 한다.
 여러 개의 column, row 가 들어가면 비교가 되지 않는다.(오류 발생)

- WHERE절뿐만 아니라 FROM절 안에 들어가는 SELECT문도 마찬가지!

 

---

 

예제1) emp 테이블에 근무하는 사원의 급여(sal)을 조사해서 봉급에 대해 오름차순 정렬을 하고,
 앞에서부터 4명씩 분류해서 1그룹,2그룹,3그룹을 필요에 따라서 SELECT하고 싶다면?

SELECT ename, sal
FROM emp
ORDER BY sal ASC;

- 사원명, 급여를 오름차순 정렬한 것

 

SELECT *
FROM(
	SELECT ename, sal
	FROM emp
	ORDER BY sal ASC);

- 오류가 발생하지 않는다. FROM절에도 select문이 들어갈 수 있다.

- 만약 1열에 SELECT ename, sal, deptno 넣는다면 오류 발생 (deptno는 저 결과테이블에 없으므로)

 

- ROWNUM() : 자동으로 행번호를 부여해주는 함수

 ORDER로 정렬한 후에 순번을 부여한다!

 이후에 1-4, 5-8, 9-12를 그룹화하기 편하다!!

 

SELECT ename, sal, ROWNUM AS rnum
	FROM 
		(SELECT ename, sal
		FROM emp
		ORDER BY sal ASC)
WHERE rnum BETWEEN 1 AND 4

- 이렇게 작성하면 오류가 발생한다. from>where>select 순서로 실행되므로.

- rownum은 select하는 시점에 부여하므로, 실행 순서를 고려해야 한다.

(따라서 select에서 rownum이라는 칼럼을 먼저 만든 후에 이후의 WHERE절로 넘어갈 필요가 있다.)

 

- 결과를 다시 괄호로 묶고 이 대상을 다시 SELECT하면 된다.

- 이를 서브쿼리라고 부른다.

 서브쿼리1(정렬), 서브쿼리2(rnum 부여), 메인쿼리(원하는 ROW 빼내기)

 

SELECT *
FROM
	(SELECT ename, sal, ROWNUM AS rnum
	FROM 
		(SELECT ename, sal
		FROM emp
		ORDER BY sal ASC))
WHERE rnum BETWEEN 1 AND 4;

 

---

 

 

예제1) SMITH의 근무부서 찾기
1) 두번의 질의로 작성

SELECT deptno FROM emp
WHERE ename='SMITH';

SELECT dname FROM dept
WHERE deptno=20;

 

2) 서브쿼리로 작성

**단, 서브쿼리를 쓰려면 여러개의 colums, rows가 되면 안된다. 값이 하나만 나와야 함!!!

SELECT dname
FROM dept
WHERE deptno=(SELECT deptno FROM emp WHERE ename='SMITH');

 

---

 

예제2) 'ALLEN'의 부서사람들 출력

SELECT ename, deptno
FROM emp
WHERE deptno=(SELECT deptno FROM emp WHERE ename='ALLEN');

- 부서번호 일치하도록

 

예제3) 'ALLEN'과 직업이 같은 부서사람들의 사원번호, 이름, 직업 출력

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

 

예제4) 'ALLEN' 의 급여와 동일하거나 더 많이 받는 사원의 이름과 급여 출력

SELECT ename, sal
FROM emp
WHERE sal >=(SELECT sal FROM emp WHERE ename='ALLEN');

예제5) 'DALLAS' 에서 근무하는 사원의 이름, 부서번호 출력

SELECT ename, deptno
FROM emp
WHERE deptno=(SELECT deptno FROM dept WHERE loc='DALLAS');

예제6) 'SALES' 부서에서 근무하는 모든 사원의 이름과 급여를 출력

SELECT ename, sal
FROM emp
WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES');

 

예제7) 직속 상관이 'KING' 인 사원의 이름과 급여

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

 

- = 으로 비교하려면 행,열이 무조건 한개씩만 나와야한다!!!

- 위와 같이 OR조건으로 서브쿼리에서 여러 개의 값이 나왔을 경우 오류 발생

오류) row가 많으면: 단일행 하위 질의에 2개 이상의 행이 리턴되었습니다.

오류) column이 많으면: 값의 수가 너무 많습니다.