쿼리 필터링과 조건절에 대한 이해

순서에 따르는 필터링

Itzik Ben-Gan

T-SQL 쿼리문에는 SQL 서버가 필터로 사용하는 논리적인 식을 적용할 수 있는 조건절로서 ON절, WHERE절, HAVING절의 세 가지가 있다. 이 세 가지 형태의 필터링은 각각 다른 역할을 수행하지만 때로는 논리 식을 어느 절에 기술해야 할지 혼란스러운 경우들이 있다. 대부분의 T-SQL 프로그래머들은 SQL 서버가 입력 데이터를 그룹핑한 다음에 HAVING 절을 적용하므로 언제 HAVING 절에 논리 식을 기술해야 하는지에 대해서는 잘 알고 있다. 그러나 어떤 경우에 ON절에 기술해야 하고 어떤 경우에는 WHERE절에 기술해야 하는지에 대해서는 혼란스러워하며 SQL 서버 프로그래밍 뉴스그룹(news://msnews.microsoft.com/microsoft.public.sqlserver.programming)에도 종종 이에 대한 질문들이 올라오고 있다.
SQL 서버 엔진팀에서 매니저로 일하고 있는 Lubor Kollar 덕분에 필자가 개발자들을 자주 당황스럽게 하는 이 주제에 대한 기사를 기고하게 되었다.

쿼리 프로세싱의 논리적인 순서

어느 절에 필터링 조건을 기술해야 하는 것을 알아내기 위해서는 ANSI에서 쿼리의 조건절을 처리하는데 대하여 정의해 둔 논리적인 순서에 대하여 이해할 필요가 있다. 이 기사에서는 물리적인 쿼리 프로세싱은 배제하고 논리적인 쿼리 프로세싱의 관점에서만 논의하고자 한다. 물리적인 프로세싱 과정에서는 옵티마이저가 옳은 결과를 내는 방법들 중에서 실행 계획을 생성하는데 있어서의 지름길을 선택하게 된다.
이 기사에는 Customers 라는 테이블과 Orders 라는 테이블을 예로 들겠다. [리스트 1]의 코드를 수행하면 이 두 개의 테이블들이 만들어지고 테스트를 위한 데이터가 생성된다. Customers 테이블에는 네 명의 고객에 대한 데이터가 저장되어 있으며, 고객 A, B, C는 시애틀에 거주하고 있고 고객 D는 덴버에 거주하고 있다. Orders 테이블에는 다섯 건의 주문 정보가 저장되어 있는데, 고객 A는 한 건, 고객 B는 세 건의 주문을 접수한 상태이다. 확인되지 않은 어떤 고객이 한 건의 주문을 접수했으며 그렇기 때문에 이 레코드의 custid 컬럼의 값은 NULL이다.

리스트 1: Customers 테이블과 Orders 테이블 생성 및 데이터 저장 스크립트
---------------------------------------------------------------------------
USE tempdb
GO
IF OBJECT_ID('Orders') IS NOT NULL
DROP TABLE Orders
GO
IF OBJECT_ID('Customers') IS NOT NULL
DROP TABLE Customers
GO
CREATE TABLE Customers
(custid varchar(5) NOT NULL PRIMARY KEY,
city varchar(10) NOT NULL,
/* 기타 컬럼들 */)

SET NOCOUNT ON
INSERT INTO Customers(custid, city) VALUES('A', 'Seattle')
INSERT INTO Customers(custid, city) VALUES('B', 'Seattle')
INSERT INTO Customers(custid, city) VALUES('C', 'Seattle')
INSERT INTO Customers(custid, city) VALUES('D', 'Denver')

CREATE TABLE Orders
(orderid int NOT NULL PRIMARY KEY,
custid varchar(5) NULL REFERENCES Customers,
/* 기타 컬럼들 */)

INSERT INTO Orders(orderid, custid) VALUES(1, 'A')
INSERT INTO Orders(orderid, custid) VALUES(2, 'B')
INSERT INTO Orders(orderid, custid) VALUES(3, 'B')
INSERT INTO Orders(orderid, custid) VALUES(4, 'B')
INSERT INTO Orders(orderid, custid) VALUES(5, NULL)
GO

대부분의 프로그래밍 환경에서 서버는 개발자가 작성한 순서대로 코드를 수행한다. 그러나 SQL 서버의 경우에는 개발자가 작성한 순서대로 절을 처리하지 않는다. 쿼리문의 시작 부분인 SELECT 리스트에는 결과로 보고자 하는 대상들을 기술하지만 실제로 SQL 서버는 최종 결과를 생성하기 전에 데이터를 액세스하고 조인하고 필터링 조건을 적용하고 그룹핑하는 작업들을 먼저 수행한다. [그림 1]에 모든 ANSI 절이 포함된 쿼리의 일반적인 구문이 나와 있다.

[그림 1] 일반적인 쿼리 구문

먼저 필터링을 위한 논리 식을 지정할 수 있는 세 가지 조건절의 논리적인 프로세싱 단계를 살펴 보자. 각 단계에 대하여 간략하게 설명한 다음에 예제 코드들을 가지고 보다 자세하게 설명하겠다. ANSI에서 정의한 바에 의하면, 모든 쿼리의 논리적인 프로세싱 단계는 [그림 1]과 같다.

1. T1과 T2 사이에 카테시안 곱(Cross Join)을 생성하고, T1에서 얻은 모든 행을 T2에서 얻은 모든 행들과 매칭시킨다. 그 결과 얻어지는 가상 테이블을 V1이라고 하겠다. V1에 있는 행들의 수는 T1에 있는 행들의 수와 T2에 있는 행들의 수를 곱한 결과가 된다.

2. V1의 행들에 대하여 조인 조건(ON 절에 기술된 필터링 조건)을 적용한다. 이 필터링 조건에 대하여 결과값이 TRUE인 행들에 대해서만 다음 단계로 진행된다. 그 가상 결과 테이블을 V2라고 하자.

3. 만약 조인 유형이 Outer Join인 경우에는 테이블들이 preserved 또는 unpreserved.로 표시된다. Left Outer Join의 경우에는 SQL 서버가 왼쪽 테이블(T1)을 preserved로 표시하고 Right Outer Join의 경우에는 오른쪽 테이블(T2)을 preserved로 표시한다. Full Outer Join의 경우에는 양쪽 테이블 모두 preserved로 표시된다. V2에 있는 행들에게 unpreserved table에 일치되는 행이 없는 preserved 테이블의 모든 행들을 추가한 결과를 V3라고 하자. SQL 서버는 unpreserved table로부터 가져온 컬럼들의 값은 NULL을 반환한다. 만약 FROM 절에 세 개 이상의 테이블을 지정하면 1부터 3까지의 단계를 반복적으로 수행하게 되며 V3를 세 번째 테이블에 조인하고 그 결과를 네 번째 테이블에 조인하는 식으로 진행된다.

4. WHERE 조건절에 정의된 필터링을 적용한다. 필터링 결과 TRUE를 반환하는 행들에 대해서만 다음 단계를 진행한다. 그래서 얻어진 결과를 V4라고 하자.

5. GROUP BY 목록에 따라 V4에 있는 모든 행들을 그룹핑한다. GROUP BY 목록에 있는 값들의 각각의 고유한 조합별로 하나의 그룹을 형성하게 되는데 그 결과를 V5라고 하자.

6. V5의 행들에 대하여 HAVING에서 정의된 필터링을 적용한다. 필터링 결과 TRUE를 반환하는 행들만 다음 단계로 진행된다. 이렇게 얻어진 결과를 V6라고 하자.

7. SELECT 목록을 처리한다. 만약 DISTINCT가 있으면 중복 값들을 제거한다. 이렇게 얻어진 결과를 V7이라고 하자.

8. ORDER BY 목록에 따라 V7의 행들을 정렬한다. 얻어진 결과는 V8이라고 하자.

9. TOP 절이 있으면 TOP 절을 처리한다 (참고로 TOP은 ANSI 표준이 아니다). 그 결과를 V9이라고 하자.

만약 어떤 쿼리에 모든 종류의 조건절이 포함되어 있지 않다면 SQL 서버는 그 조건절과 관련되는 단계들을 건너뛰게 된다. 이제는 두 가지 예제를 살펴 보고 앞에서 설명한 단계들을 적용해 가면서 쿼리를 수행해 보겠다.

주문을 한 건도 접수하지 않은 고객

고객으로 등록은 했지만 아직 주문을 한 건도 접수하지 않은 고객 정보를 반환하는 조인 쿼리를 작성해야 한다고 가정하자. 샘플 데이터에 의하면 [그림 2]에서 보는 바와 같이 예상되는 결과는 고객 C와 고객 D이다. [리스트 2]의 조인 쿼리를 수행하면 원하는 결과를 얻을 수 있다. ON 절에 C.custid = O.custid와 O.custid IS NULL 조건절을 모두 기술하지 않고 ON 절에는 C.custid = O.custid조건절을 기술하고 WHERE 절에는 O.custid IS NULL 조건절을 기술한 이유가 무엇일까요? 만약 ON 절에 이 두 가지 논리식을 모두 기술한다면 고객 C와 D가 아닌 모든 고객들에 대한 정보가 반환될 것이다. 왜 ON 절에 첫 번째 논리식을 기술하고 WHERE 절에 두 번째 논리식을 기술해야 하는지를 이해하기 위하여 먼저 쿼리에 대하여 논리적인 처리 단계를 적용해 보자.

[그림 2] 주문을 한 건도 접수하지 않은 고객 정보

Customers 테이블과 Orders 테이블의 카테시안 곱을 생성하면 20개의 행(4명의 고객 X 5개의 주문)을 가지는 결과 테이블(V1)이 만들어진다. V1에 있는 행들에 대하여 조인 조건 (C.custid = O.custid)을 적용하면, 조인 조건으로 얻을 수 있는 결과는 TRUE, FALSE, 그리고 UNKNOWN이다.
[그림 3]에서는 V1에 있는 행들을 보여 주고 각 행의 조인 조건의 결과를 match? 컬럼에 보여준다. Match? 컬럼이 TRUE인 행들만이 다음 단계에서 사용될 V2 테이블에 존재하게 된다. O.custid가 NULL인 행은 match? 컬럼이 UNKNOWN 인 것에 주의하기 바란다. 그리고, V2는 FALSE 나 UNKNOWN 을 포함하지 않는다. [그림 4]에 V2의 행들이 나와 있다.

[그림 3] 주문을 한 건도 접수하지 않은 고객: 단계 1 + custid 검색 결과


[그림 4] 주문을 한 건도 접수하지 않은 고객: 단계 2

Outer Join의 경우에 3단계에서는 Orders테이블에 일치되는 데이터가 존재하지 않는 Customers테이블의 모든 행을 V2의 행들에게 추가하게 된다. 이 경우에 고객 C와 D는 Orders 테이블에 매칭되는 데이터가 없으므로 SQL서버가 Orders테이블의 컬럼값들이 NULL인 행들을 V2의 행들에 추가한다. 이렇게 얻어진 결과를 V3이라고 하자. 다음은 SQL서버는 V3의 행에 대해 WHERE절 필터링(custid IS NULL)을 수행한다. 가능한 논리적 표현의 결과는 TRUE, FALSE, UNKNOWN이다. 그러나 이 경우에는 IS NULL이라는 조건이 있기 때문에 TRUE 또는 FALSE만이 얻어진다. [그림 5]에서는 V3의 행들을 보여 주며 match? 컬럼에는 각 행에 대하여 필터링한 결과를 보여준다. match?가 TRUE인 행들(마지막 두 개 행들)만이 다음 단계로 진행한다.

[그림 5] 주문을 한 건도 접수하지 않은 고객: 단계 3 + O.custid IS NULL인 결과

쿼리 처리의 나머지 부분은 단순히 V4에서 C.custid와 C.city값들을 추출하는 것이다. 이런 과정들을 거치게 되면 개발자는 원하는 결과인, 주문을 하지 않은 고객C와 D라는 값을 얻게 된다:
이제 ON절과 WHERE절의 차이점 그리고 ON C.custid = O.custid 와 WHERE O.custid IS NULL.을 기술한 이유에 대하여 토론해 보자. SQL서버는 outer행들을 더하기 전에 ON절을 수행하고 outer행들을 더한 후에 WHERE절을 수행한다. Outer Join은 주문을 하지 않은 고객을 표시하기 위하여 NULL값을 생성한다. 그러므로 쿼리문을 작성할 때 Outer Join을 적용하고 outer 행들을 추가한 다음에 WHERE 절에서 NULL을 점검해야 하는 것이다.
필자는 [리스트2]의 쿼리에서 ANSI SQL-92 조인 문법을 사용하였다. SQL서버의 경험이 풍부한 사람은 SQL서버가 ANSI SQL-92조인 형식을 채택하기 전에는 T-SQL에서 Left Outer Join을 *= 으로 표현하는 구식 문법을 지원했다는 것을 기억할지도 모른다. 참고로 이 *= 연산자는 하위 버전과의 호환을 위하여 지금도 지원하고 있다. Old-style문법은 논리 식을 두 개의 구분된 절(ON과 WHERE)로 작성하지 않는다. Join 조건과 필터링 조건 둘 다 WHERE절에 기술한다. [리스트2]의 쿼리를 구식 문법 형태로 작성한다면 다음과 같다:

SELECT C.*
FROM Customers AS C, Orders AS O
WHERE C.custid *= O.custid
AND O.custid IS NULL

리스트 2: 주문을 한 건도 접수하지 않은 고객정보를 반환하는 조인 쿼리
------------------------------------------------------------------
SELECT C.*
FROM Customers AS C
LEFT OUTER JOIN Orders AS O
ON C.custid = O.custid
WHERE O.custid IS NULL

위의 쿼리문을 실행하면 주문을 하지 않은 고객 정보 대신 모든 고객들에 대한 정보를 얻게 된다. 이 결과를 보면 왜 SQL서 버가 전체 논리 식을 조인 조건으로 취급해서 단계 2에서 빈 결과 집합을 생성했는지를 이해할 수 있을 것이다. 그 다음에 일치되는 데이터가 없는 고객들(모든 고객들)을 다시 추가하였다.

3회 미만의 주문을 한 접수한 시애틀 거주 고객 정보 조회

필터링과 논리적인 쿼리 프로세싱 단계의 차이점을 제대로 이해했는지 테스트하기 위하여 여러분이 직접 3회 미만의 주문을 한 시애틀 고객 정보를 반환하는 쿼리를 작성해 보기 바란다. [그림 6]에 원하는 결과가 나와 있다. 쿼리 작성이 완료되면 여러분이 작성한 쿼리문을 [리스트 3]의 솔루션과 비교해 보기 바란다.

[그림 6] 3개 미만의 주문을 접수한 시애틀 고객 정보

리스트 3: 3개 미만의 주문을 접수한 시애틀 거주 고객 정보 조회 쿼리
---------------------------------------------------------------------
SELECT C.custid, COUNT(O.orderid) AS numorders
FROM Customers AS C
LEFT OUTER JOIN Orders AS O
ON C.custid = O.custid
WHERE C.city = 'Seattle'
GROUP BY C.custid
HAVING COUNT(O.orderid) < 3

논리적 처리 단계는 이러하다. 1단계와 2단계는 조인 조건의 C.custid = O.custid 와 일치하는 것을 기반으로 하여 Customers 테이블과 Orders테이블을 조인한다. 3단계에서는 결과에 주문을 하지 않은 고객을 추가한다. 4단계에서는 SQL서버가WHERE절의 필터링 조건인 C.city = 'Seattle' 을 적용하여 고객의 city값이 시애틀인 행들만이 남게 된다. 5단계는 4단계의 행들을 C.custid로 그룹핑하여 각각의 그룹이 고유한 고객이 된다 (고객별로 하나의 그룹이 만들어진다). 6단계에서는 5단계에서 얻어진 그룹들에 대하여 COUNT (O.orderid) < 3 인 HAVING필터링 조건을 적용하여 3회 미만의 주문을 한 그룹만이 남게 된다. 마지막으로 7단계에서는 SELECT 목록(C.custid, C.city)을 처리하여 고객 ID와 city 정보를 반환한다.
이 솔루션에는 중요한 부분이 두 곳이 있다. 하나는 WHERE절에 C.city = 'Seattle' 필터링 조건을 배치하는 것이다. 만약 개발자가 실수로 ON절에 이 필터링 조건을 배치한다면 그 쿼리는 city값이 덴버인 고객 ID들까지 반환된다는 점에 유의하기 바란다. 이런 문제가 발생하는 이유는 비록 2단계에서는 그런 고객D에 대한 행을 제거했지만 3단계에서 주문을 하지 않은 outer행들을 더할 때 그런 데이터들이 다시 추가되기 때문이다.
또 하나 중요한 부분은 COUNT(*) 가 아니라COUNT(O.orderid)이라고 기술해야 한다는 점이다. 만일 COUNT(*)라고 기술한다면, 주문을 하지 않은 고객에 대해 0이 아닌 1 (고객C)이 얻어진다. 이러한 예상치 못한 결과가 얻어지는 것은 3단계의 결과에 그 원인이 있다. SQL서버는 Orders테이블의 컬럼들의 값이 NULL인 고객C에 대하여 outer행을 추가한다. COUNT(*) 는 조인 조건이 일치하건 불일치하건 관계없이 그리고 컬럼이 값을 가지고 있건 아니면 NULL인지에 관계없이 행들을 계산한다. COUNT(O.orderid)는 정확히 조인 조건에서 일치하는 결과가 있는 행, 즉 orderid 가 NULL이 아닌 행들만을 계산한다.

제어 능력을 기르자

필자의 바램은 이 기사를 통해 쿼리 조건문을 어디에 기술해야 하는지에 대한 혼란을 줄이는 것이다. 항상 다음 사항을 반드시 기억해 두기 바란다. 기본을 알면 어려운 문제도 단순해진다. 일단 논리적인 쿼리 프로세싱의 원칙을 완전히 이해하게 되면 여러분은 여러분이 작성한 조건절을 SQL 서버가 논리적으로 적용하는 것을 완전히 제어할 수 있게 된다.

제공 : DB포탈사이트 DBguide.net