SQL SubQuery 에 대해서

서브쿼리는 말 그대로 쿼리 내부에 또다른 쿼리를 가지는 것을 의미한다.
서브쿼리들은 쿼리들을 단계별로 수행하도록 쿼리를 중첩시키는 역할을 수행한다.

즉, 하나의 큰 쿼리가 있으면, 이 쿼리의 일정 부분들을 분리하여 수행할수 있도록 해주는것이다. 이는 프로그램에서 서브루틴이나 함수들이 수행하는 역할과도 유사하다.

쿼리안에 또 다른 쿼리를 작성할수 있는 능력을 가짐으로써 T-SQL은 인간이 이해하고 작성하기 쉬워졌다.

서브쿼리가 가지는 또 다른 장점은 한번 디스크에서 읽어온 데이터를 메모리 안에서 가공해서 사용할수 있도록 도와준다.

즉, 동일한 데이터를 다시 한번 이용하여 복잡한 가공에도 물리적인 I/O를 줄여준다.
서브쿼리는 크게 상관쿼리(Correlated subquery)와 인라인뷰(Inline View subquery)로 나눈다.

이러한 쿼리 구분은 상당히 작위적인 구분이다. 하나의 쿼리에서 상관된 서브쿼리를 인라인뷰로 해서 쿼리를 작성하는것이 가능하다.

그러므로 이러한 구분은 서브쿼리의 분류를 나누는것이 아니라, 서브쿼리의 특성을 설명하는 용어라고 이해하는게 맞다.

오히려 크게 구분을 나눈다면, 상관되지 않은 서브쿼리와 상관된 서브쿼리로 나누는것이 옳다.

상관되지 않은 서브쿼리들은 다시 Flattened subquery와 Materialized subquery로 나뉜다.

상관되지 않은 서브쿼리

서브쿼리 평면화(Subquery Flattening)

서브 쿼리가 상관되어 있다는 것은, 해당쿼리의 구조가 메인쿼리와 서브쿼리로 나뉘어지고, 서브쿼리가 메인쿼리를 참조하여 결과를 나타낸다는 의미다. 서브쿼리가 상관되어 있지 않다면, 메인쿼리와 서브쿼리가 서로 참조되지 않는다는 의미이다.
상관되지 않는 서브쿼리들은 다시 Flattened subquery 와 Materialized subquery로 나뉠수 있다. 두 종류의 서브쿼리를 명확히 구분하는것은 어렵지만, 각각의 특성들은 쉽게 이해할수 있을것이다.

플랫된 서브쿼리(Flattened subquery) 는 서브쿼리가 조인연산으로 변형되어, 옵티마이저가 내부적으로 조인연산을 통해서 전체쿼리를 수행하게 된다. 겉으로 보기에는 서브쿼리이지만 실제로는 조인쿼리이다.



select pub_name
from pubs..publishers
where pub_id in (select pub_id from pubs..titles where type='business')

평범하게 생각할때 일반적으로 서브쿼리의 수행은

1. 내부쿼리들이 수행된다.
2. 그 후 외부의 쿼리들이 실행된다.

라고들 생각한다. 하지만 실행계획은 전혀 그렇지 않다.

실행계획은 조인메소드이다.

위의 쿼리를 조인쿼리로 변경하면 다음과 같다. Distinct 연산후 Publishers 테이블과 조인하게 된다.



select *
from pubs..publishers as a,
(select distinct pub_id from pubs..titles where type='business') as b
where a.pub_id = b.pub_id

이러한 Flattened subquery는 보통 IN, ANY, SOME, EXISTS 등과 같은 존재쿼리(Existing query)에서 플랫 현상이 발생한다.
그럼 존재 절안에 실제 가져오는 값을 넣으면 어떻게 될까?

그러면 해당쿼리는 당연히 OR 절로 풀리게 된다.



select *
from pubs..publishers
where pub_id in('1389', '0736')

해당쿼리가 플랫되는 경우는 오로지 서브쿼리인 경우일 뿐이다.

하지만 일반적으로 이러한 경우 다른 데이터베이스에서 서브쿼리들이 플랫되지 않는다.(최신버전이서는 이기능이 상당히 채택되어있기는하다.)
위 쿼리에서 가장빠른 수행계획은 일반적으로 서브쿼리의 실행결과를 캐싱하여 이를 OR절로 풀어서 메인쿼리를 수행하는것이다.

당연히 이경우 서브쿼리의 조건절이 SARG이어야만한다. 하지만 이런방법에는 맹점이 있는데, 서브쿼리의 모든 로우들을 스캔하여 메인쿼리를 수행하는 경우라면, 아니면 이와 유사하게 대량의 데이터를 읽어야 하는 경우라면, 이 방법은 최악의 방법중의 하나가 된다.
이 문제를 해결하기 위해 많은 최신데이터베이스에 채택된것이 바로 서브쿼리 평면화(Subquery Flattening) 기술이라 할수있다.

쿼리를 조인연산으로 변경하여 수행할때, 이전 서브쿼리를 먼저 처리해서 발생할수 잇는 최악의 상황을 해결할수 있고 조인연산의 성능을 기대할수 있다.

이러한 가정은 서브쿼리의 성능이 일반적으로 조인쿼리의 성능에 떨어진다는 사실에 기반한다.

조인연산은 여러가지 견지에서 서브쿼리의 성능을 높여줄수 있다.

일단 이 조인은 FirstRow & Semi Join 이다. 조인 연결시에 첫 행이외의 로우들은 연산할 필요가 없다. 게다가 Distinct 연산에 의해서 중복값들은 건너뛰게(Skip duplicates) 되어있다.

실체화된 서브쿼리 Materialized Subquery

상관 되지 않는 서브쿼리의 또다른 하나는 실체화된 서브쿼리이다. 메인 테이블과의 비교전에 서브쿼리의 결과가 우선 실체화 되는 쿼리이다. 이러한 쿼리들은 우선해서 서브쿼리의 결과를 실체화 해야만 한다.

“Pubs 데이터베이스 Titles 테이블에서 최고의 연간 판매량(Ytd_sales)을 가지는 책을 찾아라” 는 질문에 적합한 쿼리를 작성하자.

select *
from pubs..titles
where Ytd_sales = (select MAX(ytd_sales) from pubs..titles)

이 쿼리에서 서브쿼리는 먼저 실체화 할수있다. 이쿼리의 결과는 단일 스칼라 값으로 반환될수있다.

상관 서브쿼리

서브쿼리가 상관되었다는 이야기는 메인쿼리부분과 서브쿼리 부분이 연관되어 있다는 뜻이며, 이것은 곧 참조를 뜻한다. 이는 조인 연산의 조인키 개념과 비슷하다.
서브쿼리의 참조는 쿼리의 어느부분에서도 가능하다.
SELECT 목록에도 포함할수 있으며, WHERE절 및 HAVING에도 포함이 가능하다.

select *
from pubs.dbo.titles t
where exists (
select * from pubs.dbo.sales where qty > 30 and
t.title_id = title_id
)

이것은 WHERE 절에서 사용된 상관 서브쿼리다.
동일한 테이블을 대상으로 참조하고 있다.

pubs.dbo.titles는 t 라는 인칭으로 지칭되며, 서브쿼리 안에서 pubs.dbo.sales 테이블과 맺어진다. 서로 같아야만 전체쿼리가 실행될수 있으며, 그러려면 서브쿼리가 먼저 수행되어도 안되고, 메인쿼리만 먼저 수행되어도 안된다.

이러한 이유로 쿼리의 실행계획을 살펴보면 바로 JOIN 이다.

select title_id, pub_name = (select pub_name from pubs.dbo.publishers where t.pub_id = pub_id)
from pubs.dbo.titles t

select 목록에 사용된 상관 서브쿼리다. 일반적으로 select 목록에 인라인뷰나 서브쿼리를 사용하면 성능에 안좋다는 설이 있지만, 각각 데이터베이스 버전에 따라 옵티마이저의 처리방식이 다르기에 실제로 실행결과를 보고 확인을 해야 한다.

select title
from pubs.dbo.titles
group by title
having max(ytd_sales) > all
(
select max(ytd_sales)
from pubs.dbo.publishers p inner join pubs.dbo.titles t
on t.pub_id = p.pub_id
where p.pub_name = 'New Moon Books'
)

이것은 Having 절에서 사용된 서브쿼리이다.
쿼리의 의미는
” ‘New Moon Books’ 출판사의 책들중 가장 많은 연간 판매량을 기록한 것들보다 더 많은 판매량을 기록한 책들은 무엇인가? “

인라인 뷰

모든 인라인 뷰들은 서브 쿼리라고 볼수있다. 인라인 뷰라는것이 FROM 절 이하의 부속질의로써 사용되는 Data source 이기 때문이다.

인라인뷰의 개념은
데이터 페이지로 부터 데이터를 한번 읽어서 여러번 이용하겠다는 것
이것이 바로 인라인 뷰의 장점이자 존재이유다.

“pubs.dbo.titles 테이블에서 개수별 도서수가 얼마나 되는지를 파악해보라.”

Sales 테이블에는 도서 판매정보들이 저장되어 있다. 원하는 답은 도서별 판매수량을 따진후, 해당 판매 수량별 도서가 몇가지나 되는지 알고자 하는것이다.

select tcnt, COUNT(*) title_cnt
from
(
select title_id, COUNT(*) tcnt
from pubs.dbo.sales
group by title_id
) as x
group by x.tcnt;