ex) SELECT안에 SELECT 문, INSERT, UPDATE, DELETE 안의 SELECT 문
사용 가능한 곳
SELECT 절
FROM 절
WHERE 절
HAVING 절
ORDER BY 절
기타 DML (INSERT, DELETE, UPDATE) 절
GROUP BY 절 사용 불가
종류
동작 방식에 따라
UN-CORRELATED (비연관) 서브쿼리
서브쿼리가 메인쿼리 컬럼을 가지고 있지 않은 형태의 서브쿼리
메인쿼리에 서브쿼리가 실행된 결과 값을 제공하기 위한 목적
CORRELATED (연관) 서브쿼
서브쿼리가 메인쿼리 컬럼을 ㅏ지고 있는 형태의 서브 쿼리
일반적으로 메인쿼리가 먼저 수행된 후에 서브쿼리에서 조건이 맞는지 확인하고자 할 때 사용
위치에 따라
스칼라 서브쿼리
SELECT에 사용하는 서브 쿼리
서브쿼리 결과를 마치 하나의 컬럼처럼 사용하기 위해 주로 사용
문법
SELECT * | 컬럼명 | 표현식,
(SELECT * | 컬럼명 | 표현식
FROM 테이블명 또는 뷰명
WHERE 조건)
FROM 테이블명 또는 뷰명;
인라인뷰
FROM 에 사용하는 서브쿼리
서브쿼리 결과를 테이블처럼 사용하기 위해 주로 사용
문법
SELECT * | 컬럼명 | 표현식
FROM (SELECT * | 컬럼명 | 표현식
FROM 테이블명 또는 뷰명)
WHERE 조건;
WHERE 절 서브쿼리
가장 일반적인 서브쿼리
비교 상수 자리에 값을 전달하기 위한 목적으로 주로 사용 (상수항의 대체)
리턴 데이터의 형태에 따라 단일행 서브쿼리, 다중행 서브쿼리, 다중컬럼 서브쿼리, 상호 연관 서브쿼리로 구분
문법
SELECT * | 컬럼명 | 표현식
FROM 테이명 또는 뷰명
WHERE 조건연산자 (SELECT * | 컬럼명 | 표현식
FROM 테이블명 또는 뷰명
WHERE 조건);
WHERE 절 서브쿼리 종류
단일행 서브쿼리
서브쿼리 결과가 1개의 행이 리턴되는 형태
단일행 서브쿼리 연산자 종류
연산자
의미
=
같다
<>
같지 않다
>
크다
>=
크거나 같다
<
작다
<=
작거나 같다
ex) EMP 테이블에서 전체 직원의 급여 평균보다 높은 평균을 받는 직원의 정보 출력
STEP 1. 비교대상(전체 직원 급여 평균) 확인
SELECT AVG(SAL)
FROM EMP;
STEP 2. 메인쿼리에 비교 상수 자리에 서브쿼리 결과 전달
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > (SELECT AVG(SAL)
FROM EMP);
다중행 서브쿼리
서브쿼리 결과가 여러 행이 리턴되는 형태
‘=’, ‘>’, ‘<’ 와 같은 비교 연산자 사용불가(여러 값이랑 비교할 수 없는 연산자들)
서브쿼리 결과를 하나로 요약하거나 다중행 서브쿼리 연산자를 사용
다중행 서브쿼리 연산자
연산자
의미
IN
같은 값을 찾음
> ANY
최소값을 반환함
< ANY
최대값을 반환함
< ALL
최소값을 반환함
> ALL
최대값을 반환함
ex) ALL 과 ANY 비교 > ALL(2000, 3000) : 최대값(3000)보다 큰 행들 반환 < ALL(2000, 3000) : 최소값(2000)보다 작은 행들 반환 > ANY(2000, 3000) : 최소값(2000)보다 큰 행들 반환 < ANY(2000, 3000) : 최대값(3000)보다 작은 행들 반환
ex) 다중행 서브쿼리 연산자 오류 (서브쿼리 결과가 여러 개일 경우 = 연산자와 대소 비교 불가)
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE DEPTNO = 10);
해결 1: 서브쿼리 결과를 하나의 행의 결과가 되도록 변경
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > (SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO = 10);
해결 2: 다중행 서브쿼리 연산자로 변경
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > ANY(SELECT SAL
FROM EMP
WHERE DEPTNO = 10);
다중컬럼 서브쿼리
서브쿼리 결과가 여러 컬럼이 리턴되는 형태
메인쿼리와의 비교 컬럼이 2개 이상
대소 비교 전달 불가 (두 값을 동시에 묵어 대소 비교 할 수 없음)
ex) EMP 테이블에서 부서별 최대 급여자 확인
STEP 1. 부서별 최대 급여 확인
SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO;
STEP 2. 메인쿼리에 비교 대상으로 서브쿼리 결과 전달
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO);
→ 부서별 최대 급여가 여러 값이 나오므로 비교 시에는 다중행 연산자인 IN을 사용 (= 사용 시 에러발생)
상호연관 서브쿼리
메인 쿼리와 서브쿼리의 비교를 수행하는 형태
비교한 집단이나 조건은 서브쿼리에 명시(메인쿼리절에는 서브쿼리 컬럼이 정의 되지 않았기 때문에 에러 발생)
ex) EMP 테이블에서 부서별로 해당 부서의 평균급여보다 높은 급여를 받는 사원 정보 ** 에러 발생 다중 컬럼 서브쿼리는 동시 두 컬럼에 대한 대소비교 불가
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE (DEPTNO, SAL) > (SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO);
해결: 대소 비교할 컬럼을 메인쿼리에, 일치 조건을 서브쿼리에 전달
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP E1
WHERE SAL > (SELECT AVG(SAL)
FROM EMP E2
WHERE E1 DEPTNO = E2.DEPTNO
GROUP BY DEPTNO);
→ 메인쿼리와 결과적으로 비교해야 할 컬럼은 .SAL 과 DEPTNO 인데, 그 중 SAL에 대한 대소비교 전에 먼저 비교할 부서(DEPTNO) 정보가 확정돼야 함 → 메인쿼리에는 서브쿼리의 테이블 정보가 없으므로(순서상 메인쿼리부터 해석) E.DEPTNO = D.DEPTNO 조건은 사용 불가) → 먼저 비교할 DEPTNO 겂을 서브쿼리에 전달
** 상호 연관 서브쿼리 연산 순서
메인쿼리 테이블 READ
메인쿼리 WHERE 절 확인 (SAL 확인)
서브쿼리 테이블 READ
서브쿼리 WHERE 절 확인 (다시 E1.DEPTNO 요구)
E1.DEPTNO 겂을 서브쿼리의 DEPTNO 컬럼과 비교하여 조건절 완성
위 조건에 성립하는 행의 그룹연산 결과 확인 (AVG(SAL))
위 결과를 메인쿼리에 전달하여 해당 조건을 만족하는 행만 추출 ※ 상호연관 서브쿼리 사용 시 GROUP BY 생략 가능
인라인 뷰
인라인뷰
쿼리 안의 뷰 형태로 테이블처럼 조회할 데이터를 정의하기 위해 사용
테이블명이 존재하지 않기 때문에 다른 테이블과 조인 시 반드시 테이블 별칭 명시 (단독으로 사용하는 경우 불필요)
WHERE 절 서브쿼리와 다르게 서브쿼리 결과를 메인쿼리의 어느 절에서도 사용할 수 있음
인라인뷰의 결과와 메인쿼리 테이블과 조인할 목적으로 주요 사용
모든 연산자 사용 가능
ex) EMP 테이블에서 부서별 최대 급여자를 출력하되, 최대 급여와 함께 출력
SELECT E.EMPNO, E.ENAME, E.SAL, I.MAX_SAL
FROM EMP E, (SELECT DEPTNO, MAX(SAL) AS MAX_SAL
FROM EMP
GROUP BY DEPTNO) I
WHERE E.DEPTNO = I.DEPTNO
AND E.SAL = I.MAX_SAL;
→ 인라인뷰에서의 함수에 의한 출력결과 (MAX(SAL))는 럼 별칭을 통해 메인쿼리에 전달 (그룹함수는 where 절에 들어갈 수 없음, 단일 함수만 사용 가능)
ex) EMP 테이블에서 부서별로 해당 부서의 평균 급여보다 높은 급여자를 출력하되, 평균 급여와 함께 출력
SELECT E.EMPNO, E.ENAME, E.SAL, I.AVG_SAL
FROM EMP E, (SELECT DEPTNO, AVG(SAL) AS AVG_SAL
FROM EMP
GROUP BY DEPTNO) I
WHERE E.DEPTNO = I.DEPTNO
AND E.SAL > I.AVG_SAL;
스칼라 서브쿼리
스칼라 서브쿼리
SELECT 절에 사용하는 쿼리로, 마치 하나의 컬럼처럼 표현하기 위해 사용(단 하나의 출력 대상만 표현 가능)
각 행마다 스칼라 서브쿼리 결과가 하나여야 함 (단일행 서브쿼리 형태)
조인의 대체 연산
스칼라 서브쿼리를 사용한 조인 처리 시 OUTER JOIN이 기본 (값이 없더라도 생략되지 않고 NULL로 출력됨)
ex) EMP의 각 직원의 사번, 이름과 부서이름을 출력 (부서이름을 스칼라 서브쿼리로)
SELECT EMPNO, ENAME,
(SELECT DNAME
FROM DEPT D
WHERE D.DEPTNO = E.DEPTNO) AS DNAME
FROM EMP E
WHERE DEPTNO = 10;
ex) EMP의 각 직원의 사번, 이름, 부서번호, 급여와 함께 급여 총합을 출력 (총합을 스칼라 서브쿼리로)
SELECT EMPNO, ENAME, DEPTNO, SAL
(SELECT SUM(SAL)
FROM EMP) AS SUM_SAL
FROM EMP;
ex) 서브쿼리의 아우터 조인
SELECT E1.ENAME AS 사원명
(SELECT E2.ENAME
FROM EMP E2
WHERE E1.MGR = E2.EMPNO) AS 상위관리자명
FORM EMP E1;
→ KING의 경우 MGR 컬럼 값이 NULL 이므로 MGR = EMPNO에 만족하는 E2.ENMAE 값이 없지만, 스칼라 서브쿼리는 무조건 메인쿼리절이 출력하는 대상에 대해 항상 값을 리턴해야하므로 생랴괴디 않고 NULL 출력
서브쿼리 주의 사항
특별한 경우 (TOP-N 분석 등)를 제외하고는 서브 쿼리절에 ORDER BY 절 사용 불가
단일 행 서브쿼리와 다중 행 서브쿼리에 따라 연산자의 선택이 중요
ex) 서브쿼리에 ORDER BY 전달 시 에러 발생
SELECT *
FROM EMP
WHERE SAL IN (SELECT SAL
FROM EMP
WHERE DEPTNO = 10
ORDER BY SAL);
