[SQL] 서브쿼리 subquery

1. 서브쿼리 subquery

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

- 서브쿼리의 결과 값을 사용하여 기능을 수행하는 영역은 메인쿼리 main query 라고 부른다

SELECT  조회할 칼럼
FROM	조회할 테이블
WHERE 	조건식 (	SELECT  조회할 칼럼
				  FROM	조회할 테이블
				  WHERE 	조건식 )
                  
-- 1. 이름이 JONES인 사원의 급여 출력하기
SELECT 	SAL
FROM 	EMP
WHERE	ENAME = 'JONES';

-- 2. 급여가 2975보다 높은 사원 정보 출력하기
SELECT  *
FROM 	EMP
WHERE	SAL > 2975;

-- 3. 서브쿼리로 JONES보다 높은 급여 받는 사원 정보 출력하기
SELECT  *
FROM 	EMP
WHERE	SAL > ( SELECT 	SAL
				FROM	EMP
				WHERE	ENAME = 'JONES');

  (1) 사원 이름이 JONES 인 사원의 급여는 2975

  (2) 급여가 2975보다 높은 사원 정보 출력하기

  (3) 서브쿼리로 JONES보다 높은 급여 받는 사원 정보 출력하기 ( = (1) + (2) )

 

2. 단일행 서브쿼리 single-row subquery

- 실행 결과가 단 하나의 행으로 나오는 서브쿼리

- 서브쿼리에서 출력되는 결과가 하나이므로 메인쿼리와 서브쿼리의 결과는 단일행 연산자를 사용하여 비교

- 단일행 연산자 ( '>' : 초과  |  '>=' : 이상  |  '=' : 같음  | '<=' : 이하  |  '<' : 미만  |  '<>' '^=' '!=' : 같지 않음 ) 

- 서브쿼리의 예시로 사용된 JONES라는 이름으로 단일행 서브쿼리를 사용하는 것은 이름이 중복이 가능하기 때문에 나중에 문제가 발생할 수 있다

-- 1. 날짜형 데이터
SELECT	*
FROM	EMP
WHERE	HIREDATE < ( SELECT HIREDATE
					   FROM EMP
                   	  WHERE ENAME = 'SCOTT' );
                      
-- 2. 단일행 서브쿼리와 함수
SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL, D.DEPTNO, D.DNAME, D.LOC
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
   AND E.DEPTNO = 20
   AND E.SAL < ( SELECT AVG(SAL)
 				   FROM EMP );

  (1) 서브쿼리 결과 값이 날짜(DATE) 자료형일 때도 단일행 서브쿼리로서 사용 가능

  (2) 서브쿼리에서 특정 함수를 사용한 결과 값이 하나일 때 단일행 서브쿼리로서 사용 가능

 

3. 다중행 서브쿼리 multiple-row subquery

- 실행 결과 행이 여러 개로 나오는 서브쿼리

- 단일행 연산자는 사용할 수 없고 다중행 연산자를 사용해야 메인쿼리랑 비교가 가능

 

  3-1. 다중행 연산자 IN

- 메인쿼리의 데이터가 서브쿼리의 결과 중 하나라도 일치한 데이터가 있다면 true

- WHERE 절의 연산자 IN과 같은 역할을 한다

-- 1.
SELECT *
  FROM EMP
 WHERE DEPTNO IN (20, 30);
 
 -- 2.
 SELECT *
  FROM EMP
 WHERE SAL IN ( SELECT MAX(SAL)
 				  FROM EMP
              GROUP BY DEPTNO );

  (1) DEPTNO 가 20이거나 30인 EMP 테이블의 모든 행 출력

  (2) SAL 이 EMP 테이블의 각 DEPTNO 마다의 MAX값과 같은 EMP 테이블의 모든 행 출력

 

  3-2. 다중행 연산자 ANY, SOME

- 메인쿼리의 조건식을 만족하는 서브쿼리의 결과가 하나 이상이면 true

-- 1. = ANY 연산자 사용하기
SELECT *
  FROM EMP
 WHERE SAL = ANY ( SELECT MAX(SAL)
 					 FROM EMP
                 GROUP BY DEPTNO );
                 
-- 2. = SOME 연산자 사용하기
SELECT *
  FROM EMP
 WHERE SAL = SOME ( SELECT MAX(SAL)
 			 		  FROM EMP
                  GROUP BY DEPTNO );

  (1)(2) 서브쿼리의 결과값 중 하나만 조건식을 만족하면 출력된다. ANY 연산자와 SOME 연산자를 등가 비교 연산자(=)와 함께 사용하면 IN 연산자와 정확히 같은 기능을 수행

-- 3. DEPTNO 가 30인 부서 사원들의 급여보다 적은 급여를 받는 사원 정보 출력 
SELECT	  *
  FROM	  EMP
 WHERE 	  SAL < ANY ( SELECT SAL
 				        FROM EMP
                       WHERE DEPTNO = 30 )
 ORDER BY SAL, DEPTNO;            

-- 4.
SELECT    *
  FROM    EMP
 WHERE    SAL < ( SELECT MAX(SAL)
 			     FROM EMP
             GROUP BY DEPTNO )
 ORDER BY SAL, DEPTNO;  
 
 -- 5. DEPTNO 가 30인 부서 사원들의 급여보다 많은 급여를 받는 사원 정보 출력 
SELECT	  *
  FROM	  EMP
 WHERE 	  SAL > ANY ( SELECT SAL
 				        FROM EMP
                       WHERE DEPTNO = 30 )
 ORDER BY SAL, DEPTNO;            

  (3) < ANY 연산자는 메인 쿼리의 조건식인 SAL 값이 서브쿼리의 결과 값인 DEPTNO가 30인 부서 사원들의 급여들 보다 적으면 true 를 반환하여 출력된다. 즉 SAL 값이 서브쿼리의 결과 값 중 가장 큰 값과 비교하여 작으면 출력한다. 예를들어 서브쿼리의 결과가 3000, 2800, 2500 이라고 했을 때 2500보다 많더라도 3000보다만 작으면 ANY 연산자의 정의에 따라 true를 반환하기 때문

  (4) (3)번에 의해 < ANY 연산자를 사용한 것과 ANY 연산자 없이 < 연산자와 서브쿼리에서 MAX 함수를 사용한 결과가 같다

  (5) > ANY 연산자는 메인 쿼리의 조건식인 SAL 값이 서브쿼리의 결과 값인 DEPTNO가 30인 부서 사원들의 급여들 보다 많으면 true 를 반환하여 출력된다. 즉 SAL 값이 서브쿼리의 결과 값 중 가장 작은 값과 비교하여 작으면 출력한다. 예를들어 서브쿼리의 결과가 3000, 2800, 2500 이라고 했을 때 3000보다 작더라도 2500보다만 크면 ANY 연산자의 정의에 따라 true를 반환하기 때문

 

  3-3. 다중행 연산자 ALL

- ANY, SOME 연산자와 달리 메인쿼리의 조건식을 서브쿼리의 결과 모두가 만족하면 true

-- 1. DEPTNO 가 30인 부서 사원들의 급여보다 적은 급여를 받는 사원 정보 출력 
SELECT	  *
  FROM	  EMP
 WHERE 	  SAL < ALL ( SELECT SAL
 				        FROM EMP
                       WHERE DEPTNO = 30 );    

-- 2. DEPTNO 가 30인 부서 사원들의 급여보다 많은 급여를 받는 사원 정보 출력 
SELECT	  *
  FROM	  EMP
 WHERE 	  SAL > ALL ( SELECT SAL
 				        FROM EMP
                       WHERE DEPTNO = 30 );             

  (1) < ALL 연산자는 서브쿼리의 모든 결과값보다 작은 값을 가진 메인쿼리의 행만 출력된다. 즉 서브쿼리의 결과 중 최솟값보다 작은 값을 가진 데이터만 출력된다

  (2)  > ALL 연산자는 서브쿼리의 모든 결과값보다 큰 값을 가진 메인쿼리의 행만 출력된다. 즉 서브쿼리의 결과 중 최댓값보다 큰 값을 가진 데이터만 출력된다

 

  3-4. 다중행 연산자 EXISTS

- 서브쿼리의 결과가 존재하면 ( 즉, 행이 1개 이상일 경우 ) true

- 특정 서브쿼리 결과 값의 존재 유무를 통해 메인쿼리의 데이터 노출 여부를 결정해야 할 때 사용

 

4. 다중열 서브쿼리 multiple-column subquery

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

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

-- 1.
SELECT *
  FROM EMP
 WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL)
                           FROM EMP
					   GROUP BY DEPTNO );

  (1) 메인쿼리 조건절의 (DEPTNO, SAL) 과 서브쿼리의 SELECT 절의 DEPTNO, MAX(SAL) 이 같은 자료형

 

5. FROM 절에 사용하는 서브쿼리 : 인라인 뷰 inline view 와 WITH 절

- FROM 절에 사용하는 서브쿼리는 인라인 뷰 inline view 라고도 부른다

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

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

- FROM 절에 너무 많은 서브쿼리를 지정하면 가독성이나 성능이 떨어질 수 있기 때문에 경우에 따라 WITH 절 사용

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

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

-- 1. 인라인 뷰 사용하기
SELECT E10.EMONO, 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;

-- 2. WITH 절 사용하기
WITH
E10 AS (SELECT * FROM EMP WHERE DEPTNO = 10),
D   AS (SELECT * FROM DEPT)
SELECT E10.EMONO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
  FROM E10, D
 WHERE E10.DEPTNO = D.DEPTNO;

  (1) 인라인 뷰를 사용하여 FROM 절 안에서 서브쿼리 사용

  (2) WITH 절을 사용하여 메인쿼리 시작 전에 서브쿼리와 별칭을 미리 지정

 

6. SELECT 절에 사용하는 서브쿼리 : 스칼라 서브쿼리 scalar subquery

- SELECT 절에 사용하는 서브쿼리는 스칼라 서브쿼리 scalar subquery 라고도 부른다

- 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;