서브쿼리인데 셀프조인을 곁들인

 

오늘은 서브 쿼리와 셀프 조인에 대해서 정리를 하려고 한다.

 

SELF JOIN이란 동일 테이블 사이의 조인을 말한다.

 

따라서 FROM절에 동일 테이블이 두 번 이상 나타난다.

동일 테이블 사이의 조인을 수행하면 테이블과 칼럼의 이름이 모두 동일하기 때문에 식별을 위해서 반드시 ALIAS를 사용해야 한다.

보통 셀프조인을 할 경우는 외부 조인을 많이 사용한다.

기준을 보고 조건에 따라 존재하는 연관된 데이터를 가져오는 것이 일반적이기 때문에 외부 조인을 사용하고, 내부 조인 시에는 조건을 만족하지 않는 행은 출력에서 제외되기 때문이다.

 

Self JOIN은 특별한 문법이 없다. 단지 여러 가지 JOIN기능을 자신을 기준으로 하기 때문이다.

 

 

서브 쿼리 

 - 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말한다. 서브 쿼리는 알려지지 않은 기준을 이용한 검색을 위해 사용한다.  여기서 "알려지지 않은 기준"을 쉽게 풀면 이미 데이터가 존재하여 존재하는 데이터를 가지고 검색하는 것이 아닌 특별한 상황에 사용되는 기준을 검색을 하여 메인 쿼리에게 도움을 주는 형식이다.

 

서브 쿼리와 조인?

 

중요한 차이점이다. 사실 실무에서는 조인이냐 서브 쿼리냐, 서브 쿼리가 조인보다 느리다.라는 소리를 하는데 실행계획도 안 보고 그렇게 단정 지을 수는 없을 거 같고 , 나는 차이점과 환경을 분석해서 더 좋은 속도를 내기 위해서 자세히 알아보기로 했다.

 

우선 조인은 조인에 참여하는 모든테이블이 대등한 관계에 있기 때문에 조인에 참여하는 모든 테이블의 칼럼을 SQL의 어느 위치에서라도 자유롭게 사용할 수 있다. 

그러나 서브쿼리는 메인 쿼리의 칼럼을 모두 이용할 수 있지만 메인 쿼리는 서브 쿼리의 칼럼은 사용할 수 없다.

 

만약 질의결과에 서브 쿼리 칼럼을 표시해야 한다면 조인 방식으로 변환하거나 함수, 스칼라 서브 쿼리 등을 사용해야 한다.

 

조인은 집합 간의 곱(Product)의 관계이다. 이 말은 1:1 관계에서의 조인이면 1(=1x1) 레벨의 집합이 생성되고

1:M관계에서의 조인이면 M(=1xM) 레벨의 집합이 생성됨을 뜻한다.

그러나 서브 쿼리는 메인 쿼리에 종속적이다. 따라서 서브 쿼리의 레벨과 상관없이 항상 메인 쿼리 레벨로 결과 집합이 생성된다. 

 

서브 쿼리 주의사항

 

- 서브 쿼리를 괄호로 감싸서 사용해야 한다.

 

- 서브 쿼리는 단일행(Single Row) 또는 복수행 (Multiple Row) 비교 연산자와 함께 사용 가능하다 간단히 이야기하면 단일행 비교 연산자는 서브 쿼리의 결과가 반드시 1건 이하 어야 하고 복수행 비교 연산자는 서브 쿼리의 결과 건수와 상관이 없다.

 

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

 

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

 

SELECT절, FROM절, WHERE절, HAVING절, ORDER BY절, INSERT문의 VLAUES절 , UPDATE문에 SET절

 

 

동작 방식에 따른 분류

 

 Un-Correlated( 비연관) = 메인 쿼리에 서브 쿼리 결괏값을 제공하기 위한 목적

 

 Correlated(연관) = 서브 쿼리가 메인 쿼리 칼럼을 가지고 있는 형태이다. 일반적으로 메인 쿼리가 먼저 수행되어 읽힌 데이터를 서브 쿼리에서 조건이 맞는지 확인하고자 할 때 사용된다.

 

 

서브 쿼리는 메인 쿼리 안에 포함된 종속적인 관계이기 때문에 논리적인 실행 순서는 항상 메인 쿼리에서 읽힌 데이터에 대해 서브 쿼리에서 해당 조건이 만족하는지를 확인하는 방식으로 수행되어야 한다. 그러나 실제 서브 쿼리의 실행 순서는 상황에 따라 달라질 수 있다.

 

반환되는 데이터의 형태에 따라 3가지로 분류할 수가 있다.

 

 

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

 

서브 쿼리의 실행결과가 항상 1건 이하인 서브 쿼리를 의미한다. 단일행 서브 쿼리는 단일행 비교 연산자(=,<,>, <>...)와 함께 사용한다. 

 

서브 쿼리와 단일행 비교 연산자를 함께 사용할 때는 반드시 결과가 한건이어야 한다.

만약 2건 이상 반환하게 되면 RunTime Exception이 발생한다.

 

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

 

서브 쿼리의 실행결과가 여러 건인 서브 쿼리이다. 다중행 서브 쿼리는 다중행 비교 연산자(IN,ALL,ANY,SOME,EXISTS)와 함께 사용된다.

 

 다중행 비교연산자

•  IN(서브 쿼리) : 서브 쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미한다.
•  ALL(서브 쿼리) : 서브 쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미한다.
•  ANY=SOME(서브 쿼리) : 서브 쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미한다.
•  EXISTS(서브 쿼리) : 서브 쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미한다. 조건을 만족하는 건이 여러 건이라도 1건만 찾으면 더 이상 검색하지 않는다.

 

다중행 칼럼 서브 쿼리(Multi Column SubQuery)

 

서브 쿼리 실행결과로 여러 칼럼을 반환한다. 메인 쿼리 조건절에 여러 칼럼을 동시에 비교할 수 있다.

서브 쿼리와 메인 쿼리에서 비교하고자 하는 칼럼의 개수, 위치가 동일해야 한다.

 

다중 칼럼 서브 쿼리는 서브 쿼리의 결과로 여러 개의 칼럼이 반환되어 메인 쿼리의 조건과 동시에 비교되는 것을 의미한다. ex) WHERE (A, B) IN (SELECT * FROM TABLE);

 

 

연관 서브쿼리 (Correlated SubQuery)

 

- 서브 쿼리 내에 메인칼럼이 사용된 서브쿼리이다. 이 연관 서브쿼리 특징은 메인 쿼리에 존재하는 모든 행에 대해서 조건이 메인 쿼리에 맞는지 확인하는 작업을 반복적으로 수행한다.

 

하지만 EXISTS (서브 쿼리)를 사용하면 아무리 조건을 만족하는 건이 여러 건이라도 조건을 만족하는 1건만 찾으면 추가적인 검색을 하지 않는다.

 

 

기타 서브쿼리

 1. SELECT절에 서브쿼리 사용

 - Scalar SubQuery는 한 행, 한 칼럼만 반환하는 서브 쿼리를 말한다. 스칼라 서브쿼리는 칼럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있다. 스칼라 서브쿼리는 메인쿼리의 결과 건수만큼 반복수행 된다. 달일형이기에 2건이상 반환되면 역시 예외가 발생한다.

 

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

 - FROM절에서 사용되는 서브쿼리를 인라인 뷰(lnline View)라고 한다. 마치 서브 쿼리의 결과가 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있다. 인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다. 

 

인라인 뷰를 사용하는 것은 조인 방식을 사용하는 것과같다. 왜냐하면 동적으로 생성된 테이블이기 때문이기에 동급관계로 취급된다. 

인라인뷰 칼럼은 SQL문에 자유롭게 참조할 수 있다.

 

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

 

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

따라서 그룹 함수의 결괏값에 대한 추가적인 조건으로 결과 집합을 걸러내 산출할 수 있다.

SELECT t.TEAM_NAME , AVG(p.HEIGHT) FROM
PLAYER p JOIN TEAM t ON p.TEAM_ID = t.TEAM_ID 
GROUP BY t.TEAM_NAME,p.TEAM_ID
HAVING AVG(p.HEIGHT) < (SELECT AVG(p2.HEIGHT) FROM PLAYER p2 WHERE p2.TEAM_ID = 'K02'); 

 

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

 

바로 예시

UPDATE TEAM T SET T.STADIUM_NAME = (SELECT s.STADIUM_NAME FROM STADIUM s WHERE T.STADIUM_ID = s.STADIUM_ID); 

 

메인 쿼리에 반복적으로 서브 쿼리가 수행되어 테이블을 활용해 UPDATE를 수행했다.

다만 서브 쿼리에서 실행한 결과가 NULL이 되면 NULL값이 채워질 수 있기 때문에 주의해야 한다.

 

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

 

INSERT INTO TABLE(ID) VALUES((SELECT MAX(ID)+1 FROM TALBLE));

이런 식으로 INSERT도 가능하다.