실행 가능한 SQLite 문서: LEFT JOIN

Q: SQLite의 LEFT JOIN은 어떻게 동작하나요?

LEFT JOIN은 왼쪽 테이블의 모든 행을 그대로 가져오고, 오른쪽 테이블에 매칭되는 행이 있으면 그걸 붙여줍니다. 매칭되는 행이 없어도 왼쪽 행은 결과에 남고, 오른쪽 컬럼은 NULL로 채워집니다. 참고로 LEFT OUTER JOIN도 같은 의미인데, SQLite에서는 OUTER 키워드가 선택사항입니다.

Q: SQLite에서 LEFT JOIN과 INNER JOIN은 뭐가 다른가요?

INNER JOIN은 양쪽 테이블에서 조인 조건이 맞는 행만 결과에 포함합니다. 반면 LEFT JOIN은 왼쪽 테이블의 모든 행을 무조건 살리고, 매칭되지 않는 오른쪽 컬럼은 NULL로 채웁니다. '매칭되지 않은 것' 자체가 의미 있는 정보일 때 — 예를 들면 '주문이 한 번도 없는 사용자' 같은 걸 찾을 때 LEFT JOIN을 씁니다.

Q: LEFT JOIN을 썼는데 왜 INNER JOIN처럼 동작하나요?

거의 100% WHERE 절 때문입니다. 오른쪽 테이블 컬럼에 조건을 거는데 NULL을 고려하지 않으면, 매칭되지 않은 행이 전부 걸러져 버립니다. 오른쪽 테이블에 대한 조건은 WHERE가 아니라 ON 절에 넣어야 하고, 매칭되지 않은 행을 찾고 싶다면 WHERE right.col IS NULL처럼 써야 합니다. WHERE right.col = 'x' 같은 조건은 NULL 행을 조용히 제거해 버린다는 점에 주의하세요.

LEFT JOIN은 왼쪽 테이블을 전부 남긴다

INNER JOIN은 양쪽이 모두 매칭되는 행만 돌려준다. 대부분은 그게 우리가 원하는 결과지만, 항상 그런 건 아니다. "매칭되는 게 없다"는 사실 자체가 답이 되는 경우도 있기 때문이다. 주문을 한 번도 안 한 회원, 한 번도 팔리지 않은 상품, 댓글이 하나도 없는 게시글 같은 것들 말이다. 이럴 때 필요한 게 바로 LEFT JOIN이다.

LEFT JOIN(또는 LEFT OUTER JOIN)은 왼쪽 테이블의 모든 행을 그대로 가져온다. 오른쪽 테이블에 매칭되는 행이 있으면 그 컬럼 값들이 함께 붙고, 없으면 왼쪽 행은 그대로 살아남되 오른쪽 컬럼은 전부 NULL로 채워진다.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total REAL
);

INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders (user_id, total) VALUES (1, 49.99), (1, 12.50), (2, 99.00);

SELECT u.name, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

Cleo는 주문 내역이 없지만 결과에는 그대로 등장합니다. 다만 total 컬럼이 NULL로 채워질 뿐이죠. 여기서 LEFT JOIN을 INNER JOIN으로 바꾸면 Cleo는 결과에서 아예 사라져 버립니다.

동작 원리 이해하기

쿼리를 위에서 아래로 읽으면서, 먼저 등장하는 테이블을 기준 테이블이라고 생각하면 편합니다. users의 모든 행은 무슨 일이 있어도 결과에 등장합니다. 그다음 LEFT JOIN은 각 사용자에 대해 이렇게 묻습니다. "orders에 매칭되는 행이 있나?"

매칭되는 행이 있다 → 그 행의 컬럼을 사용자 행에 붙여서 출력
매칭이 여러 건이다 → 매칭된 수만큼 출력 행이 생성됨 (Ada는 주문이 2건이라 두 번 등장)
매칭이 없다 → 오른쪽 테이블의 모든 컬럼을 NULL로 채워서 한 행 출력

마지막 경우가 바로 LEFT JOIN이 존재하는 이유입니다. 여기서의 NULL은 "값을 모른다"는 뜻이 아니라 "오른쪽에서 붙일 게 없다"는 의미입니다.

LEFT OUTER JOIN도 같은 동작을 합니다. SQLite에서 OUTER 키워드는 생략 가능하고, 실제로 대부분 빼고 씁니다.

매칭되지 않는 행 찾아내기

LEFT JOIN의 가장 대표적인 활용법은 바로 오른쪽 테이블에 매칭이 없는 왼쪽 행을 찾는 것입니다. 핵심 요령은, 오른쪽 테이블에서 실제 데이터에 NOT NULL로 들어 있는 컬럼(보통 기본 키)을 골라 조인 후에 NULL 여부를 검사하는 것입니다.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 2, 99.00);

SELECT u.name
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.id IS NULL;

결과적으로 Cleo만 돌아옵니다. 조인은 주문 데이터가 있는 경우 그 값을 붙여주고, WHERE o.id IS NULL 조건이 매칭에 실패한 행만 남기는 식이죠. 이런 패턴을 흔히 "안티 조인(anti-join)"이라고 부릅니다.

ON과 WHERE의 차이: 놓치기 쉬운 함정

LEFT JOIN을 쓸 때 가장 자주 마주치는 버그가 바로 이 부분이라, 잠깐 짚고 넘어갈 가치가 있습니다. 조건은 ON 절이나 WHERE 절 어디에든 넣을 수 있지만, 외부 조인에서는 두 위치의 동작이 완전히 달라집니다.

ON은 조인이 일어나는 도중 에 평가됩니다. 여기 들어간 조건은 오른쪽 테이블의 어떤 행을 매칭으로 인정할지를 결정하죠.
WHERE는 조인이 결과 행들을 모두 만들어낸 후 에 실행됩니다. 즉, 합쳐진 결과를 걸러내는 역할입니다.

오른쪽 테이블에 대한 조건을 WHERE에 넣으면 어떤 일이 벌어지는지 한번 보세요:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

-- 잘못됨: LEFT JOIN이 조용히 INNER JOIN으로 바뀜
SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'shipped';

Cleo는 주문이 없기 때문에 해당 행의 o.status는 NULL이고, NULL = 'shipped'는 참이 아니므로 결과에서 빠져 버립니다. Boris의 상태는 'pending'이라 이 역시 제외됩니다. 결국 LEFT JOIN이 슬그머니 INNER JOIN처럼 동작해 버린 셈이죠.

해결 방법은 간단합니다. 이 조건을 ON 절로 옮겨서 _출력 행_이 아니라 매칭 자체를 거르도록 만들면 됩니다:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id AND o.status = 'shipped';

이제 모든 사용자가 결과에 등장합니다. Ada는 배송 완료된 주문이 함께 나오고, Boris는 NULL이 나옵니다(그의 대기 중인 주문은 매칭 조건을 만족하지 못했으니까요). Cleo도 NULL이죠(주문 자체가 없음). "모든 사용자를 보여주되, 배송 완료된 주문이 있다면 같이 보여줘"라는 질문에는 이게 정확한 답입니다.

기억해 둘 규칙: 왼쪽 테이블에 대한 조건은 WHERE에 넣어도 됩니다. 반면 오른쪽 테이블에 대한 조건은 거의 항상 ON 절에 넣어야 합니다. 단, IS NULL로 매칭되지 않은 행을 일부러 찾고 싶을 때만 예외죠.

LEFT JOIN으로 개수 세기

자주 마주치는 작업이 있습니다. 부모 행마다 연관된 자식 행이 몇 개인지 세되, 자식이 하나도 없는 부모까지 포함시키는 거죠. INNER JOIN을 쓰면 0인 항목은 빠져버립니다. 이럴 때는 LEFT JOIN에 오른쪽 테이블 컬럼을 COUNT해 주면 원하는 결과가 나옵니다.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 1, 12.50), (3, 2, 99.00);

SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS revenue
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY u.name;

두 가지 짚고 넘어갈 점이 있습니다.

COUNT(o.id)는 우측 테이블에서 NULL이 아닌 행만 셉니다. 그래서 Cleo는 1이 아니라 0이 나옵니다. COUNT가 NULL을 무시하기 때문이죠. 만약 COUNT(*)로 작성했다면 Cleo는 1이 됩니다(행 자체는 존재하고, 그 안의 값들이 NULL일 뿐이니까요). 대부분의 경우 원하는 건 COUNT(right.id) 형태입니다.
COALESCE(SUM(o.total), 0)은 Cleo의 NULL 합계를 0으로 바꿔줍니다. 이걸 빼먹으면 매출이 NULL로 표시되는데, 의미상으로는 맞지만 보기에 영 깔끔하지 않죠.

SQLite 다중 테이블 조인하기

LEFT JOIN은 체인처럼 이어 붙일 수 있습니다. 각 조인은 지금까지의 결과에 또 다른 테이블을 붙이는 식으로 동작하죠. 한 번 LEFT JOIN으로 어떤 컬럼을 nullable하게 만들었다면, 거기에 매달리는 테이블도 계속 LEFT JOIN으로 이어가야 합니다. 그렇지 않으면 뒤따르는 INNER JOIN이 살려두고 싶었던 행들을 조용히 잘라내 버립니다.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);
CREATE TABLE shipments (order_id INTEGER PRIMARY KEY, carrier TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (10, 1, 49.99), (11, 2, 99.00);
INSERT INTO shipments VALUES (10, 'UPS');

SELECT u.name, o.total, s.carrier
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
LEFT JOIN shipments AS s ON s.order_id = o.id;

사용자 세 명이 다시 등장합니다. Ada는 주문과 배송 기록이 모두 있습니다. Boris는 주문은 있지만 배송 정보가 없어서 carrier 컬럼이 NULL입니다. Cleo는 아예 주문이 없으니 o.total과 s.carrier 둘 다 NULL로 나옵니다. 이렇게 LEFT JOIN을 연달아 걸어두면 관계 사슬의 어느 지점에서 데이터가 끊기든 모든 사용자가 결과에 그대로 남습니다.

SQLite LEFT JOIN, 언제 써야 할까

질문의 핵심이 결국 왼쪽 테이블이고 오른쪽 테이블은 부가 정보일 때 LEFT JOIN을 꺼내 들면 됩니다. "모든 사용자, 주문이 있다면 주문도 함께"라든가 "모든 상품과 그에 대한 최신 리뷰" 같은 표현은 그대로 LEFT JOIN으로 옮겨집니다.

반대로 양쪽 테이블이 둘 다 반드시 있어야 하는 상황이라면 INNER JOIN을 쓰세요. "주문과 그 주문을 낸 사용자 정보"라는 질문에서 사용자 없는 주문은 애초에 말이 안 되니, 이런 경우엔 INNER JOIN의 필터링 동작이 정확히 원하는 결과를 줍니다.

만약 LEFT JOIN ... WHERE right.col IS NOT NULL 같은 쿼리를 짜고 있다면, 사실은 INNER JOIN을 원했던 겁니다. 반대로 LEFT JOIN ... WHERE right.col IS NULL을 쓰고 있다면 안티 조인(anti-join)을 의도한 것이고, 이 경우엔 제대로 쓴 게 맞습니다.

다음 주제: 셀프 조인

가끔은 조인할 대상 테이블이 _지금 조회 중인 바로 그 테이블_인 경우가 있습니다. 직원과 그 직원의 매니저, 카테고리와 상위 카테고리, 같은 도시에 사는 사용자 쌍 같은 것들이죠. 이게 바로 셀프 조인이고, 다음 페이지에서 다룹니다.

자주 묻는 질문

SQLite의 LEFT JOIN은 어떻게 동작하나요?

LEFT JOIN은 왼쪽 테이블의 모든 행을 그대로 가져오고, 오른쪽 테이블에 매칭되는 행이 있으면 그걸 붙여줍니다. 매칭되는 행이 없어도 왼쪽 행은 결과에 남고, 오른쪽 컬럼은 NULL로 채워집니다. 참고로 LEFT OUTER JOIN도 같은 의미인데, SQLite에서는 OUTER 키워드가 선택사항입니다.

SQLite에서 LEFT JOIN과 INNER JOIN은 뭐가 다른가요?

INNER JOIN은 양쪽 테이블에서 조인 조건이 맞는 행만 결과에 포함합니다. 반면 LEFT JOIN은 왼쪽 테이블의 모든 행을 무조건 살리고, 매칭되지 않는 오른쪽 컬럼은 NULL로 채웁니다. '매칭되지 않은 것' 자체가 의미 있는 정보일 때 — 예를 들면 '주문이 한 번도 없는 사용자' 같은 걸 찾을 때 LEFT JOIN을 씁니다.

LEFT JOIN을 썼는데 왜 INNER JOIN처럼 동작하나요?

거의 100% WHERE 절 때문입니다. 오른쪽 테이블 컬럼에 조건을 거는데 NULL을 고려하지 않으면, 매칭되지 않은 행이 전부 걸러져 버립니다. 오른쪽 테이블에 대한 조건은 WHERE가 아니라 ON 절에 넣어야 하고, 매칭되지 않은 행을 찾고 싶다면 WHERE right.col IS NULL처럼 써야 합니다. WHERE right.col = 'x' 같은 조건은 NULL 행을 조용히 제거해 버린다는 점에 주의하세요.