[SQLD] SQL 활용 - 서브쿼리(Subquery)

서브쿼리 (Subquery)

• 하나의 쿼리 문장 내에 포함된 또 하나의 쿼리 문장
• 비교연산자의 오른쪽에 기술해야 하고 반드시 괄호 안에 넣어야함
• 메인 쿼리가 실행되기 이전에 한 번만 실행됨

⚠️ 서브쿼리 사용시 주의사항

- 서브쿼리를 괄호로 감싸서 사용한다
- 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 하고 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.
- 서브쿼리에서는 ORDER BY절은 메인쿼리의 마지막 문장에 위치해야 한다.

 

서브쿼리 사용 가능한 곳

• SELECT 절
• FROM 절
• WHERE 절
• HAVING 절
• ORDER BY 절
• INSERT문의 VALUES 절
• UPDATE문의 SET 절

 

SELECT 절 서브쿼리 (스칼라 서브쿼리)

스칼라 서브쿼리 (Scalar Subquries)라고 불리며 SELECT 절 안에 서브쿼리가 들어있다.

이 때, 서브쿼리의 결과는 반드 시 단일 행이나 SUM, COUNT 등의 집계 함수를 거친 단일 값으로 리턴되어야 한다.

이유는 서브쿼리를 끝마친 값 하나를 메인쿼리에서 SELECT 하기 때문이다.

 

-- 홍길동 학생과 학과를 조회

SELECT 학생이름,
	(SELECT 학과.학과이름
    FROM 학과
    WHERE 학과.학과ID = 학생.학생ID) AS 학과이름
FROM 학생
WHERE 학생이름 = '홍길동';

 

 

FROM 절 서브쿼리 (인라인뷰 서브쿼리)

인라인뷰 (Inline Views)라고 불리며 FROM 절 안에 서브쿼리가 들어있다.

이 때, 서브쿼리의 결과는 반드시 하나의 테이블로 리턴되어야 한다.

이유는 서브쿼리를 끝마친 테이블 하나를 메인쿼리의 FROM에서 테이블로 잡기 때문

-- 수학 과목을 수강하는 학생들의 점수를 조회

SELECT 학생이름, 수학점수
FROM (SELECT 학생.학생이름 AS 학생이름,
			과목.과목점수 AS 수학점수
        FROM 학생, 과목
        WHERE 학생.학생이름 = 과목.학생이름
        AND 과목.과목이름 = '수학');

 

WHERE 절 서브쿼리 (중첩 서브쿼리)

중첩 서브쿼리(Nested Subquries)라고 불리며 WHERE 절 안에 서브쿼리가 들어있다.

가장 자주 쓰이는 대중적인 서브쿼리이며 단일행과 복수행 둘 다 리턴이 가능하다.

이유는 서브쿼리를 끝마친 값들을 메인쿼리의 조건절을 통해 비교등을 하기 때문.

-- 수학 과목을 수강하는 학생들의 모든 정보 조회

SELECT *
FROM 학생
WHERE 학생.학생이름 IN (SELECT 과목.학생이름 FROM 과목 WHERE 과목.과목이름 = '수학');

 

 

단일 행 서브쿼리

• 서브쿼리의 수행결과가 오직 하나의 ROW(행)만을 반환
• 이 하나의 결과를 가지고 메인쿼리는 비교연산자를 통해 쿼리를 수행함
• 비교연산자는 단일행 비교연산자를 사용(>, >=, <, <=, =, <>, ...)

-- 사원들의 평균 급여보다 더 많은 급여를 받는 사원을 검색
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT AVG(SAL)
			FROM EMP);

 

 

다중 행 서브쿼리

• 서브쿼리의 수행결과가 두 건이상의 데이터를 반환
• 비교연산자는 다중행 비교연산자를 사용 (IN, ANY, SOML, ALL, EXISTS)

다중 행 연산자	설명
IN (서브쿼리)	서브쿼리 결과 중 하나만 동일하면 참 (OR 조건)
비교연산자 ALL (서브쿼리)	서브쿼리 결과가 메인쿼리 조건문에 전부 일치하면 참 - 메인쿼리 < ALL (서브쿼리): 서브쿼리의 결과와 비교하여 최소값 반환 - 메인쿼리 > ALL (서브쿼리): 서브쿼리 결과와 비교하여 최대값 반환
비교연산자 ANY (서브쿼리)	서브쿼리 결과가 메인쿼리 조건문에 하나라도 일치하면 참 - 메인쿼리 < ALL (서브쿼리): 서브쿼리의 결과와 비교해 메인쿼리의 데이터 중 한개라도 서브쿼리보다 작다면 최소값 반환 - 메인쿼리 > ALL (서브쿼리): 서브쿼리의 결과와 비교해 메인쿼리의 데이터 중 한개라도 서브쿼리보다 크다면 최대값 반환
EXSISTS (서브쿼리)	서브쿼리의 결과가 하나라도 있다면 참 아무것도 없다면 거짓

[ALL 예시]
-- 30번 소속 사원들 중 급여를 가장 많이 받는 사원보다
-- 더 많은 급여를 받는 사람의 이름과 급여를 출력

SELECT ENAME, SAL
FROM EMP
WHERE SAL < ALL (SELECT SAL
				FROM EMP
                WHERE DEPTNO = 30);

 

다중 칼럼 서브쿼리

서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교가 되는 것을 의미

(SQL Server 지원 안됨)

각 팀의 키가 가장 작은 선수들의 팀코드, 선수명, 포지션, 백넘거, 키를 출력하시오.
(다중 컬럼 서브쿼리 및 IN 연산자 사용 / 팀코드와 선수명을 오름차순으로 정렬)

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE (TEAM_ID, HEIGHT) IN (
	SELECT TEAM_ID, MIN(HEIGHT)
    FROM PLAER
    GROUP BY TEAM_ID)
ORDER BY 1, 2;

 

연관 서브쿼리(Correlated Subquery)

서브쿼리 내에 메인 쿼리 칼럼이 사용된 서브쿼리

선수 자신이 속한 팀의 평균 키보다 작은 선수들의 정보를 출력하는  SQL문을 연관 서브쿼리를 이용해 작성

SELECT T.TEAM_NAME 팀명, M.PLAYER_NAME 선수명, M.POSITION 포지션, M.BACK_NO 백넘버
FROM PALYER M, TEAM T
WHERE M.TEAM_ID = T.TEAM_ID AND M.HEIGHT < (
	SELECT AVG(S.HEIGHT)
    FROM PLAYER S
    WHERE S.TEAM_ID = M.TEAM_ID AND S.HEIGHT IS NOT NULL
    GROUP BY S.TEAM_ID)
ORDER BY 선수명;

 

 

 

VIEW (뷰) 생성, 삭제

존재하는 테이블을 참조해서 만드는 가상의 테이블로 실제 데이터가 존재하지 않는다.

뷰의 장점	설명
독립성	테이블 구조가 변경되어도 뷰는 변경되지 않는다
편리성	질의문 단순화, 데이터 관리 간단
보안성	보여주고 싶은 데이터만 보여준다

하지만, 구조를 바꾸지 못하고 삽입, 삭제, 수정 기능이 없다. 또한 자신만의 인덱스를 가질 수 없다.

[생성]
CREATE VIEW 뷰명 AS SELECT 칼럼명 FROM 테이블명;

[조회]
SELECT 칼럼명 FROM 뷰명;

[삭제]
DROP VIEW 뷰명;