서브 쿼리
서브 쿼리란 하나의 SQL 문안에 포함돼 있는 또 다른 SQL 문이다.
그림과 같이 메인 쿼리가 서브 쿼리를 포함하는 종속적인 관계.
조인은 조인에 참여하는 모든 테이블이 대등한 관계라서 참여하는 모든 테이블의 칼럼을 어느 위치에서라도 자유롭게 사용 가능하지만, 서브 쿼리는 메인 쿼리의 칼럼은 모두 사용 가능하지만 메인 쿼리는 아니다.
서브 쿼리를 사용할 때 다음 사항에 주의해야 한다.
- 서브 쿼리는 괄호로 감싸서 기술
- 서브 쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하지만 단일 행은 1건 이하가 나와야 한다.
- 중첩 서브 쿼리 및 스칼라 서브 쿼리에서는 ORDER BY를 사용하지 못한다.
서브쿼리의 종류
동작하는 방식이나 변환되는 데이터의 형태에 따라 분류한다.
동작하는 방식에 따라
- 비연관 서브 쿼리 : 서브 쿼리가 메인 쿼리 컬럼을 갖고 있지 않는 형태의 서브쿼리다. 메인 쿼리에 값을 제공하기 위한 목적으로 주로 사용.
- 연관 서브 쿼리 : 메인 쿼리 칼럼을 갖고 있는 형태의 서브 쿼리. 일반적으로 메인 쿼리가 먼저 수행돼 읽혀진 데이터를 서브 쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용
서브 쿼리는 메인 쿼리 안에 포함된 종속적 관계다. 따라서 논리적인 실행 순서는 항상 메인 쿼리에서 읽은 데이터를 서브 쿼리에서 해당 조건이 만족하는지 확인하는 방식으로 수행돼야 한다. 하지만 실제 서브 쿼리의 실행 순서는 상황에 따라 달라 질 수 있다.
반환되는 데이터의 형태에 따라
단일 행 서브 쿼리 : 실행 결과가 항상 1건 이하인 서브 쿼리
다중 행 서브 쿼리 : 서브 쿼리의 실행 결과가 여러 건인 서브 쿼리를 의미
다중 컬럼 서브 쿼리 : 서브 쿼리의 실행 결과로 여러 컬럼을 반환한다.
단일 행 서브 쿼리
단일 행 비교 연산자(=, <, <=, >, >=, <> 등) 함께 사용된다.
SELECT LNAME AS 선수명
, POSITION AS 포지션
, JERSEY AS 백넘버
FROM PLAYERS
WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYERS WHERE LNAME = 'Davis')
ORDER BY LNAME;
다중 행 서브 쿼리
서브 쿼리의 결과가 2건 이상 반환된다.
다중 행 비교 연산자
IN (서브 쿼리) : 서브 쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미한다.
비교연산자 ALL (서브 쿼리) : 서브 쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미한다.
비교연산자 ANY(서브 쿼리) : 서브 쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미한다.
EXISTS (서브 쿼리) : 서브 쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미한다. 조건을 만족하는 건이 여러 건이더라도 1건만 찾으면 더이상 검색하지 않는다.
SELECT CITY AS 도시명
, NAME AS 팀명
FROM TEAMS
WHERE ID = (SELECT TEAM_ID FROM PLAYERS WHERE LNAME = 'Herrara')
ORDER BY NAME;
다중 칼럼 서브 쿼리
다중 칼럼 서브 쿼리는 서브 쿼리의 결과로 여러 개의 칼럼이 반환돼 메인 쿼리의 조건과 동시에 비교되는 것을 의미한다.
SELECT TEAM_ID AS 팀코드
, LNAME AS 선수명
, POSITION AS 포지션
, JERSEY AS 백넘버
, HEIGHT AS 키
FROM PLAYERS
WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT)
FROM PLAYERS GROUP BY TEAM_ID)
ORDER BY TEAM_ID, LNAME;
연관 서브 쿼리
서브 쿼리 내에 메인 쿼리 칼럼이 사용된 서브 쿼리다.
SELECT B.NAME AS 팀명
, A.LNAME AS 선수명
, A.POSITION AS 포지션
, A.JERSEY AS 백넘버
, A.WEIGHT AS 키
FROM PLAYERS A, TEAMS B
WHERE A.WEIGHT < (SELECT AVG(X.WEIGHT) FROM PLAYERS X
WHERE X.TEAM_ID = A.TEAM_ID GROUP BY X.TEAM_ID)
AND B.ID = A.TEAM_ID
ORDER BY 선수명;
그 밖의 위치에서 사용하는 서브 쿼리
SELECT 절에서 사용하는 스칼라 서브 쿼리
스칼라 서브 쿼리란 1 ROW 1 COLUMN만을 반환하는 서브 쿼리이다.
스칼라 서브 쿼리는 대부분의 곳에서 사용 가능하다.
SELECT A.LNAME AS 선수명
, A.WEIGHT AS 키
, ROUND((SELECT AVG(X.WEIGHT) FROM PLAYERS X
WHERE X.TEAM_ID = A.TEAM_ID), 3) AS 팀평균키
FROM PLAYERS A;
FROM 절에서 서브 쿼리 사용하기
인라인 뷰라고 한다. 인라인 뷰를 사용하면 서브 쿼리의 결과를 마치 테이블처럼 사용할 수 있다.
SELECT B.NAME AS 팀명
, A.LNAME AS 선수명
, A.JERSEY AS 백넘버
FROM (SELECT TEAM_ID, LNAME, JERSEY
FROM PLAYERS
WHERE POSITION = 'Infielder') A
, TEAMS B
WHERE B.ID = A.TEAM_ID
ORDER BY 선수명;
HAVING 절에서 서브 쿼리 사용하기
집계함수와 함께 사용될 때 그룹핑 된 결과에 대해 부가적인 조건을 주기 위해 사용한다.
SELECT A.TEAM_ID AS 팀코드
, B.NAME AS 팀이름
, ROUND(AVG(A.WEIGHT),3) AS 평균키
FROM PLAYERS A, TEAMS B
WHERE B.ID = A.TEAM_ID
GROUP BY A.TEAM_ID, B.NAME
HAVING AVG(A.WEIGHT) < (SELECT AVG(X.WEIGHT)
FROM PLAYERS X
WHERE X.TEAM_ID IN
(SELECT ID FROM TEAMS
WHERE NAME = 'Giants'));
뷰
테이블은 실제로 데이터를 갖고 있는 반면, 뷰는 실제 데이터를 갖고 있지 않다.
뷰 사용의 장점
- 독립성 : 테이블 구조가 변경돼도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
- 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다. 또한 해당 형태의 SQL 문을 자주 사용할 때 뷰를 이용하면 편리하게 사용할 수 있다.
- 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 칼럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다.
뷰는 다음과 같이 생성할 수 있다.
CREATE VIEW V_PLAYER_TEAM AS
SELECT A.PLAYER_NAME, A.POSITION, B.TEA_NAME
FROM PLAYER A, TEAM B
WHERE B.TEAM_ID = A.TEAM_ID;뷰는 뷰를 기반으로도 생성할 수 있다.
뷰를 사용하는 방법은 다음과 같다.
SELECT PLAYER_NAME, POSITION, TEAM_NAME
FROM V_PLAYER_TEAM
WHERE PLAYER_NAME LIKE '황%';뷰를 제거하기 위해서는 DROP VIEW 문을 사용한다.
DROP VIEW V_PLAYER_TEAM;