Subquery

1. Subquery

- SQL문을 실행하는데 필요한 데이터를 추가로 조회하기 위해 SQL문 내부에서 사용하는 SELECT문을 의미한다.

- 반대로, 서브쿼리의 결과 값을 사용하여 기능을 수행하는 영역은 Main query라고 부른다.

 

1.1 특징

- 서브쿼리는 연산자와 같은 비교 또는 조회 대상의 오른쪽에 놓이며 괄호 ()로 묶어서 사용한다.

- 특수한 몇몇 경우를 제외한 대부분의 서브쿼리에서는 ORDER BY절을 사용할 수 없다.

- 서브쿼리의 SELECT절에 명시한 컬럼은 메인쿼리의 비교 대상과 같은 자료형과 같은 개수로 지정해야 한다.

- 서브쿼리에 있는 SELECT문의 결과 행 수는 함께 사용하는 메인쿼리의 연산자 종류와 호환 가능해야 한다.

 

2. 단일행 서브쿼리

- 실행 결과가 단 하나의 행으로 나오는 서브쿼리를 의미한다.

- 서브쿼리에서 출력되는 결과가 하나이므로 메인쿼리와 서브쿼리 결과는 단일행 연산자(<, <=, >, >=, =, <>, ^=, !=)를 사용하여 비교한다.

/* 예시1 */
SELECT *
FROM EMP
WHERE HIREDATE < (SELECT HIREDATE FROM EMP WHERE ENAME='SCOTT');

/* 예시2 */
SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND E.DEPTNO=20
AND E.SAL > (SELECT AVG(SAL) FROM EMP);

 

3. 다중행 서브쿼리

- 실행 결과 행이 여러 개로 나오는 서브쿼리를 의미한다. 그렇기 때문에 단일행 연산자는 사용할 수 없고 다중행 연산자를 사용해야 메인쿼리와 비교할 수 있다.

- 다중행 연산자 종류

종류	설명 및 예시
IN	- 메인쿼리의 데이터가 서브쿼리의 결과 중 하나라도 일치한 데이터가 있다면 TRUE - SELECT * FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
ANY, SOME	- 메인쿼리의 조건식을 만족하는 서브쿼리의 결과가 하나 이상이면 TRUE - SELECT * FROM EMP WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO=30) ORDER BY SAL, EMPNO; - SELECT * FROM EMP WHERE SAL = SOME (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
ALL	- 메인쿼리의 조건식을 서브쿼리의 결과 모두가 만족하면 TRUE - SELECT * FROM EMP WHERE SAL < ALL (SELECT FROM EMP WHERE DEPTNO = 30);
EXISTS	- 서브쿼리의 결과가 존재(행이 1개 이상일 경우)하면 TRUE, 존재하지 않으면 FALSE가 된다. - FALSE 조건의 서브쿼리를 실행하면 결과 데이터에 아무 행도 출력되지 않는다. - SELECT * FROM EMP WHERE EXISTS (SELECT DNAME FROM DEPT WHERE DEPTNO = 50);

 

3.1 서브쿼리에 ANY, SOME 연산자를 사용한 경우와 IN 연산자를 사용한 경우 비교

- 메인쿼리와 값을 비교할 때 ANY 및 SOME 연산자를 등가 비교 연산자(=)와 함께 사용하면 IN 연산자와 정확히 같은 기능을 수행한다.

- IN 연산자가 알아보기도 편하고 글자 수도 적기 때문에 IN 연산자 사용이 더 선호되는 편이다.

/* ANY 연산자 + 등가 비교 연산자(=) */
SELECT *
FROM EMP
WHERE SAL = ANY (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

/* IN 연산자 */
SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

/* 같은 결과가 나온다. */

 

3.2 서브쿼리에 ANY, SOME 연산자를 사용한 경우와 MAX, MIN 함수를 사용한 경우 비교

/* < ANY 연산자 */
SELECT *
FROM EMP
WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO=30)
ORDER BY SAL, EMPNO;

/* MAX 함수 */
SELECT *
FROM EMP
WHERE SAL < (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30)
ORDER BY SAL, EMPNO;

/* 같은 결과가 나온다. */

 

4. 다중열 서브쿼리

- 서브쿼리의 SELECT절에 비교할 데이터를 여러 개 지정하는 방식이다.

- 메인쿼리에 비교할 열을 괄호로 묶어 명시하고 서브쿼리에서는 괄호로 묶은 데이터와 같은 자료형 데이터를 SELECT절에 명시하여 사용한다.

/* 예시 */
SELECT *
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO);

 

5. Inline View

- FROM절에서 사용하는 서브쿼리를 의미한다.

- 특정 테이블 전체 데이터가 아닌 SELECT문을 통해 일부 데이터를 먼저 추출해 온 후 별칭을 주어 사용한다.

- FROM절에 직접 테이블을 명시하여 사용하기에는 테이블 내 데이터 규모가 너무 크거나 현재 작업에 불필요한 열이 너무 많아 일부 행과 열만 사용하고자 할 때 유용하다.

- 하지만 FROM절에 너무 많은 서브쿼리를 지정하면 가독성이나 성능이 떨어질 수 있다.

/* 예시 */
SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM (SELECT * FROM EMP WHERE DEPTNO=10) E10, (SELECT * FROM DEPT) D
WHERE E10.DEPTNO=D.DEPTNO;

 

5.1 WITH절

WITH
[별칭1] AS (SELECT문1),
[별칭2] AS (SELECT문2),
...
[별칭n] AS (SELECT문n)

- 오라클 9i부터 제공한다.

- 인라인 뷰에서 FROM절에 너무 많은 서브쿼리가 지정되어 가독성이나 성능이 떨어지는 것을 방지하기 위해 사용한다.

- 메인쿼리가 될 SELECT문 안에서 사용할 서브쿼리와 별칭을 먼저 지정한 후 메인쿼리에서 사용한다.

/* 예시 */
WITH
E10 AS (SELECT * FROM EMP WHERE DEPTNO=10),
D AS (SELECT * FROM DEPT)
SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM E10, D
WHERE E10.DEPTNO=D.DEPTNO;

 

6. Correlated Subquery

- 상호 연관 서브쿼리라고 불린다.

- 메인쿼리에 사용한 데이터를 서브쿼리에서 사용하고 서브쿼리의 결과 값을 다시 메인쿼리로 돌려주는 방식이다.

- 성능을 떨어뜨리는 원인이 될 수 있어 사용 빈도가 높지는 않다.

/* 예시 */
SELECT *
FROM EMP E1
WHERE SAL > (SELECT MIN(SAL) FROM EMP E2 WHERE E2.DEPTNO=E1.DEPTNO)
ORDER BY DEPTNO, SAL;

 

7. Scalar Subquery

- 스칼라 서브쿼리라고 불린다.

- SELECT절에 하나의 열 영역으로서 결과를 출력할 수 있다.

- SELECT절에 명시하는 서브쿼리는 반드시 하나의 결과만 반환하도록 작성해야 한다.

/* 예시 */
SELECT EMPNO, ENAME, JOB, SAL,
	(SELECT GRADE FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL) AS SALGRADE,
	DEPTNO,
	(SELECT DNAME FROM DEPT WHERE E.DEPTNO=DEPT.DEPTNO) AS DNAME
FROM EMP E;

 

참고

- 『오라클로 배우는 데이터베이스 입문』