서브쿼리
: 하나의 SQL문안에 포함돼 있는 또 다른 SQL문을 말한다.
서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만 메인 쿼리는 서브쿼리의 컬럼을 사용할 수 없다.
질의결과에 서브쿼리컬럼을 표시해야한다면 조인방식으로 변환하거나, 함수, 스칼라서브쿼리 등을 사용해야 한다.
서브쿼리를 사용할 때 다음 사항에 주의해야 한다.
- 서브쿼리는 괄호로 감싸서 기술한다.
- 서브쿼리는 단일행 또는 복수행 비교연산자와 함께 사용가능하다.
단일행비교연산자는 서브쿼리의 결과가 반드시 1건이하이어야 하고,
복수행비교연산자는 서브쿼리의 결과 건수와 상관없다.- 중첩서브쿼리 및 스칼라서브쿼리에서는 ORDER BY를 사용하지 못한다.
서브쿼리의 종류는 동작하는 방식이나 반환되는 데이터의 형태에 따라 분류할 수 있다.
-
동작하는 방식에 따라 서브쿼리를 분류하면 아래와 같이 두 가지로 나눌 수 있다.
서브쿼리 종류 설명 Un-Correlated(비연관)서브쿼리 서브쿼리가 메인쿼리 컬럼을 갖고 있지 않는 형태의 서브쿼리이다. 메인쿼리에 값을 제공하기 위한 목적으로 주로 사용한다. Correlated(연관)서브쿼리 서브쿼리가 메인쿼리 컬럼을 갖고 있는 형태의 서브쿼리이다. 일반적으로 메인쿼리가 먼저 수행돼 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할때 주로 사용한다. -
반환되는 데이터의 형태에 따라 서브쿼리는 세가지로 나눌 수 있다.
서브쿼리종류 설명 Single Row서브쿼리 서브쿼리의 실행결과가 항상 1건 이하인 서브쿼리 단일행 서브쿼리는 단일행 비교연산자와 함께 사용된다. (단일행 비교연산자는 =, <, <=, >, >=, <>등이 있다.) Multi Row서브쿼리 서브쿼리의 실행결과가 여러건인 서브쿼리를 의미한다. 다중행서브쿼리는 다중행 비교연산자와 함께 사용된다. (다중행 비교연산자에는 IN, ALL, ANY, SOME, EXISTS가 있다.) Multi Column서브쿼리 서브쿼리의 실행결과로 여러 컬럼을 반환한다. 메인쿼리의 조건절에 여러 컬럼을 동시에 비교할 수 있다. 서브쿼리와 메인쿼리에서 비교하고자 하는 컬럼개수와 컬럼의 위치가 동일해야 한다.
단일 행 서브쿼리
-
서브쿼리가 단일행 비교연산자와 함께 사용될 때는 서브쿼리의 결과 건수가 반드시 1건 이하여야한다. 2건이상을 반환하면 런타임오류가 발생한다.
Scott이 소속된 팀의 선수들에 대한 정보를 출력
입력✏️
SELECT FNAME AS 선수이름 ,POSITION AS 포지션 FROM PLAYERS WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYERS WHERE FNAME='Scott') ORDER BY FNAME;
출력💻
만일 Scott과 같은 이름을 가진 동명이인이 있었다면 2건이상의 결과가 반환돼 오류가 발생할 것이다.
그러므로 사용에 유의해야되고 가급적 결과값이 단 1건만 생성되는 집계함수를 적용하여 단일행 서브쿼리를 사용하도록 해야한다.
다중 행 서브쿼리
-
서브쿼리의 결과가 2건이상 반환될 수있다면, 반드시 다중행 비교연산자와 함께 사용해야 한다.
-
다중 행 비교연산자 종류
- IN(서브쿼리)
: 서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미한다.- 비교연산자 ALL(서브쿼리)
: 서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미한다.- 비교연산자 ANY(서브쿼리)
:서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미한다.- EXISTS(서브쿼리)
: 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미한다. 조건을 만족하는 건이 여러 건이더라도 1건만 찾으면 더이상 검색하지 않는다.
다중 컬럼 서브쿼리
- 다중컬럼 서브쿼리는 서브쿼리의 결과로 여러개의 컬럼이 반환돼 메인쿼리의 조건과 동시에 비교되는 것을 의미한다.
소속팀별 키가 가장 작은 사람들의 정보를 출력
입력✏️
SELECT TEAM_ID
, FNAME
, POSITION
, WEIGHT
FROM PLAYERS
WHERE (TEAM_ID, WEIGHT) IN (SELECT TEAM_ID
,MIN(WEIGHT)
FROM PLAYERS
GROUP BY TEAM_ID)
ORDER BY TEAM_ID, FNAME;출력💻
뷰 (View)
- 뷰는 실제 데이터를 갖고 있지 않다 단지 뷰 정의만을 갖고있다.
- 뷰 사용의 장점
- 독립성
: 테이블 구조가 변경돼도 뷰를 사용하는 응용프로그램은 변경하지 않아도 된다.- 편리성
: 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다. 또한 해당 형태의 SQL문을 자주 사용할 때 뷰를 이용하면 편리하게 사용할 수 있다.- 보안성
: 직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 컬럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다.
뷰 생성
CREATE VIEW 뷰이름 AS
SELECT 필드이름1, 필드이름2, ...
FROM 테이블
WHERE 조건이미 존재하는 뷰를 참조해서도 생성가능
CREATE OR REPLACE VIEW 뷰이름 AS
SELECT 필드이름1, 필드이름2, ...
FROM 테이블
WHERE 조건뷰 삭제
DROP VIEW 뷰이름;