[SQLD] #024 SQL 활용 - 서브 쿼리

목차

0. 서브쿼리란?

1. 단일 행 서브쿼리

2. 다중 행 서브쿼리

3. 다중 칼럼 서브쿼리

4. 연관 서브쿼리

5. 그 밖의 위치에서 사용하는 서브쿼리

6. 뷰

 

 

 

---

 

 

 

0. 서브쿼리란?

서브쿼리(Subquery) : 하나의 SQL문 안에 포함되어있는 또 다른 SQL 문

 

서브쿼리는 알려지지 않은 기준을 이용한 검색을 위해 사용한다. 서브쿼리는 아래 그림과 같이 메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다. 

 

 

서브쿼리의 특징

JOIN은 JOIN에 참여하는 모든 테이블이 대등한 관계에 있기 때문에 조인에 참여하는 모든 테이블의 칼럼을 어느 위치에서라도 자유롭게 사용할 수 있다. 그러나 서브쿼리는 메인 쿼리의 칼럼을 모두 사용할 수 있지만 메인 쿼리는 서브쿼리의 칼럼을 사용할 수 없다. 따라서 질의 결과에 서브쿼리 칼럼을 표시해야 한다면 조인 방식으로 변환하거나 함수, 스칼라 서브쿼리 등을 사용해야 한다. 

 

JOIN은 집합간의 곱(PRODUCT) 관계이다. 즉 1:1 관계의 테이블이 조인하면 1(1*1) 레벨의 집합이 생성되고, 1:M 관계의 테이블을 조인하면 M(1*M) 레벨의 집합이 생성된다. 그리고 M:N 관계의 테이블을 조인하면 MN(M*N) 레벨의 집합이 결과로써 생성된다. 

 

 

 

 

서브쿼리 사용시 주의사항 

1. 서브쿼리를 괄호로 감싸서 사용한다. 

2. 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야하고, 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다. 

3. 서브쿼리에서는 ORDER BY를 사용하지 못한다. ORDER BY절은 SELECT 절에서 한 개만 올 수 있기 때문에 ORDER BY 절은 메인 쿼리의 마지막 문장에 위치해야 한다. 

 

 

 

 

서브쿼리가 SQL 문에서 사용 가능한 곳

- SELECT 절

- FROM 절

- WHERE 절

- HAVING 절

- ORDER BY 절

- INSERT 문의 VALUES 절

- UPDATE 문의 SET 절

 

 

 

 

 

서브쿼리 분류

서브쿼리는 메인쿼리 안에 포함된 종속적인 관계이기 때문에 논리적인 실행 순서는 항상 메인 쿼리에서 읽혀진 데이터에 대해 서브쿼리에서 해당 조건이 만족하는지를 확인하는 방식으로 수행되어야 한다. 

그러나 실제 서브쿼리의 실행 순서는 상황에 따라 달라질 수 있다.

 

 

 

 

 

 

 

 

 

 

1. 단일 행 서브 쿼리

서브쿼리가 단일 행 비교 연산자 (=, <, <=, >, >=, <>)와 함께 사용될 때는 서브쿼리의 결과 건수가 반드시 1건 이하여야 한다. 만약 서브쿼리의 결과건수가 2건 이상이면 SQL문은 런타임 에러가 발생한다. 

 

 

예제) '정남일' 선수가 소속된 팀의 선수들에 대한 정보를 표시하는 문제

 

 

위 예제를 서브쿼리 방식의 SQL문으로 작성하면 다음과 같다. 

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버
FROM PLAYER 
WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정남일')
ORDER BY PLAYER_NAME;

 

메인 쿼리는 서브쿼리에서 반환된 결과를 이용해서 조건을 만족하는 선수들의 정보를 출력한다. 

 

 

 

 

 

 

 

 

 

 

 

 

2. 다중 행 서브쿼리

서브쿼리의 결과가 2건 이상 반환될 수 있으면 반드시 다중 행 비교 연산자 (IN, ALL, ANY, SOME)와 함께 사용해야 한다. 

 

다중 행 비교 연산자는 다음과 같다. 

 

예제) '정현수' 라는 선수가 소속되어있는 팀 정보를 출력하는 서브쿼리를 작성하라.

SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명
FROM TEAM
WHERE TEAM_ID = (SELECT TEAM_ID 
                 FROM PLAYER
                 WHERE PLAYER_NAME = '정현수')
ORDER BY TEAM_NAME;   

-- ERROR : 단일 행 하위 질의에 2개 이상의 행이 리턴되었다.

'정현수'라는 이름을 가진 선수가 두명있고, 각각 다른 팀에 있다고 하자. 그러면 위의 SQL문은 서브쿼리의 결과로 2개 이상의 행이 반환되어 단일 행 연산자인 '=' 로는 처리가 불가능하기 떄문에 에러가 발생했다. 

따라서 다중 행 비교 연산자로 바꿔서 SQL문을 작성하면 다음과 같다.

SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명
FROM TEAM
WHERE TEAM_ID IN (SELECT TEAM_ID 
                  FROM PLAYER
                  WHERE PLAYER_NAME = '정현수')
ORDER BY TEAM_NAME;   

 

 

 

 

 

 

 

 

 

 

 

 

3. 다중 칼럼 서브쿼리

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

 

 

예제) 소속팀별 키가 가장 작은 사람들의 정보를 출력하라.

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

 

 

 

 

 

 

 

 

 

 

 

 

4. 연관 서브쿼리

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

 

 

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

SELECT T.TEAM_NAME 팀명, M.PLAYER_NAME 선수명, M.POSITION 포지션, M.BACK_NO 백넘버, M.HEIGHT 키
FROM PLAYER 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 선수명;

 

 

 

 

 

 

 

 

 

 

 

 

 

5. 그 밖의 위치에서 사용하는 서브쿼리

1. SELECT 절에 서브쿼리 사용하기

SELECT 절에 사용하는 서브쿼리 : 스칼라 서브쿼리

 

스칼라 서브쿼리는 한 행, 한 칼럼만을 반환하는 서브쿼리이다. 스칼라 서브쿼리 또한 단일 행 서브쿼리이기 때문에 2건 이상 반환되면 SQL문은 에러가 난다. 선수 정보와 해당 선수가 속한 팀의 평균 키를 함꼐 출력하는 경우는 아래와 같다. 

 

 

 

 

 

 

 

2. FROM 절에서 서브쿼리 사용하기

FROM 절에서 사용되는 서브쿼리 : 인라인 뷰

 

FROM 절에는 테이블 명이 와야한다. 그런데 서브쿼리가 FROM 절에 사용되면, 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있다. 인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 DB에는 저장되지 않는다. 인라인 뷰를 사용하는 것은 JOIN 방식을 사용하는 것과 같다. 그렇기 때문에 인라인뷰의 칼럼은 SQL문을 자유롭게 참조할 수 있다. 

 

예제) K리그 선수들중에서 포지션이 미드필더인 선수들의 소속팀명 및 선수 정보를 출력하라.

SELECT T.TEAM_NAME 팀명, P.PLAYER_NAME 선수명, P.BACK_NO 백넘버
FROM (SELECT TEAM_ID, PLAYER_NAME, BACK_NO
	  FROM PLAYER
      WHERE POSITION = 'MF') P
	  TEAM T 
WHERE P.TEAM_ID = T.TEAM_ID ORDER BY 선수명;

 

 

 

 

 

 

 

 

 

3. HAVING 절에서 서브쿼리 사용하기

HAVING 절은 그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해서 사용한다. 

 

예제) 평균키가 삼성블루윙즈팀의 평균키보다 작은 팀의 이름과, 해당 팀의 평균키를 구하는 SQL 문을 작성하라.

SELECT P.TEAM_ID 팀코드, T.TEAM_NAME 팀명, AVG(P.HEIGHT) 평균키
FROM PLAYER P, TEAM T
WHERE P.TEAM_ID = T.TEAM_ID 
GROUP BY T.TEAM_ID, T.TEAM_NAME 
HAVING AVG(P.HEIGHT) < (SELECT AVG(HEIGHT)
                        FROM PLAYER 
                        WHERE TEAM_ID = 'K02')

 

 

 

 

 

 

 

 

 

 

4. UPDATE 문의 SET 절에서 사용하기

TEAM 테이블에 STADIUM_NAME 을 추가했다고 가정하자. TEAM 테이블에 추가된 STADIUM_NAME의 값을 STADIUM 테이블을 이용하여 변경하고자 할 때 다음과 같이 SQL문을 작성할 수 있다. 

UPDATE TEAM A SET A.STADIUM_NAME = (SELECT X.STADIUM_NAME 
                                    FROM STADIUM X
                                    WHERE X.STADIUM_ID = A.STADIUM_ID);

 

 

 

 

 

 

 

 

5. INSERT 문의 VALUES 절에서 사용하기

PLAYER 테이블에 '홍길동' 이라는 선수를 삽입하고자 한다. 이때 PLAYER_ID의 값을 현재 사용중인 PLAYER_ID에 1을 더한 값으로 넣고자 한다. 그때 다음과 같이 SQL 문을 작성할 수 있다. 

INSERT INTO PLAYER(PLAYER_ID, PLAYER_NAME, TEAM_ID)
VALUES((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1) FROM PLAYER), '홍길동', 'K06');

 

 

 

 

 

 

 

 

 

 

 

 

6. 뷰 (View)

테이블은 실제로 데이터를 가지고있지만, 뷰는 실제 데이터를 가지고 있지 않다. 뷰는 단지 '뷰 정의'만을 가지고 있다. 

뷰는 아래와 같은 장점을 가진다. 

 

 

뷰는 다음과 같이 CREATE VIEW 문을 통해서 생성할 수 있다. 

CREATE VIEW V_PLAYER_TEAM AS  -- 뷰의 명칭은 V_PLAYER_TEAM
SELECT P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.TEAM_ID, T.TEAM_NAME
FROM PLAYER P, TEAM T 
WHERE P.TEAM_ID = T.TEAM_ID;

위의 V_PLAYER_TEAM은 선수 정보와 해당 선수가 속한 팀명을 함께 추출하는 것이다. 뷰는 테이블 뿐만 아니라 이미 존재하는 뷰를 참조해서도 생성할 수 있다. 

CREATE VIEW V_PLAYER_TEAM_FILTER AS  -- 뷰의 명칭은 V_PLAYER_TEAM_FILTER
SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_NAME
FROM V_PLAYER_TEAM
WHERE POSTION IN ('GK','MF');

위의 V_PLAYER_TEAM_FILTER 뷰는 선수 포지션이 골키퍼, 미드필더인 선수만 추출하고자 하는 뷰이다. 

 

 

 

뷰를 사용하는 방법은 다음과 같다. 

-- V_PLAYER_TEAM 뷰에서 성이 황씨인 선수만을 추출하는 SQL문

SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_ID, TEAM_NAME
FROM V_PLAYER_TEAM 
WHERE PLAYER_NAME LIKE '황%'

 

 

 

 

 

 

 

 

 

 

 

---

출처
이 글의 내용은 모두 한국데이터베이스진흥원이 출판한 SQL 전문가 가이드 2013 Edition을 기본으로 한다.