데이터베이스5-1 서브쿼리(Subquery)

서브쿼리(Subquery)

- 하나의 SQL문에 포함되어 있는 또 다른 SQL문

- SELECT, UPDATE, DELETE, INSERT와 같은 DML문, CREATE TABLE또는 VIEW에서 이용될 수 있다.

- 알려지지 않은 조건에 근거한 값들을 검색하는 SELECT 문장을 작성하는 데 유용하다.

 

*주의사항*

 

서브쿼리는 괄호로 묶어야 한다.	두 종류의 비교 연산자들이 서브쿼리에 사용된다.
단일 행 연산자(=, >, >=, <, <=, < >, !=)	복수 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)
서브쿼리는 연산자의 오른쪽에 나타나야 한다.	서브쿼리에서는 ORDER BY를 사용하지 못한다.

 

*서브쿼리의 유형*

 

단일 행(Sing-Row) 서브쿼리	SELECT문장으로부터 오직 하나의 행을 검색하는 질의
다중 행(Multiple-Row) 서브쿼리	SELECT문장으로부터 하나 이상의 행을 검색하는 질의
다중 열(Multiple-Cplumn) 서브쿼리	SELECT문장으로부터 하나 이상의 컬럼을 검색하는 질의
FORM절상의 서브쿼리(INLINE VIEW)	FROM절 상에 오는 서브쿼리로 VIEW처럼 작용한다.

1. 단일 행(Sing-Row) 서브쿼리

- 오직 한 개의 행(값)을 반환(Return only one row)

- 단일 행 연산자만 사용할 수 있다. (=, >, >=, <, <=, < >, !=)

 

 

-> 이 서브쿼리를 실행 시키면, empno가 7369인 job을 먼저 검색하고 job이 ‘CLERK’인 사원의 이름과 직업을 반환한다. (서브쿼리문 먼저 실행)

 

예제1) emp테이블에서 평균 급여보다 높은 급여를 받는 사원을 선택하라.

> SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

 

예제2) emp테이블에서 jones의 급여보다 많이 받는 사원 중 부서가 20번인 사원을 선택하라.

(1) jones의 급여를 구하기(서브쿼리)

> SELECT sal FROM emp WHERE ename = ‘jones’

 

(2) 전체 코드

> SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = ‘jones’) AND depno = 20;

 

예제3) 담당업무가 manager인 업무를 하는 사원의 평균 급여보다 작게 받는 사원을 선택하라.

(1) 담당업무가 manager인 업무를 하는 사원의 평균 급여 구하기 (서브쿼리)

> SELECT AVG(sal) FROM emp WHERE job = ‘manager’;

 

(2) 전체코드

> SELECT * FROM emp WHERE sal < (SELECT AVG(sal) FROM emp WHERE job = ‘manager’);

 

예제4) 사원테이블에서 사원명, 입사일, 담당업무, 부서코드를 선택하되 ‘ADAMS’와 같은 업무를 하는 사원이거나 ‘SCOTT’과 같은 부서인 사원을 선택하라

(1) ADAMS와 같은 업무를 하는 사원 구하기(서브쿼리)

> SELECT * FROM emp WHERE job = (SELECT job FROM emp WHERE ename = ‘ADAMS’);

 

(2) SCOTT과 같은 부서인 사원 구하기(서브쿼리)

> SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = ‘SCOTT’);

 

(3) 전체 코드

> SELECT ename, hiredate, job, deptno  FROM emp

WHERE job = (SELECT job FROM emp WHERE ename = ‘ADAMS’)

OR deptno = (SELECT deptno FROM emp WHERE ename = ‘SCOTT’);

 

예제5) 사원테이블에서 사원번호가 7521번인 사원과 업무가 같고 급여가 7934번인 사원보다 많은 급여를 받는 사원의 사번, 이름, 업무, 입사일자, 급여를 선택하라.

(1) 사원번호가 7521번인 사람과 업무가 같은 사원 구하기(서브쿼리)

> SELECT * FROM emp WHERE job = (SELECT job FROM emp WHERE empno = 7521);

 

(2) 급여가 7934번인 사원보다 많은 급여를 받는 사원 구하기(서브쿼리)

> SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE empno = 7934);

 

(3) 전체 코드

> SELECT empno, ename, job, hiredate, sal FROM emp

WHERE job = (SELECT job FROM emp WHERE empno = 7521)

AND sal > (SELECT sal FROM emp WHERE empno = 7934);

---

* 서브쿼리에서 그룹함수 사용 *

 

1) 단일행을 반환하는 서브쿼리'

 

-> 단일행을 반환하는 서브쿼리에 그룹함수를 사용해 메인쿼리에서 데이터를 출력할 수 있다.

 

예제1) 사원테이블에서 급여의 평균보다 적은 사원의 사번, 이름, 업무, 급여, 부서번호를 선택해라.

(1) 급여의 평균보다 적은 사원을 구하기(서브쿼리)

> SELECT * FROM emp WHERE sal < (SELECT AVG(sal) FROM emp);

 

(2) 전체 코드

> SELECT empno, ename, job, sal, deptno FROM emp WHERE sal < (SELECT AVG(sal) FROM emp);

 

2) 서브쿼리를 갖는 HAVING절

 

예제1) 사원테이블에서 사원의 최소급여가 30번 부서의 최소급여보다 많이 받는 부서를 선택하라

(1) 부서별 급여의 최소금액 구하기

> SELECT deptno, MIN(sal) FROM emp GROUP BY deptno;

 

(2) 30번 부서의 최소급여 구하기(서브쿼리)

> SELECT MIN(sal) FROM emp WHERE deptno = 30;

 

(3) 전체 코드

> SELECT deptno, MIN(sal) FROM emp GROUP BY deptno

HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 30);

 

예제2) 사원테이블의 사원의 평균급여가 10번부서의 평균급여보다 많이 받는 업무를 하는 업무별 평균급여를 선택하라.

(1) 10번 부서의 평균급여 구하기(서브쿼리)

> SELECT AVG(sal) FROM emp WHERE deptno = 10;

 

(2) 업무별 평균급여 구하기

> SELECT job, AVG(sal) FROM emp GROUP BY job;

 

(3) 전체 코드

> SELECT job, AVG(sal) FROM emp GROUP BY job

HAVING (AVG)sal > (SELECT AVG(sal) FROM emp WHERE deptno = 10);

---

2. 다중 행(Multiple-Row) 서브쿼리

- 서브쿼리의 결과가 2건 이상 반환되는 것(여러 행이 검색되는 쿼리문)

- 반드시 복수 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)와 함꼐 사용해야 한다.

 

IN	서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미한다.
ANY	서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미한다.
ALL	서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미한다.
EXISTS	서브쿼리의 결과에 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미한다.

 

<기본구조> 

 

 

1) IN 연산자의 사용 예제

 

예제1) 부서별로 급여를 가장 많이 받는 사원과 같은 급여를 받는 사원의 사원번호, 사원명, 급여, 부서코드를 출력하라.

(1) 부서별로 급여를 가장 많이 받는 사원 구하기 (서브쿼리)

> SELECT MAX(sal) FROM emp GROUP BY deptno;

 

(2) 부서별 최고 급여와 같은 급여를 받는 사원 구하기

> SELECT * FROM emp WHERE sal IN(SELECT MAX(sal) FROM emp GROUP BY deptno);

 

(3) 전체 코드

> SELECT empno, ename, sal, deptno FROM emp WHERE sal IN(SELECT MAX(sal) FROM emp GROUP BY deptno);

 

예제2) 업무별로 최대 급여를 받는 사원과 같은 급여를 받는 사원의 사원번호와 이름, 업무, 급여를 출력하라.

(1) 업무별로 최대 급여를 받는 사원 구하기 (서브쿼리)

> SELECT MAX(sal) FROM emp GROUP BY job;

 

(2) 업무별 최고 급여와 같은 급여를 받는 사원 구하기

> SELECT * FROM emp WHERE sal IN(SELECT MAX(sal) FROM emp GROUP BY job);

 

(3) 전체 코드

> SELECT empno, ename, job, sal FROM emp WHERE sal IN(SELECT MAX(sal) FROM emp GROUP BY job);

 

2) ANY 연산자의 사용 예제

- ANY 연산자는 서브쿼리의 결과값 중 어느 하나의 값이라도 만족이 되면 결과값을 반환한다.

 

예제1) 업무가 'SALESMAN'인 사원의 최소급여보다 많으면서 부서번호가 20번이 아닌 사원의 이름과 급여, 부서코드를 출력하라.

(1) 업무가 ‘SALESMAN’인 사원의 최소급여를 구하기(서브쿼리)

> SELECT MIN(sal) FROM emp WHERE job = ‘SALESMAN’;

 

(2) 전체 코드

> SELECT ename, sal, deptno FROM emp WHERE deptno != 20 AND sal > ANY(SELECT MIN(sal) FROM emp WHERE job = ‘SALESMAN’;

 

 

예제2) 사원테이블의 사원중 KING속한 부서의 사원보다 늦게 입사한 사원의 사원명, 업무, 급여, 입사일을 선택하라. 

(1) KING이 속한 부서의 입사일을 구한다. (서브쿼리)

> SELECT hiredate FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = ‘KING’);

 

(2) 전체 코드

> SELECT ename, job, sal, hiredate FROM emp

WHERE hiredate > ANY (SELECT hiredate FROM emp

WHERE deptno = (SELECT deptno FROM emp WHERE ename = ‘KING’

 

 

3) ALL 연산자의 사용 예제

- ALL 연산자는 서브쿼리의 결과값 중 모든 결과 값이 만족 되어야 만 결과값을 반환한다.

 

예제1) 업무가 'SALESMAN'인 사원의 급여보다 많으면서 부서번호가 20번이 아닌 사원의 이름과 급여를 출력하라

 

(1) 업무가 ‘SALESMAN’인 사원의 최대급여 구하기(서브쿼리)

> SELECT sal FROM emp WHERE job = ‘SALESMAN’

 

(2) 전체 코드

> SELECT ename, sal FROM emp WHERE deptno != 20 AND sal > ALL (SELECT sal FROM emp WHERE job = ‘SALESMAN’);

 

 

ALL과 ANY의 정리        

 

사용	의미	같은 표현
컬럼 > ANY	가장 작은 값보다 크다	컬럼 > MIN
컬럼 < ANY	가장 큰 값보다 작다.	컬럼 < MAX
컬럼 > ALL	가장 큰 값보다 크다.	컬럼 > MAX
컬럼 < ALL	가장 작은 값보다 작다.	컬럼 < MIN

 

4) EXISTS 연산자의 사용 예제

 

- 두 테이블간의 결과에 어떤 값이 존재하는 지 확인하는 연산자. (존재를 판가름)

- 서브쿼리로부터 결과값이 존재(exist)하면 true를 반환하고 존재하지 않으면 false를 반환.

- EXISTS 뒤에는 서브쿼리만 올 수 있다.

 

SELECT 컬럼명  FROM 테이블명 WHERE EXISTS ( 서브쿼리 );   SELECT 컬럼명 FROM 테이블명 WHERE EXISTS ( SELECT 1 FROM 테이블명 WHERE 조건식 );

 

예제) 사원테이블에서 사원번호, 이름, 급여를 선택하되 사원을 관리할 수 있는 사원의 정보를 선택한다.

> SELECT * FROM emp e WHERE EXISTS (SELECT empno FROM emp WHERE e.empno = mgr);

 

-> e와 emp는 데이터가 같은 두개의 테이블이다. (자기의 데이터를 복제해서 여러 개의 테이블이 있는 것처럼 사용한다.)

-> emp를 e라는 이름(별명)을 붙여서 사용했다. (별명은 짧을수록 좋다)

 

 

[문제] emp테이블의 사원 중 사원명, 담당업무, 입사일 급여를 선택하되 SCOTT이 속한 부서의 평균급여보다 많이 받는 사원과 MANAGER업무를 하는 사원을 선택하라. 단, 사원명을 오름차순으로 정렬하여 출력하라.

1) SCOTT이 속한 부서의 평균 급여를 구하기(서브 쿼리)

> SELECT AVG(sal) FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = ‘SCOTT’);

 

2) 전체 코드

> SELECT ename, job, hiredate, sal FROM emp

WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = ‘SCOTT’))

OR job = ‘MANAGER ORDER BY ename ASC;

 

---

2. 다중 행(Multiple-Row) 서브쿼리

- 결과값이 두 개 이상의 컬럼을 반환하는 서브쿼리이다. (여러 개의 컬럼을 검색하는 서브 쿼리)

- 다중 열 서브 쿼리는 주로 ‘IN’을 사용한다.

- 반드시 비교 대상 컬럼과 1:1 대응되어야 한다.

 

<기본구조>

 

 

1) Pairwise(쌍비교) Subquery

 

예제1) 부서번호가 30인 사원의 급여와, 부서번호가 같은 사원의 사원번호, 급여, 부서번호를 출력하라.

> SELECT empno, sal, deptno FROM emp WHERE (sal, deptno) IN(SELECT sal, deptno FROM emp WHERE deptno = 30;

 

예제2) 업무별로 최소 급여를 받는 사원의 사번, 이름, 업무, 부서번호를 출력하세요. 단 업무별로 정렬하세요.

(1) 업무별로 최소 급여를 구하기

> SELECT job, MIN(sal) FROM emp GROUP BY job;

 

(2) 전체 코드

> SELECT empno, ename, job, deptno FROM emp WHERE (job, sal) IN(SELECT job, MIN(sal) FROM emp GROUP BY job) ORDER BY job ASC

---

5. FROM절 상의 서브쿼리(INLINE VIEW)

- 서브쿼리가 FROM 절 안에서 사용되는 경우, 해당 서브쿼리를 ‘인라인 뷰’라고 한다.

- FROM 절에서 사용된 서브쿼리의 결과가 하나의 테이블에 대한 View처럼 사용된다.

 

- 하나의 테이블에서 자료의 양이 많을 경우 FROM 절에 테이블 전체를 기술해 사용하면 효율이 떨어질 수 있기 때문에 필요한 행과 열만 선택해 FROM 절에 기술하면 효율적인 검색을 할 수 있다.

 

<View> 

 

-> VIEW는 테이블과 동일한 역할을 수행하지만, 데이터를 직접 가지지 않고 테이블에 대한 SELECT 값만 가지고 있다. -> 저장장치 내에 물리적으로 존재하지 않지만 사용자에게 있는 것처럼 보여지는 가상의 테이블

 

*FROM 절에 서브쿼리 문 생성하기 (테이블 명 대신 서브쿼리를 사용해 테이블을 하나 더 생성)*

> SELECT * FROM (SELECT empno, ename, sal, hiredate FROM emp) e;

 

-> 테이블 명이 없기 때문에 별명(ALIAS) e를 테이블이름으로 적어준다. 테이블의 컬럼은 사원번호, 사원명, 급여, 입사일

 

예시) 위에서 새로 생성된 e테이블의 사원명과 급여를 선택해서 출력하라.

> SELECT e.ename, e.sal FROM (SELECT empno, ename, sal, hiredate FROM emp) e;

 

예시) emp테이블에서 부서번호가 30인 부서의 사원번호, 사원명, 급여, 입사일 컬럼이 들어간 테이블을 생성한 뒤 사원명과 급여를 선택하라.

> SELECT e.ename, e.sal FROM (SELECT empno, ename, sal, hiredate FROM emp WHERE deptno = 30) e;