SQL - 서브쿼리

 

1. 서브쿼리란?

 

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

 - 서브쿼리 값을 사용하여 기능을 수행하는 영역은 메인쿼리라고 부릅니다. 

SELECT 조회할 열
FROM 조회할 테이블
WHERE 조건식 (SELECT 조회할 열 FROM 조회할 테이블 WHERE 조건식)
-- 가장 빈번하게 사용되는 패턴

ex) 'JONES'의 최고 급여가 2975일때 이것보다 더 높은 급여를 받는 사원 정보 출력 

SELECT ENAME, SAL
FROM EMP 
WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'JONES')

*중요한 것은 필요한 조건을 어떤 서브쿼리(SELECT)를 통해 나누어 처리할 지를 결정하는 것 

 

> 서브쿼리의 특징

 

1. 연산자와 같은 비교 또는 조회 대상의 오른쪽에 놓이며 괄호를 묶어서 사용

2. 특수한 몇몇 경우를 제외하고 서브쿼리 내에서 ORDER BY 사용불가

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

    -> 비교대상 데이터가 하나라면 SELECT절도 역시 같은 자료형 같은 열 

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

    (다중행, 단일행 서브쿼리에서 자세히 알아보자)

 

2. 실행 결과가 하나인 단일행 서브쿼리 

 

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

 - 메인쿼리와 단일행 서브쿼리 사이에는 단일행 연산자(조건 연산자)만 사용 가능하다.

    [대소비교 + 같다아니다]

 - 서브쿼리 결과로 여러 행을 반환할 떄에는 다중행 서브쿼리를 사용하자

 

2.1 단일행 서브쿼리와 날짜 데이터 

SELECT *
FROM EMP
WHERE HIREDATE < (SELECT HIREDATE FROM EMP WHERE ENAME = 'SCOTT');

 - 다음과 같이 날짜데이터의 경우에도 서브쿼리 사용가능

 

2.2 단일행 서브쿼리와 함수 

 

 - 특정 함수의 결과가 하나일 때 (다중행 함수) 단일행 서브쿼리로 사용가능 

SELECT E.EMPNO
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  *서브쿼리 결과 기준으로 OR (그냥 IN과 동일)
ANY,SOME	메인쿼리의 조건식을 만족하는 서브쿼리의 결과가 하나 이상 있으면 TRUE  *메인쿼리 결과 기준으로 서브쿼리 OR
ALL	메인쿼리 조건식을 기준으로 서비쿼리 결과 모두 만족시 TRUE
EXISTS	서브쿼리의 결과가 존재하면 TRUE  *메인쿼리에 상관없이 서브쿼리 결과가 존재하면, 메인쿼리 행 TRUE > SELECT됨    EXISTS (SELECT '1' FROM DUAL) ->이런식이라면

 

3.1 IN

SELECT * FROM EMP
WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
--부서별 최고 급여 받는 사원

SELECT MAX (SAL) FROM EMP GROUP BY DEPTNO;
-- 이 결과에 IN한것과 같음

 

3.2 ANY, SOME 연산자 

 

 - 서브쿼리 반환한 여러 결과 값 중 메인쿼리와 조건식을 사용한 결과가 하나라도 true라면 메인쿼리 조건식을 true로 반환

 - 서브쿼리 결과행과 메인쿼리행 하나씩 데카르트곱으로 비교

 - ANY는 이때 하나의 행과 서브쿼리 모든행 비교시 하나라도 TRUE면 그 행의 결과는 TRUE이런식 

 

   *ANY나 SOME연산자를 등가 비교 연산자 = 와 같이 사용하면 IN과 정확하게 동일)  

SELECT * FROM EMP
WHERE SAL < ANY(SELECT SAL FROM EMP WHERE DEPTNO = 30)
-- 30번 부서 사원들의 최대 급여보다 적은 급여를 받는 사원 정보 출력
--위와 동일 
SELECT * FROM EMP WHERE SAL < (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30)


SELECT * FROM EMP WHERE SAL > ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30);
--위와 동일
SELECT * FROM EMP WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 30);

 -> 서브쿼리 결과 (950,1250,1500,1600,2850) -> 메인쿼리 모든 행 하나씩 서브쿼리 결과와 조사 

 -> 메인쿼리 SAL이 서브쿼리 결과들과 비교해서 하나라도 만족한다면 해당 행은 TRUE

 -> 위 경우 최대값보다 작은 모든 행 출력 

 

* ANY와 < 최대보다 작은 값, > 최소보다 큰 값  

 

3.3 ALL연산자

 

 - ANY와 SOME과 검사하는 방식은 동일하지만, 모든 결과가 일치해야함 

 

*ALL과 < 최소보다 작은 값, > 최대보다 큰 값

 

3.4 EXISTS 연산자 

 

 - 서브쿼리에 결과 값이 하나 이상 존재하면 조건식이 모두 true이다.

 - NOT EXISTS도 종종 사용한다.

SELECT * 
FROM EMP
WHERE EXISTS ( SELECT DNAME FROM DEPT WHERE DEPTNO =10)

 

*자주 사용하진 않지만, 특정 서브쿼리 결과 값이 존재할 때 메인쿼리 데이터 노출여부 결정시에 사용

 

4. 비교할 열이 여러 개인 다중열 서브쿼리 

 

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

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

    SELECT에 명시해야 사용가능 

 

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

 * 다중열이어도 결과가 단일행 일 수 있다. 

 

5. FROM절 서브쿼리와 WITH절 

 

 - 조건식이 아닌, FROM절에 서브쿼리 이를 인라인 뷰라고 부른다.

 - 인라인 뷰는 특정 테이블 전체 데이터가 아닌 SELECT된 일부 데이터 추출한 뒤에 별칭 주어 사용 

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

 - from절에 직접 테이블 쓰기에는 불필요하거나 데이터가 거대함 이럴때 사용

 - FROM절에 너무 많은 서브쿼리 지정시 가독성 떨어짐 -> WITH절 사용 

 

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. SELECT절에 사용하는 서브쿼리

 

 - 서브쿼리를 SELECT에 사용하면 스칼라 쿼리라고 부른다.

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

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

* 단일행 but 메인쿼리에 맞춰서 알맞게 값 하나씩 -> 즉 서브쿼리 평가를 행마다 실행한다는 것