[SQL] with절과 서브쿼리

부제: 서브쿼리가 너무 싫어서 서브쿼리 안 쓰는 방법을 필사적으로 찾아봄

서브쿼리를 작성할 때 밑에서부터 타고 올라가다 보니 내가 어디까지 정의했는지 헷갈린 적이 많았다. 그보다 더 심각한 건 다른 사람이 작성한 쿼리를 파악할 때인데 쿼리문의 중간 어드메 그 뿌리를 찾아가는게 너무 괴로운 경험이었다. 그래서 도대체 왜 with절을 안 쓰고 서브쿼리를 쓰는지,,, 크게 두 가지 의문이 들었다.

with절과 서브쿼리, 어떤 차이가 있는지?

일단 서브쿼리 자체는 세 종류이다. select절, from절, where절에 위치하고 각각 컬럼처럼, 테이블처럼, 조건의 value처럼 사용된다. 이 중에서도 with절과 쓰임이 같은 것은 from절 서브쿼리이다. 그래서 내가 의문을 가진 부분도 from절 서브쿼리에 해당하는 내용이 되겠다.

with절과 from절 서브쿼리는 형태는 비슷하다. 테이블 형태로 알리아스를 지정해주어야 한다.

wth절

WITH 테이블명 AS (
)

SELECT 컬럼명
FROM 테이블명

from절 서브쿼리

SELECT 컬럼명
FROM (
) AS

with절의 경우 전제 조건에 해당하는 쿼리가 실제 조회를 위한 쿼리와 구분되므로 가독성이 좋다. 즉 위에서부터 순서대로 읽어내려가면 내용을 이해할 수 있다는 뜻이다.

from절 서브쿼리의 경우 select절과 where절이 from절을 감싸는 형태이기 때문에 전제 조건에 해당하는 쿼리가 중간에 위치하게 된다. 그 결과 앞서 언급한대로 내용을 파악하기 위해서 중간 지점을 찾아 거슬러 올라가야 하는 것이다.

그리고 서브쿼리는 서로 참조할 수 없기 때문에 필요 시에는 같은 내용을 계속 반복해서 작성해야 한다. 이게 아주 비효율적인 부분이다.

특히 궁금했던 것은 성능 차이로, 서브쿼리를 써야만 하는 이유가 성능이라면 납득이 될 것 같았는데 결론적으로 with절을 사용하는 게 성능이 더 우수하다고 한다. 정확히 말하면 with절 자체가 서브쿼리의 팩토링 방식으로 제기된 것이다.

The WITH clause is for subquery factoring, also known as common table expressions or CTEs:

The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying query_name. Oracle Database optimizes the query by treating the query name as either an inline view or 
as a temporary table.

→ 임시 테이블로 기능한다

In many cases the choice of which to use comes down to your preferred style, and CTEs can make code more readable particularly with multiple levels of subqueries (opinions vary of course). If you only refer to the CTE/inline view once you probably won't see any difference in performance, and the optimiser may end up with the same plan.

→ CTE(with절)나 인라인 뷰(서브쿼리)를 한 번만 사용할 경우 성능차이는 없다

They are particularly useful though when you need to use the same subquery in more than one place, such as in a union. You can pull an inline view out into a CTE so the code isn't repeated, and it allows the optimiser to materialize it if it thinks that would be beneficial.

→ (with절은)한 군데 이상 동일한 서브쿼리를 사용할 경우 특히 유용하다

 

with문을 사용하지 못하는 경우가 있는지?

오라클에서는 9i R2 버전 이후로 사용할 수 있다고 하는데 이미 20년도 더 이전이라 사용 가능여부는 문제가 되지 않는다고 판단된다.

써놓고 보니까 나의 킹받음에 비해 별 내용이 없긴 한데 가독성을 위해서라도 with절을 적극적으로 사용해보려고 한다. 막상 with절을 쓰다보면 또 다른 불편함이 있을수도…

---

참고

Difference between WITH clause and subquery?

[Oracle] 오라클 WITH 절 사용법 및 동작방식 (임시 테이블)

서브쿼리의 종류와 WITH 쿼리