[SQLD 2-2-1] SQL 활용 - 서브쿼리

서브쿼리 (Sub Query)

- 하나의 SQL문 안에 포함되어있는 또 다른 SQL문을 말한다.

- 반드시 괄호로 묶어야 함

- 서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만, 메인쿼리는 서브쿼리의 컬럼을 사용할 수 없다. 

- 서브쿼리는 서브쿼리 레벨과 상관없이 항상 메인쿼리 레벨로 결과 집합이 생성된다.

 

# 서브쿼리 사용 가능한 곳

- SELECT 절

- FROM W절

- WHERE 

- HAVING 절

- ORDER BY 절

- 기타 DML(INSERT, DELETE, UPDATE)

- GROUP BY 절 사용 불가

 

서브쿼리 종류

1. 동작하는 방식에 따라

2. 위치에 따라

1) 스칼라 서브쿼리

- SELECT 에 사용하는 서브쿼리

- 서브쿼리의 결과를 마치 하나의 컬럼처럼 사용하기 위해 주로 사용

- 각 행마다 스칼라 서브쿼리 결과가 하나여야 함(단일행 서브쿼리 형태)

- 조인의 대체 연

SELECT * | 컬럼명 | 표현식,
(SELECT * | 컬럼명 | 표현식
FROM
WHERE 조건)
FROM 테이블명 또는 뷰명;

 

 

2) 인라인뷰

- FROM 절에 사용하는 서브쿼리

- 서브쿼리 결과를 테이블처럼 사용하기 위해 주로 사용

SELECT * | 컬럼명 | 표현식
FROM
(SELECT * | 컬럼명 | 표현식
FROM 테이블명 또는 뷰명)
WHERE 조건;

 

 

3) WHERE 절 서브쿼리

- 가장 일반적인 서브쿼리

- 비교 상수 자리에 값을 전달하기 위한 목적으로 주로 사용(상수항의 대체)

- 리턴 데이터의 형태에 따라 단일행 서브쿼리, 다중행 서브쿼리, 다중컬럼 서브쿼리, 상호연관 서브쿼리로 구분

SELECT * | 컬럼명 | 표현식
FROM 테이블명 또는 뷰명
WHERE 조건연산자
(SELECT * | 컬럼명 | 표현식
FROM 테이블명 또는 뷰명
WHERE 조건);

 

 

# 단일 행 서브쿼리

예제) EMP 테이블에서 전체 직원의 급여 평균보다 높은 평균을 받는 직원의 정보 출력

 

SETP1) 비교대상(전체 직원 급여 평균) 확인

SELECT AVG(SAL)
FROM EMP;

 

실행 결과)

	AVG(SAL)
1	2073.214285714285714285714285714

 

SETP2) 메인쿼리의 비교 상수 자리에 서브쿼리 결과 전달

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > (SELECT AVG(SAL)
		FROM EMP);

 

실행 결과)

	EMPNO	ENAME	SAL
1	7566	JONES	2975
2	7698	BLAKE	2850
3	7782	CLARK	2450
4	7788	SCOTT	3000
5	7839	KING	5000
6	7902	FORD	3000

 

# 다중 행 서브쿼리

예제) 다중행 서브쿼리 연산자 오류 (서브쿼리 결과가 여러 개일 경우 = 연산자와 대소 비교 불가)

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > (SELECT SAL
	FROM EMP
    	WHERE DEPTNO = 10);
        
# 결과 : Error : 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

 

해결 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);

 

# 다중 컬럼 서브쿼리 예제

예제) EMP 테이블에서 부서별 최대 급여자 확인

 

SETP1) 부서별 최대 급여 확인

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

 

실행 결과)

	DEPTNO	MAX(SAL)
1	30	2850
2	20	3000
3	10	5000

 

SETP2) 메인쿼리에 비교 대상으로 서브쿼리 결과 전달

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

 

실행 결과)

	EMPNO	ENAME	SAL	DEPTNO
1	7698	BLAKE	2850	30
2	7902	FORD	3000	20
3	7788	SCOTT	3000	20
4	7839	KING	5000	10

- 부서별 최대 급여가 여러 값이 나오므로 비교 시에는 다중행 연산자인 IN 을 사용(= 사용시 에러 발생)

---

상호연관 서브쿼리

- 메인쿼리와 서브쿼리의 비교를 수행하는 형태

- 비교할 집단이나 조건은 서브쿼리에 명시(메인쿼리절에는 서브쿼리 컬럼이 정의되지 않았기 때문에 에러 발생)

 

예제) EMP 테이블에서 부서별로 해당 부서의 평균급여보다 높은 급여를 받는 사원 정보

SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE (DEPTNO, SAL) > (SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO);

# 결과 : Error : 연산자의 지정이 부적합합니다

 

* 에러 발생 - 다중 컬럼 서브쿼리는 동시 두 컬럼에 대한 대소비교 불가

 

해결) 대소 비교할 컬럼을 메인쿼리에, 일치 조건을 서브쿼리에 전달

SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP E1
WHERE SAL > (SELECT AVG(SAL)
FROM EMP E2
WHERE E1.DEPTNO = E2.DEPTNO
GROUP BY DEPTNO);

 

# 상호연관 서브쿼리 연산 순서

1) 메인쿼리 테이블 READ

2) 메인쿼리 WHERE 절 확인 (SAL 확인)

3) 서브쿼리 테이블 READ

4) 서브쿼리 WHERE 절 확인 (다시 E1.DEPTNO 요구)

5) E1.DEPTNO 값을 서브쿼리의 DEPTNO 컬럼과 비교하여 조건절 완성

6) 위 조건에 성립하는 행의 그룹연산 결과 확인 (AVG(SAL))

7) 위 결과를 메인쿼리에 전달하여 해당 조건을 만족하는 행만 추출

 

* 상호연관 서브쿼리 사용 시 GROUP BY 생략 가능

---

인라인 뷰 (Inline View)

- 쿼리 안의 뷰의 형태로 테이블처럼 조회할 데이터를 정의하기 위해 사용

- 테이블명이 존재하지 않기 때문에 다른 테이블과 조인 시 반드시 테이블 별칭 명시(단독으로 사용하는 경우 불필요)

- WHERE 절 서브쿼리와 다르게 서브쿼리 결과를 메인쿼리의 어느 절에서도 사용할 수 있음

- 인라인 뷰의 결과와 메인쿼리 테이블과 조인할 목적으로 사용

- 모든 연산자 사용 가능