SQLite LEFT JOIN으로 주문이 없는 고객까지 집계하기

고객별로 결제 완료 주문이 몇 건인지 보고 싶다고 가정해 보겠습니다. 주문이 아예 없는 고객뿐 아니라 취소된 주문만 있는 고객도 결과에 0건으로 표시해야 합니다. 이때 LEFT JOIN의 조건 위치와 COUNT가 세는 대상을 함께 살펴봐야 합니다.

이 글은 SQL 집계 함수와 GROUP BY, INNER JOIN을 익힌 뒤 이어서 읽는 독립 실습입니다. INNER JOIN이 양쪽에서 일치하는 행을 연결한다면, LEFT JOIN은 일치하는 오른쪽 행이 없는 왼쪽 행도 결과에 남깁니다.

실습 데이터 준비

다음 SQL을 SQLite에서 실행해 고객 세 명과 주문 네 건을 준비합니다. 새 실습용 데이터베이스에서 실행하세요.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, '가람'),
  (2, '나래'),
  (3, '다온');

INSERT INTO orders (id, customer_id, status) VALUES
  (101, 2, 'cancelled'),
  (102, 3, 'paid'),
  (103, 3, 'paid'),
  (104, 3, 'cancelled');

가람은 주문이 없고, 나래는 취소 주문만 있습니다. 다온은 결제 완료 주문 두 건과 취소 주문 한 건이 있습니다. orders.id는 INTEGER PRIMARY KEY인 주문 식별자입니다. 실제 주문 행의 ID는 NULL이 아니므로 주문 건수를 셀 때 사용할 수 있습니다.

ON에서 결제 완료 주문을 고르기

먼저 주문을 연결할 때 status = 'paid' 조건을 적용합니다.

SELECT
  c.id,
  c.name,
  COUNT(*) AS joined_rows,
  COUNT(o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;

이 실습 데이터에서 예상되는 결과는 다음과 같습니다.

id	name	joined_rows	paid_orders
1	가람	1	0
2	나래	1	0
3	다온	2	2

ON의 두 조건을 모두 만족하는 주문만 고객에 연결됩니다. 가람은 주문 자체가 없고, 나래의 주문은 상태가 paid가 아니므로 두 고객에게 연결되는 주문은 없습니다. 그래도 LEFT JOIN은 각각 고객 행을 남기며, 그 행의 o.id를 비롯한 주문 열을 NULL로 채웁니다.

COUNT(*)는 그룹에 있는 행을 세므로 가람과 나래에게 각각 1을 반환합니다. 반면 COUNT(o.id)는 o.id가 NULL이 아닌 행만 세므로 둘 다 0입니다. 다온은 조건에 맞는 주문 행이 두 개라 두 값이 모두 2입니다.

즉, 이 예제의 COUNT(*)는 결제 완료 주문 수가 아니라 조인 후 각 고객 그룹에 남은 행 수입니다. COUNT(o.id)는 실제로 연결된 결제 완료 주문 수입니다. GROUP BY c.id, c.name은 고객별로 행을 묶고, ORDER BY c.id는 결과를 고객 ID 순으로 정렬합니다.

같은 조건을 WHERE로 옮기기

이번에는 고객 ID만으로 주문을 연결한 다음, 결제 완료 조건을 WHERE에 둡니다.

SELECT
  c.id,
  c.name,
  COUNT(*) AS joined_rows,
  COUNT(o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;

예상 결과는 다음과 같습니다.

id	name	joined_rows	paid_orders
3	다온	2	2

가람은 연결할 주문이 없어 주문 열이 NULL인 행으로 남지만, 그 행에서 o.status = 'paid'는 참이 아니므로 WHERE를 통과하지 못합니다. 나래는 취소 주문이 고객 ID와 일치해 연결되지만, 그 주문이 WHERE 조건을 통과하지 못합니다. 다온의 취소 주문도 제외되고 결제 완료 주문 두 건만 남습니다.

특히 나래의 경우를 주의하세요. ON에서 상태까지 검사하면 일치하는 주문이 없어 NULL로 채운 행이 생깁니다. WHERE에서 상태를 검사하면 취소 주문이 이미 연결된 뒤 제거되며, 그 고객을 위해 NULL 행이 새로 만들어지지는 않습니다.

이 예제에서 WHERE o.status = 'paid'는 오른쪽 열이 NULL인 행을 제외하므로 결과가 같은 조건의 INNER JOIN과 같아집니다. 모든 WHERE 조건이 항상 그런 것은 아닙니다. 예를 들어 WHERE o.id IS NULL은 일치하는 주문이 없는 고객을 찾는 데 사용할 수 있습니다.

왜 OR o.id IS NULL만 추가해서는 부족할까?

두 번째 쿼리의 조건을 WHERE o.status = 'paid' OR o.id IS NULL로 바꾸면 주문이 없는 가람은 남습니다. 그러나 취소 주문이 있는 나래는 여전히 빠집니다. 나래에게는 고객 ID가 일치하는 실제 주문 행이 있어서 o.id가 NULL이 아니고, 그 주문의 상태도 paid가 아니기 때문입니다. 결제 완료 주문이 0건인 고객을 모두 유지하려면 이 실습처럼 상태 조건을 ON에 두고 COUNT(o.id)로 세는 방법을 사용할 수 있습니다.

실행 범위와 참고 자료

위 SQL은 SQLite를 대상으로 작성했습니다. 이 원고의 쿼리는 실행하지 못했으며, 표의 값은 제시된 데이터와 SQLite의 JOIN·집계 규칙을 바탕으로 계산한 예상 결과입니다. 실제 실행으로 확인한 SQLite 버전이나 출력은 없습니다. 직접 실행할 때는 데이터 준비 SQL을 먼저 실행한 다음 두 SELECT를 각각 비교해 보세요.

• SQLite SELECT 문서: 외부 조인에서 NULL 행을 추가하는 시점과 WHERE 처리
• SQLite 집계 함수 문서: COUNT(*)와 COUNT(X)의 정의
• SQLite CREATE TABLE 문서: INTEGER PRIMARY KEY의 동작