2-2-1. 서브쿼리

1. 서브쿼리 개요

1️⃣ 서브쿼리의 기초 개념

1) 쿼리(Query)

• 하나의 명령 문장

• 메인쿼리(Mainquery)라고 함

2) 서브쿼리(Subquery)

• 메인쿼리 안에서 또 다른 명령 문장이 독립정으로 실행되는 것

• 내부쿼리 또는 중첩쿼리라고도 함

• 복잡한 쿼리를 단순화하고, 특정 조건에 맞는 데이터를 필터링하거나 계산할 때 사용

• 메인쿼리의 일부로 실행되며, 메인쿼리와 구분하기 위해 서브쿼리는 괄호로 감싸서 사용

• 메인쿼리의 WHERE, FROM, SELECT절 등에 사용됨

3) 형식

• 메인쿼리

SELECT 칼럼1, 칼럼2, ...
FROM 테이블
WHERE 칼럼1 = 칼럼2;

• 메인쿼리 내의 서브쿼리

SELECT 칼럼1, 칼럼2, ...
FROM 테이블
WHERE 칼럼1 = (
							SELECT 칼럼
							FROM 테이블
							WHERE 조건   /* 세미콜론(;) 사용하지 않음 */
							);

2️⃣ 언제 서브쿼리를 사용하는가?

• WHERE절에서 조건을 추가하고 싶을 때

• 데이터를 좀 더 복잡한 조건을 만들어 필터링하고 싶을 때

• 원래 데이터에는 존재하지 않는 계산된 칼럼을 생성하고 싶을 때 SELECT 문에서 사용

• DB에 테이블을 만들지 않고, 쿼리 안에서만 임시로 FROM절에 테이블을 만들어 사용(IN-LINE-VIEW)

• 일부 작업은 서브쿼리가 조인보다 간단함
특히, EXISTS 또는 IN 처럼 존재 여부를 확인하는 경우, 서브쿼리가 적합

3️⃣ 서브쿼리의 종류

1) 동작하는 방식에 따른 서브쿼리

2) 반환되는 데이터의 형태에 따른 서브쿼리

2. 서브쿼리의 종류

1️⃣ 단일행 서브쿼리

0) 단일행 서브쿼리

• 오직 하나의 행(row)만을 반환하는 서브쿼리
(0행일 경우, 서브쿼리의 반환값을 NULL로 간주)

• 단일행 서브쿼리는 하나의 행을 반환하며, 메인쿼리에서 하나의 값처럼 사용됨

• 주로 비교 연산자(=, >, <, >=, <=, <>) 및 집계 함수(AVG, SUM, MAX, MIN, COUNT 등)와 함께 자주 사용됨

1) WHERE 절에서의 단일행 서브쿼리 사용

• 급여가 평균 급여보다 높은 사원 정보 조회

SELECT ENAME, SAL
FROM EMP
WHERE SAL > (
						 SELECT AVG(SAL)
						 FROM EMP
						 )
ORDER BY SAL DESC;

2) HAVING 절에서의 단일행 서브쿼리 사용

• 부서의 평균 급여가 전체 평균 급여보다 높은 부서 조회

SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > (
									 SELECT AVG(SAL)
									 FROM EMP);

3) SELECT 절에서의 단일행 서브쿼리 사용

• 각 행에서 전체 평균 급여를 함께 표시

SELECT ENAME, SAL, (
										SELECT AVG(SAL)
										FROM EMP
										) AS AVG_SAL
FROM EMP
ORDER BY SAL DESC;

4) FROM 절에서의 단일행 서브쿼리 사용 (IN-LINE-VIEW)

• 평균 급여를 FROM 절에서 조회하고 메인쿼리에서 평균 급여보다 높은 급여를 받는 사원 조회

SELECT E.ENAME, E.SAL, A.AVG_SAL
FROM EMP E, (
						 SELECT AVG(SAL) AS AVG_SAL
						 FROM EMP
						 ) A
WHERE E.SAL > A.AVG_SAL
ORDER BY SAL DESC;

5) INSERT 문에서의 단일행 서브쿼리 사용

• 새 사원을 추가할 때 자동으로 다음 사원 번호를 생성

INSERT INTO EMP (EMPNO, ENAME, SAL) VALUES ((
																						 SELECT MAX(EMPNO) + 1
																						 FROM EMP
																						 ),
																						'NEW_EMP', 3000);

6) UPDATE 문에서의 사용

• SALES 부서의 모든 사원의 급여를 10% 인상하고 NEW_SAL에 값 입력

-- 신규 칼럼을 추가하는 SQL
ALTER TABLE EMP
		ADD NEW_SAL NUMBER(7,2);

-- SALES 부서의 모든 사원의 급여를 10% 인상하고 NEW_SAL에 값 입력
-- EMP 테이블의 DEPTNO=30으로 해도 되지만, 여기서는 업데이트 문제에서의 서브쿼리를 살펴보기
-- 위한 예제이므로 WHERE 절에 서브쿼리를 사용함
UPDATE EMP
		SET NEW_SAL = SAL * 1.1
WHERE DEPT = (
							SELECT DEPTNO
							FROM DEPT
							WHERE DNAME = 'SALES'
							);

-- 업데이트 후 조회
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME, E.SAL, E.NEW_SAL
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE D.DNAME = 'SALES';

7) DELETE 문에서의 사용

• 평균 급여 미만을 받는 모든 사원 삭제

SELECT ENAME, SAL, (
										SELECT AVG(SAL)
										FROM EMP
										) AVG
FROM EMP
ORDER BY DESC;

DELETE
FROM EMP
WHERE SAL < (
						 SELECT AVG(SAL)
						 FROM EMP);

8) 스칼라 서브쿼리 (칼럼 대신 값을 반환)

• 각 사원의 부서 이름을 함께 조회

SELECT ENAME, SAL, (
										SELECT DNAME
										FROM DEPT
										WHERE DEPT.DEPTNO = EMP.DEPTNO
										) AS DEPT_NAME
FROM EMP
ORDER BY EMPNO DESC;

9) 상관 서브쿼리

• 메인 쿼리의 각 행에 의존하여 실행되는 형태의 서브쿼리

• 각 사원의 급여가 자신이 속한 부서의 평균 급여보다 높은 경우를 조회

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

10) CASE 문에서의 사용

• 각 사원의 급여가 평균 이상인지 미만인지 표시

SELECT ENAME, SAL, CASE
											WHEN SAL > (
																	SELECT AVG(SAL)
																	FROM EMP)
											THEN 'Above Average'
											ELSE 'Below Average'
									END AS SALARY_STATUS
FROM EMP;

2️⃣ 다중행 서브쿼리

0) 다중행 서브쿼리

• 하나 이상의 행을 반환하는 서브쿼리

• 다중행 연산자(IN, ANY, ALL, EXISTS 등)와 함께 사용됨

• 하나 이상의 행을 반환하므로 단일행 연산자(=, >, <, >=, <=, <>) 를 직접 사용할 수 없음

1) IN

• 서브쿼리가 반환한 여러 행 중 하나라도 일치하면 조건을 만족

• 부서 위치가 뉴욕에 있는 사원의 정보 조회

SELECT ENAME, DEPTNO
FROM EMP
WHERE DEPTNO IN (
								 SELECT DEPTNO
								 FROM DEPT
								 WHERE LOC = 'NEW YORK'
								 );

SELECT A.ENAME, A.DEPTNO, B.DNAME, B.LOC
FROM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPTNO;

2) ANY 또는 SOME

• 서브쿼리가 반환한 여러 행 중 하나라도 일치하면 조건을 만족

• 10번 부서에 속한 어떤 직원의 급여보다 더 높은 급여를 받는 사원 조회

SELECT ENAME, SAL
FROM EMP
WHERE SAL > ANY (
								 SELECT SAL
								 FROM EMP
								 WHERE DEPTNO = 10
								);

3) ALL

• 서브쿼리가 반환한 모든 행이 조건을 만족

• 20번 부서에 속한 모든 직원의 급여보다 더 높은 급여를 받는 사원 조회

SELECT ENAME, SAL
FROM EMP
WHERE SAL > ALL (
								 SELECT SAL
								 FROM EMP
								 WHERE DEPTNO = 20
								);

4) EXISTS

• 서브쿼리가 한 행이라도 반환하면 조건을 만족

• 두 테이블에서 동일한 부서이면서 부서의 위치가 ‘DALLAS’인 사원명과 부서번호 조회

SELECT ENAME, DEPTNO
FROM EMP E
WHERE EXISTS (
							SELECT 1
							FROM DEPT D
							WHERE D.DEPTNO = E.DEPTNO
										AND D.LOC = 'DALLAS'
							);

3️⃣ 다중칼럼 서브쿼리

0) 다중칼럼 서브쿼리(Multi-Column Subquery)

• 서브쿼리에서 여러 칼럼을 반환하여 메인쿼리의 조건과 비교하는 서브쿼리

• 일반적으로 두 개 이상의 칼럼을 비교할 때 사용되며, 비교 연산자로 IN, EXISTS 등을 사용

1) IN 연산자와 다중칼럼 서브쿼리

• SAL이 3000보다 큰 직원들의 JOB과 DEPTNO를 서브쿼리로 찾고, 그 JOB과 DEPTNO를 가진 EMP 테이블의 직원들 조회

SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE (JOB, DEPTNO) IN (
												SELECT JOB, DEPTNO
												FROM EMP
												WHERE SAL > 3000
												);

2) EXISTS 연산자와 다중칼럼 서브쿼리

• 서브쿼리가 한 행이라도 반환하면 조건을 만족

• ‘DALLAS’에 위치한 부서에 속한 모든 직원의 이름과 사원 번호 조회

SELECT ENAME, DEPTNO
FROM EMP E
WHERE EXISTS (
							SELECT 1
							FROM DEPT D
							WHERE D.DEPTNO = E.DEPTNO
										AND D.LOC = 'DALLAS'
							);

4️⃣ 연관 서브쿼리

0) 연관 서브쿼리 (Correlated Subquery)

• 메인쿼리의 각 행에 대해 서브쿼리가 한 번씩 실행되는 서브쿼리

• 서브쿼리는 메인쿼리의 각 행과 관련되어 있으며, 메인쿼리의 데이터를 참조

• 연관 서브쿼리는 반복적으로 실행되므로 일반 서브쿼리보다 성능 떨어짐

• 서브쿼리는 메인쿼리의 행 데이터를 참조하여 각 행에 대해 조건을 평가

• 서버쿼리는 메인쿼리와 관련된 데이터를 기반으로 동적으로 실행

1) 연관 서브쿼리 #1

• 각 직원의 급여가 동일한 부서의 평균 급여보다 높은 직원을 조회

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

2) 연관 서브쿼리 #2

• 각 부서에서 가장 최근에 고용된 직원 조회

SELECT ENAME, HIREDATE, DEPTNO
FROM EMP E1
WHERE HIREDATE = (
									SELECT MAX(HIREDATE)
									FROM EMP E2
									WHERE E1.DEPTNO = E2.DEPTNO
									);

5️⃣ 뷰 (VIEW)

0) 뷰 (VIEW)

• 하나 이상의 테이블에서 데이터를 검색하는 SQL 쿼리의 결과를 저장하는 가상 테이블

• 실제 데이터를 저장하지 않고, 기본 테이블에서 데이터를 가져와 동적이며 읽기 전용의 데이터 집합 제공

• 특징
• 복잡한 쿼리를 단순화
• 데이터 접근을 제어
• 보안과 편의성 높일 수 있음

1) 뷰(View) 형식

• SELECT문을 기본으로 함

• 가장 첫 줄에 CREATE VIEW 적고 뷰 이름을 지정

CREATE VIEW 뷰이름
SELECT 칼럼1, 칼럼2, ...
FROM 테이블
WHERE 조건;

2) 뷰(View)의 예

• 부서의 지역(LOC)이 ‘CHICAGO’인 부서의 직원 정보를 포함하는 뷰 생성

CREATE VIEW EMP_DEPT_VIEW AS
SELECT E.ENAME, E.JOB, D.DNAME, D.LOC
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE D.LOC = 'CHICAGO';

3) 뷰의 장점

• 데이터 중복 방지
• 뷰는 실제 데이터를 저장하지 않아 중복 피할 수 있음

• 데이터 일관성
• 뷰는 항상 최신 데이터를 보여줌

• 보안 강화
• 뷰를 통해 특정 칼럼이나 행에 대한 접근 제한할 수 있음
• 원본 테이블의 구조를 숨기면서 필요한 정보만 제공

• 복잡한 쿼리 단순화
• 자주 사용되는 복잡한 쿼리를 뷰로 만들어 재사용
• 코드의 가독성과 유지보수성 높임
• 실무에서 뷰를 사용하는 가장 큰 이유 중 하나

• 유연성
• 기본 테이블 구조가 변경되어도 뷰를 통해 일관된 인터페이스 제공
• 애플리케이션 코드 수정 없이 뷰만 수정하면 됨

• 성능 최적화
• 일부 DBMS에서는 materialized view를 제공하여 성능 향상

• 논리적 데이터 독립성
• 논리적 데이터 독립성
• 데이터베이스의 논리적 구조(테이블 , 칼럼 등)와 물리적 구조(저장 경로, 저장 파일, 색인 등)를 분리하여, 한쪽의 변화가 다른 쪽에 영향을 미치지 않도록 하는 데이터베이스의 설계 원칙
• 뷰는 논리적 데이터 독립성을 실현하는 중요한 수단
• 뷰를 통해 물리적 데이터 구조와 논리적 데이터 구조 분리할 수 있음

• 저장 공간 절약
• 실제 데이터 저장하지 않으므로 추가 저장 공간 필요 없음

• 시스템 카탈로그 용이
• 시스템 카탈로그
• 데이터베이스에서 테이블, 뷰, 색인, 제약 조건, 사용자 권한 등 데이터베이스에 대한 메타데이터를 관리하는 특수한 데이터베이스 영역
• 뷰는 논리적 데이터 구조로서 가상 테이블이며, 물리적으로 데이터를 저장하지 않기 때문에 시스템 카탈로그에 영향을 덜 미침

4) 뷰(View)와 테이블(Table)의 주요 차이점