Oracle 서브쿼리(Sub Query) 사용법 & 예제

서브쿼리(Sub Query) 개념

서브쿼리와 메인쿼리 관계

1) SQL문 내에 쓰인 SELECT 문을 의미하며, 이때 바깥에 있는 SQL문을 메인 쿼리(Main Query)라 부른다.

2) MainQuery 안에 포함된 종속적인 관계이기 때문에 논리적인 실행순서는 항상 MainQuery에서 읽혀진 데이터에 대해 SubQuery에서 해당 조건이 만족하지를 확인하는 방식으로 수행되어야 한다. 그러나 실제 SubQuery의 실행순서는 상황에 따라 달라질 수 있음. → SubQuery의 검색된 결과 값이 MainQuery에 사용될 때의 SubQuery는 MainQuery 실행 전에 실행됨.

서브쿼리 종류	설명
비연관(Un-Correlated) 서브쿼리	서브쿼리가 메인쿼리 칼럼을 가지고 있지 않는 형태의 서브쿼리를 의미한다. 메인쿼리에 값(서브쿼리가 실행된 결과)을 제공하기 위한 목적으로 주로 사용한다.
연관(Correlated) 서브쿼리	서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태의 서브쿼리를 의미한다. 일반적으로 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인 할 때 주로 사용된다.

3) 조인은 조인에 참여하는 모든 테이블이 대등한 관계에 있기 때문에 조인에 참여하는 모든 테이블의 칼럼을 어느 위치에서라도 자유롭게 사용할 수 있다. 그러나 SubQuery는 MainQuery에서 from절에 명시된 테이블의 모든 칼럼을 모두 사용할 수 있지만, MainQuery는 SubQuery의 from절에 명시된 테이블의 칼럼을 사용할 수는 없다(함수, 스칼라 서브쿼리(Scalar Subquery) 제외)

서브쿼리(Sub Query) 분류

1) 단일 행(Single Row) 서브쿼리

SELECT player_name, position, back_no

FROM player

WHERE height <= (서브쿼리)

① 서브 쿼리 문장에서 단 하나의 행과 열을 검색하는 SQL문장.

② 서브쿼리의 실행결과가 항상 1건 이하인 서브쿼리를 의미. → 서브쿼리의 결과가 2건 이상이면 런타임에 실행오류 뜸

③ 단일 행 연산자(=, <, >, <=, >=, !=)를 오른쪽에 기술한다.

2) 다중 행(Multi Row) 서브쿼리

서브쿼리의 결과로 2건 이상 반활될때는 ‘단일 행 서브쿼리’처럼 비교연산자(= 등)만으로는 처리가 불가능하기 떄문에 에러 반환됨. 이 때는 ‘다중 행 서브쿼리’에 맞는 비교연산자를 추가해야 함. 연산자를 제외하면 단일 행 서브 쿼리와 같다.

① IN (서브쿼리) : 서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건. (Multiple OR조건) 다시 말해, 서브쿼리의 모든 결과값에 대해 각각 일치하는 값이 있는지 Check.

② ANY (서브쿼리) : 서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건. (어떠한 값)

③ ALL (서브쿼리) : 서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건. → 보통 ANY와 ALL 연산자는 IN 연산자와 달리 어떤 특정한 값이 아닌 범위로 비교연산을 처리한다.

SELECT empno, ename, job, sal

FROM emp

WHERE sal > ANY(SELECT sal FROM emp WHERE job = 'MANAGER')

AND job <> 'MANAGER';

④ EXISTS (서브쿼리) : 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인. 조건을 만족하는 건이 여러 건이더라도 1건만 찾으면 더 이상 찾지 않음.

3) 다중 열, 칼럼(Multi Column) 서브쿼리

① 서브쿼리 결과로 여러 개의 칼럼이 반환되어 메인 쿼리의 조건과 동시에 비교되는 방식으로, 반드시 비교 대상 컬럼과 1:1 대응돼야 함.

② '='도 사용 가능하지만 주로 'IN'을 사용하고 권장함.

4) 연관 서브쿼리

① 메인 쿼리의 칼럼이 사용된 서브쿼리를 의미.

② EXISTS 서브쿼리는 항상 연관 서브 쿼리로 수행된다. 조건을 만족하는 건이더라도 1건만 찾으면 더 이상 찾지 않는 것을 항상 인지.

5) SELECT 절에서 사용하는 서브쿼리 = 스칼라 서브쿼리(Scalar Subquery)

한 행, 한 칼럼(1 Row 1 Column)만 반환하는 서브쿼리를 의미한다.

SELECT PLAYER_NAME 선수명, HEIGHT 키, (SELECT AVG(HEIGHT) FROM PLAYER X WHERE X.TEAM_ID = P.TEAM_ID) 팀평균키

FROM PLAYER P;

6) FROM 절에서 사용하는 서브쿼리 = 인라인 뷰(Inline VIew)

① 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용 가능.

② 인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰. 그래서 일반적인 뷰를 정적 뷰, 인라인 뷰를 동적 뷰라고도 함.

③ 일반적으로 메인 Query보다 먼저 수행되므로 SQL문장 내에서 절차성을 주는 효과를 준다.

7) Having절에서 사용하는 서브쿼리

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

SELECT P.TEAM_ID 팀코드, T.TEAM_NAME 팀명, AVG(P.HEIGHT) 평균키

FROM PLAYER P, TEAM T

WHERE P.TEAM_ID = T.TEAM_ID 

GROUP BY P.TEAM_ID, T.TEAM_NAME

HAVING AVG(P.HEIGHT) < (SELECT AVG(HEIGHT) FROM PLAYER WHERE TEAM_ID ='K02')

8) UPDATE문의 SET절에서 사용하기

UPDATE TEAM A

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

서브쿼리를 사용한 변경 작업을 할 때 서브쿼리의 결과가 NULL을 반환할 경우 해당 컬럼의 결과가 NULL이 될 수 있기 때문에 주의해야 한다.

9) INSERT문의 VALUES절에서 사용하기

INSERT INTO PLAYER(PLAYER_ID, PLAYER_NAME, TEAM_ID) 

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