[ORACLE 초급] 서브쿼리 - WHERE절(단일행, 다중행, 다중열), 인라인뷰(WITH절), 스칼라서브쿼리

서브쿼리

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

SELECT문 외에도 INSERT, UPDATE, DELETE, CREATE문 등 다양한 SQL문에서 서브쿼리 사용

-- SELECT문의 WHERE절에 SUBQUERY (메인쿼리 내의 서브쿼리)
SELECT 열
FROM 테이블
WHERE 조건식 ( SELECT 열
                             FROM 테이블
                             WHERE 조건식 )

✨ 서브쿼리의 특징

1. 연산자와 같은 비교 또는 조회 대상의 오른쪽에 놓이며 괄호로 묶어 사용
2. 특수한 경우 제외, 대부분의 서브쿼리는 ORDER BY절을 사용할 수 없음
3. 서브쿼리 SELECT절의 열은 메인쿼리의 비교 대상과 같은 자료형과 같은 개수로 지정
4. 메인쿼리 연산자 종류와 서브쿼리 SELECT문 결과 행 수는 호환 가능해야 함

단일행 서브쿼리

실행 결과가 하나

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

단일행 연산자(>, >=, =, <=, <, <>, ^=, !=)를 사용하여 값 비교

-- JONES의 급여보다 높은 급여를 받는 사원 정보 (서브쿼리 이용)
SELECT *
FROM EMP
WHERE SAL > (SELECT SAL
                         FROM EMP
                         WHERE ENAME = 'JONES'); -- 참고로, 이때 JONES라는 이름이 여러 개 존재하면 에러발생

-- 서브쿼리 결과 값이 날짜형 (SCOTT보다 빨리 입사한 사원 목록)
SELECT *
FROM EMP
WHERE HIREDATE < (SELECT HIREDATE
                                  FROM EMP
                                  WHERE ENAME = 'SCOTT'); -- 참고로, 이때 JONES라는 이름이 여러 개 존재하면 에러발생

다중행 서브쿼리

실행 결과가 여러 개

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

다중행 연산자(IN, ANY, SOME, ALL, EXISTS)를 사용하여 값 비교

• IN : 하나라도 일치한 데이터가 있다면 TRUE
• ANY, SOME : 만족하는 결과가 하나 이상이면 TRUE
• ALL : 결과가 모두 만족하면 TRUE
• EXISTS : 결과가 존재하면 TRUE (행이 1개 이상이면 TRUE)

-- <IN> 부서별 최고 급여와 동일한 급여를 받는 사원 정보
SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

-- <SOME> 등가 비교 연산자(=)와 ANY, SOME을 함께 사용하면 IN 연산자와 같은 기능 수행
SELECT *
FROM EMP
WHERE SAL = SOME (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

-- <ANY> 등가 비교 연산자가 아닌 대소 비교 연산자와 ANY를 함께 사용하면?
-- < ANY : 부서 30 중 최대값보다 적은 급여를 가진 사원을 찾게 됨
-- > ANY : 부서 30 중 최소값보다 높은 급여를 가진 사원을 찾게 됨
SELECT *
FROM EMP
WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30);

-- <ALL> 부서 30 중 최대 급여보다 더 많은 급여를 받는 사원 출력
SELECT *
FROM EMP
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30);

-- <EXISTS> 부서50이 있으면 EMP 전체 출력
SELECT *
FROM EMP
WHERE EXISTS (SELECT DNAME FROM DEPT WHERE DEPTNO = 50);

다중열 서브쿼리

비교할 열이 여러 개

multiple-column subquery : 서브쿼리의 SELECT절에 비교할 데이터를 여러 개 지정하는 방식 (복수열 서브쿼리)

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

FROM절 서브쿼리

= 인라인 뷰
특정 테이벌 전체 데이터가 아닌 SELECT문을 통해 일부 데이터를 먼저 추출 후 사용

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

FROM절에 직접 테이블을 명시하여 사용하기에 데이터 규모가 크거나 불필요한 열이 많을 때, 일부 행과 열만 사용하고자 할 때 사용

WITH절

FROM절에 너무 많은 서브쿼리를 지정하면 가독성이나 성능이 떨어지기 때문에 사용

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

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

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;