SELECT 문의 논리적 실행 순서와 조건절

2과목이 시작됩니다. 1과목이 모델을 읽는 법이었다면 여기서부터는 그 모델 위에서 도는 SQL 자체입니다. 그리고 2과목의 문항 대부분은 결국 한 가지를 묻습니다 — 이 질의가 어떤 차례로 해석되는가. 별칭이 어느 절에서 보이는지, 집계 조건을 어디에 적어야 하는지, NULL이 정렬의 어느 끝에 서는지가 전부 그 차례에서 따라 나옵니다. 차례를 외우는 것이 아니라 차례로부터 답을 끌어내는 연습이 이 노트의 목표입니다.

집합 기반 처리

행이 아니라 집합

관계형 데이터베이스는 데이터를 행과 열로 이루어진 테이블에 담고, 테이블을 집합으로 다룹니다. 여기서 집합이란 조건을 만족하는 행들의 묶음이고, SQL의 연산은 그 묶음 하나를 받아 다른 묶음 하나를 내놓습니다.

이 성질이 중요한 이유는 SQL이 한 번에 한 행씩 처리한다고 가정하면 틀리는 자리가 많기 때문입니다. UPDATE 사원 SET 급여 = 급여 * 1.1은 100명을 한 명씩 차례로 올리는 명령이 아니라 사원 집합 전체에 한 번 적용되는 명령입니다. 그래서 중간에 어떤 행이 먼저 갱신되는지는 결과에 영향을 주지 않습니다.

선언형 질의

SQL은 선언형 언어입니다 — 무엇을 원하는지 적을 뿐 어떻게 가져올지는 적지 않는다는 뜻입니다. 어떤 인덱스를 탈지, 어느 테이블을 먼저 읽을지는 옵티마이저가 정합니다. 옵티마이저는 통계 정보를 보고 실행 방법을 고르는 데이터베이스 내부 모듈입니다.

그래서 SQL에는 서로 다른 두 가지 순서가 있습니다. 문법이 정한 해석 순서인 논리적 실행 순서와, 옵티마이저가 실제로 고른 물리적 실행 순서입니다. 앞엣것은 늘 같고 뒤엣것은 매번 다를 수 있습니다. 시험에서 「결과가 무엇인가」를 묻는 문항은 앞엣것으로 풀고, 3과목의 튜닝 문항은 뒤엣것을 다룹니다.

논리적 실행 순서

여섯 절의 차례

작성하는 순서와 해석되는 순서가 다릅니다. 적을 때는 SELECT가 맨 앞이지만 해석은 다음 차례로 진행됩니다.

SELECT   부서번호, AVG(급여) AS 평균급여   -- 5
  FROM   사원                             -- 1
 WHERE   입사일 >= DATE '2020-01-01'       -- 2
 GROUP BY 부서번호                         -- 3
HAVING   AVG(급여) >= 5000                 -- 4
 ORDER BY 평균급여 DESC;                    -- 6

FROM으로 집합을 만들고, WHERE로 행을 걸러 내고, GROUP BY로 묶고, HAVING으로 묶음을 걸러 내고, SELECT로 열을 고르고, 마지막에 ORDER BY로 줄을 세웁니다. 이 여섯 줄만 손에 익으면 아래 물음 대부분이 저절로 풀립니다.

별칭이 보이는 절

별칭은 칼럼이나 테이블에 붙이는 다른 이름이고 AS로 답니다. 별칭은 SELECT 절에서 만들어지므로 SELECT보다 먼저 해석되는 절에서는 아직 존재하지 않습니다.

위 예제의 ORDER BY 평균급여 DESC가 성립하는 것이 그 때문입니다. 같은 자리에 WHERE 평균급여 >= 5000을 적으면 식별자를 찾을 수 없다는 오류가 납니다. 테이블 별칭은 사정이 다릅니다 — FROM이 1번이라 그 뒤의 모든 절에서 쓸 수 있고, 오히려 테이블 별칭을 붙였으면 원래 테이블 이름 쪽을 쓸 수 없게 됩니다.

물리적 실행과의 거리

논리적 실행 순서는 결과가 무엇이어야 하는지를 정할 뿐, 데이터베이스가 그 순서로 움직인다는 뜻이 아닙니다. WHERE에 인덱스를 탈 수 있는 조건이 있으면 테이블 전체를 읽고 거르는 대신 인덱스로 필요한 행만 집어 옵니다. 결과가 같다면 옵티마이저는 어떤 길로 가든 자유입니다.

WHERE 절의 연산자

비교와 범위

=·>·>=·<·<=·<> 여섯이 비교 연산자이고, 여기에 범위와 목록을 다루는 것들이 붙습니다.

• BETWEEN a AND b — 양 끝을 포함합니다. 급여 BETWEEN 3000 AND 5000은 3000과 5000을 모두 세므로 급여 >= 3000 AND 급여 <= 5000과 같습니다.
• IN (a, b, c) — = a OR = b OR = c와 같습니다.
• LIKE '김%' — %는 길이 제한 없는 문자열, _는 한 글자입니다.

LIKE '%김%'처럼 앞에 %가 붙으면 인덱스의 선두를 고정할 수 없어 범위를 좁히지 못합니다. 1과목에서 다중값 속성이 성능 문제로 번지던 경로가 정확히 이 자리입니다.

논리 연산자의 우선순위

NOT → AND → OR 차례로 강합니다. AND가 OR보다 먼저 묶입니다.

-- 부서 = '영업' OR (부서 = '기획' AND 급여 >= 5000)
WHERE 부서 = '영업' OR 부서 = '기획' AND 급여 >= 5000

영업 부서는 급여와 무관하게 전부 걸립니다. 의도가 「영업이거나 기획이면서 급여 5000 이상」이었다면 괄호를 쳐야 합니다. 시험에서 결과 건수를 묻는 문항의 절반가량이 이 괄호 하나입니다.

NULL과의 비교

급여 = NULL은 참이 되지 않습니다. NULL은 「값이 없음」이라 어떤 값과 비교해도 결과가 참도 거짓도 아닌 알 수 없음이 되고, WHERE 절은 참인 행만 남기기 때문입니다. NULL을 찾으려면 IS NULL·IS NOT NULL을 씁니다.

NOT IN에 NULL이 섞이면 결과가 통째로 비는 것도 같은 이유입니다. 부서번호 NOT IN (10, 20, NULL)은 부서번호 <> 10 AND 부서번호 <> 20 AND 부서번호 <> NULL로 펼쳐지고, 마지막 항이 늘 알 수 없음이라 AND 전체가 참이 될 수 없습니다.

GROUP BY와 HAVING

역할 분담

GROUP BY는 지정한 칼럼의 값이 같은 행들을 한 묶음으로 만듭니다. 묶고 나면 한 묶음이 한 행이 되므로, SELECT 절에는 묶음의 기준 칼럼이거나 집계 함수만 올 수 있습니다. 묶음에 속한 행마다 값이 다른 칼럼을 그냥 적으면 어느 행의 값을 내놓아야 할지 정해지지 않습니다.

HAVING은 그렇게 만들어진 묶음을 거르는 조건입니다. 행을 거르는 WHERE와 묶음을 거르는 HAVING이 역할을 나눠 갖습니다.

WHERE AVG(급여) >= 5000이 오류인 이유가 차례에 있습니다. WHERE는 2번이라 3번의 묶음이 아직 없고, 평균을 낼 대상이 정해지지 않았습니다.

내릴 수 있는 조건

반대로 HAVING에 적어도 되지만 WHERE로 내리는 편이 나은 조건이 있습니다. 집계 함수를 쓰지 않는 조건이 그렇습니다.

-- 묶은 다음에 버린다
 GROUP BY 부서번호 HAVING 부서번호 <> 90

-- 묶기 전에 버린다 — 묶을 행 자체가 줄어든다
 WHERE 부서번호 <> 90 GROUP BY 부서번호

결과는 같지만 두 번째가 정렬·해시 작업에 넘기는 행이 적습니다. 3과목에서 「조건을 가능한 한 아래로 내린다」로 다시 만나게 되는 원칙의 첫 모습입니다.

ORDER BY와 NULL의 자리

기본 정렬 위치

ORDER BY는 기본이 오름차순이고 DESC를 붙이면 내림차순입니다. 칼럼 이름 대신 ORDER BY 2처럼 SELECT 목록의 순번을 적을 수도 있습니다.

NULL이 어느 끝에 서는지는 데이터베이스마다 다릅니다. 오라클은 NULL을 가장 큰 값으로 취급합니다 — 오름차순이면 맨 뒤, 내림차순이면 맨 앞입니다. SQL Server는 반대로 NULL을 가장 작은 값으로 보므로 같은 질의가 다른 순서를 내놓습니다.

NULLS FIRST와 NULLS LAST

기본값에 기대지 않고 직접 지정할 수 있습니다.

SELECT 사원명, 커미션
  FROM 사원
 ORDER BY 커미션 DESC NULLS LAST;

커미션이 큰 순으로 세우되 커미션이 없는 사원은 맨 뒤로 보냅니다. 화면에 순위를 보여 주는 질의에서 NULL이 1등 자리에 서는 사고가 이 한 줄로 막힙니다. 시험에서는 정렬 결과를 직접 적어 보라는 문항이 나오므로, 오라클 기준값과 명시적 지정을 함께 기억해 두는 편이 안전합니다.

DISTINCT

중복 제거의 대가

DISTINCT는 결과 집합에서 중복 행을 없앱니다. 공짜가 아닙니다 — 무엇이 중복인지 알려면 전체를 정렬하거나 해시 테이블에 담아야 하고, 그 작업은 대상 건수에 비례해 메모리와 시간을 먹습니다. 메모리가 모자라면 임시 영역에 내려 썼다가 다시 읽으므로 디스크 작업까지 붙습니다.

그래서 「혹시 중복이 있을까 봐」 습관적으로 붙인 DISTINCT가 느린 질의의 흔한 원인입니다. 중복이 생긴 진짜 이유는 대개 조인 쪽에 있습니다 — 1:M 조인이 결과 행을 늘려 놓은 것을 뒤에서 지우고 있는 것입니다.

존재만 확인할 때

값이 필요 없고 짝이 있는지만 알면 되는 자리라면 조인과 DISTINCT 대신 EXISTS를 씁니다.

-- 주문이 있는 회원 — 조인 후 중복 제거
SELECT DISTINCT m.회원번호 FROM 회원 m JOIN 주문 o ON o.회원번호 = m.회원번호;

-- 같은 결과 — 첫 건을 찾으면 멈춘다
SELECT m.회원번호 FROM 회원 m
 WHERE EXISTS (SELECT 1 FROM 주문 o WHERE o.회원번호 = m.회원번호);

아래쪽은 회원 한 명당 주문을 하나만 찾으면 그 자리에서 판정이 끝나므로 주문 테이블을 끝까지 읽지 않습니다. 이 대비는 다음다음 노트의 서브쿼리에서 이어집니다.

연습 문제

1. 사원 테이블에 다음 다섯 행이 있다.
   A(영업, 3000) · B(영업, 6000) · C(기획, 4000) · D(기획, 7000) · E(개발, 8000)
   WHERE 부서 = '영업' OR 부서 = '기획' AND 급여 >= 5000의 결과 건수와, 같은 조건에 (부서 = '영업' OR 부서 = '기획') AND 급여 >= 5000으로 괄호를 쳤을 때의 결과 건수는?
   ① 2건, 2건
   ② 3건, 2건
   ③ 4건, 3건
   ④ 3건, 3건

   ②. 괄호가 없으면 AND가 먼저 묶여 「영업이거나, 기획이면서 5000 이상」이 됩니다. 영업 A·B가 급여와 무관하게 걸리고 기획에서는 D만 걸려 3건입니다. 괄호를 치면 영업·기획 넷 중 5000 이상인 B·D만 남아 2건입니다. E는 어느 쪽에서도 부서 조건에 걸리지 않습니다.
2. 다음 중 오류가 나는 것은?
   ① SELECT 급여 * 12 AS 연봉 FROM 사원 ORDER BY 연봉 DESC
   ② SELECT 급여 * 12 AS 연봉 FROM 사원 WHERE 연봉 >= 60000
   ③ SELECT 부서번호, COUNT(*) FROM 사원 GROUP BY 부서번호 HAVING COUNT(*) >= 3
   ④ SELECT 부서번호, COUNT(*) FROM 사원 WHERE 입사일 >= DATE '2020-01-01' GROUP BY 부서번호

   ②. 칼럼 별칭은 SELECT 절에서 만들어지는데 WHERE가 그보다 먼저 해석되므로 그 이름을 알지 못합니다. ①의 ORDER BY는 마지막에 해석되어 별칭이 이미 있고, ③은 집계 조건을 HAVING에 두었으며, ④는 집계 함수가 없는 조건을 WHERE에 두었으니 모두 정상입니다.
3. 커미션 칼럼에 값이 400, NULL, 1200, NULL, 300인 다섯 행이 있다. 오라클에서 ORDER BY 커미션 DESC로 정렬했을 때 나오는 순서는?
   ① 1200, 400, 300, NULL, NULL
   ② NULL, NULL, 1200, 400, 300
   ③ NULL, NULL, 300, 400, 1200
   ④ 300, 400, 1200, NULL, NULL

   ②. 오라클은 NULL을 가장 큰 값으로 취급하므로 내림차순에서는 NULL이 앞에 섭니다. 나머지는 값이 큰 순으로 1200, 400, 300입니다. ①을 얻으려면 DESC NULLS LAST를 붙여야 합니다.
4. 부서번호가 10, 20, 30인 사원이 각각 4명, 6명, 5명이고 부서번호가 NULL인 사원이 2명이다. SELECT 부서번호 FROM 사원 WHERE 부서번호 NOT IN (10, 20)의 결과 건수는?
   ① 5건
   ② 7건
   ③ 15건
   ④ 0건

   ①. NOT IN은 부서번호 <> 10 AND 부서번호 <> 20으로 펼쳐집니다. 30번 5명은 두 조건을 모두 만족하지만, 부서번호가 NULL인 2명은 비교 결과가 알 수 없음이라 WHERE를 통과하지 못합니다. 목록 안에 NULL이 들어 있었다면 결과는 ④가 됩니다.
5. 다음 두 질의의 관계로 옳은 것은?
   ㉠ SELECT 부서번호, COUNT(*) FROM 사원 GROUP BY 부서번호 HAVING 부서번호 <> 90
   ㉡ SELECT 부서번호, COUNT(*) FROM 사원 WHERE 부서번호 <> 90 GROUP BY 부서번호
   ① 결과가 다르다
   ② 결과는 같고 ㉠이 묶을 행 수가 더 적다
   ③ 결과는 같고 ㉡이 묶을 행 수가 더 적다
   ④ ㉡은 문법 오류다

   ③. 집계 함수를 쓰지 않는 조건이라 어느 절에 두어도 결과는 같습니다. 다만 ㉠은 90번 부서까지 묶은 뒤에 버리고 ㉡은 묶기 전에 버리므로, GROUP BY가 처리할 행 수는 ㉡이 적습니다.
6. 한 개발자가 「중복이 나올까 봐」 조회 질의마다 SELECT DISTINCT를 붙이는 습관이 있다. 이 습관이 성능에 주는 영향과, 중복이 실제로 나왔을 때 먼저 확인해야 할 곳을 근거와 함께 서술하시오.

   DISTINCT는 중복 판정을 위해 결과 집합 전체를 정렬하거나 해시 테이블에 담아야 하므로, 대상 건수에 비례하는 CPU와 메모리를 씁니다. 작업 영역이 모자라면 임시 세그먼트에 내려 썼다가 다시 읽으므로 디스크 I/O까지 더해집니다. 중복이 애초에 없는 질의에서는 이 비용이 전부 낭비입니다. 중복이 실제로 나왔다면 먼저 확인할 곳은 조인입니다 — 1:M 관계를 조인하면 1쪽의 행이 M쪽 건수만큼 늘어나고, 그 늘어난 행을 뒤에서 DISTINCT로 지우는 모양이 되기 때문입니다. 값이 필요 없고 짝의 존재만 확인하면 되는 자리라면 조인을 EXISTS 서브쿼리로 바꾸는 편이 낫습니다. 첫 건을 찾는 순간 판정이 끝나 중복 자체가 생기지 않고 중복 제거 단계도 사라집니다. 채점은 정렬·해시 비용을 든 것에 2점, 조인의 1:M 확대를 원인으로 지목한 것에 2점, EXISTS 같은 대안을 제시한 것에 1점입니다.