조인 (Joins)
조인 (Joins)
조인은 관계형 데이터베이스에서 가장 기본이 되는 연산 중 하나예요. 두 개의 테이블(또는 릴레이션)을 가로로 연결해서 하나의 결과로 만들어 주죠. 이때 각 쪽 테이블을 조인 절에서 쓰는 위치에 따라 **왼쪽(left)**과 **오른쪽(right)**이라고 부르고, 결과의 각 행에는 두 릴레이션의 컬럼이 함께 담겨요.
조인은 두 릴레이션의 행들을 짝지을 규칙을 사용해요. 흔히 조건식(predicate)이 그 규칙이 되지만, 조건 없이 그냥 가능한 조합을 모두 만드는 경우도 있어요. 이 규칙이 어떻게 쓰이느냐에 따라 조인 종류가 나뉘니까 하나씩 살펴볼게요.
아우터 조인 (Outer Joins)
짝이 없는 행도 결과에 남기고 싶을 때가 있어요. 그럴 때 OUTER 조인을 쓰면 되는데, 아우터 조인은 다시 세 가지로 나뉘어요.
LEFT: 왼쪽 릴레이션의 모든 행이 적어도 한 번은 등장해요.RIGHT: 오른쪽 릴레이션의 모든 행이 적어도 한 번은 등장해요.FULL: 양쪽 릴레이션의 모든 행이 적어도 한 번은 등장해요.
OUTER가 붙지 않은 조인은 INNER 조인이에요. 안쪽 조인은 짝이 지어진 행만 결과로 돌려주죠. 아우터 조인에서 짝이 없는 행이 반환될 때, 반대쪽 테이블의 컬럼 값은 NULL로 채워져요.
크로스 조인 (Cross Product Joins, Cartesian Product)
가장 단순한 조인은 CROSS JOIN이에요. 이 조인에는 아무 조건도 없고, 그냥 가능한 모든 행의 쌍을 돌려줘요.
가능한 모든 행의 쌍을 반환해볼게요.
SELECT a.*, b.* FROM a CROSS JOIN b;
이건 JOIN 절을 생략한 것과 동일해요.
SELECT a.*, b.* FROM a, b;
조건부 조인 (Conditional Joins)
대부분의 조인은 한쪽의 컬럼과 다른 쪽의 컬럼을 연결하는 조건식으로 정의돼요. 조건은 조인과 함께 ON 절로 명시적으로 쓸 수 있고(더 명확해요), 옛날 방식으로 WHERE 절에 암시적으로 넣을 수도 있어요.
먼저 TPC-H 스키마의 l_regions와 l_nations 테이블을 준비할게요.
CREATE TABLE l_regions (
r_regionkey INTEGER NOT NULL PRIMARY KEY,
r_name CHAR(25) NOT NULL,
r_comment VARCHAR(152)
);
CREATE TABLE l_nations (
n_nationkey INTEGER NOT NULL PRIMARY KEY,
n_name CHAR(25) NOT NULL,
n_regionkey INTEGER NOT NULL,
n_comment VARCHAR(152),
FOREIGN KEY (n_regionkey) REFERENCES l_regions (r_regionkey)
);
국가(nation)에 대한 지역(region)을 반환해볼게요.
SELECT n.*, r.* FROM l_nations n JOIN l_regions r ON (n_regionkey = r_regionkey);
조건으로 사용할 컬럼 이름이 양쪽에서 같고 그 값이 같아야 한다면, 더 간단한 USING 문법을 쓸 수 있어요.
CREATE TABLE l_regions (
regionkey INTEGER NOT NULL PRIMARY KEY,
name CHAR(25) NOT NULL,
comment VARCHAR(152)
);
CREATE TABLE l_nations (
nationkey INTEGER NOT NULL PRIMARY KEY,
name CHAR(25) NOT NULL,
regionkey INTEGER NOT NULL,
comment VARCHAR(152),
FOREIGN KEY (regionkey) REFERENCES l_regions (regionkey)
);
국가에 대한 지역을 반환해볼게요.
SELECT n.*, r.* FROM l_nations n JOIN l_regions r USING (regionkey);
조건식이 반드시 **동등 비교(equality)**일 필요는 없어요. 어떤 조건식이든 사용할 수 있죠. 한 작업이 더 오래 걸렸는데 비용은 더 적게 든 작업 쌍을 찾는 예를 볼게요.
SELECT s1.t_id, s2.t_id FROM west s1, west s2
WHERE s1.time > s2.time AND s1.cost < s2.cost;
자연 조인 (Natural Joins)
자연 조인은 이름이 같은 컬럼을 기준으로 두 테이블을 연결해요.
도시, 공항 코드(iata), 공항 이름을 담은 예시를 들어볼게요. 두 테이블 모두 일부러 불완전하게 만들어서, 서로 짝이 없는 행이 있게 했어요.
CREATE TABLE city_airport (city_name VARCHAR, iata VARCHAR);
CREATE TABLE airport_names (iata VARCHAR, airport_name VARCHAR);
INSERT INTO city_airport VALUES
('Amsterdam', 'AMS'),
('Rotterdam', 'RTM'),
('Eindhoven', 'EIN'),
('Groningen', 'GRQ');
INSERT INTO airport_names VALUES
('AMS', 'Amsterdam Airport Schiphol'),
('RTM', 'Rotterdam The Hague Airport'),
('MST', 'Maastricht Aachen Airport');
두 테이블이 공유하는 IATA 컬럼으로 조인하려면 이렇게 실행하면 돼요.
SELECT * FROM city_airport NATURAL JOIN airport_names;
결과는 다음과 같아요.
| city_name | iata | airport_name |
|---|---|---|
| Amsterdam | AMS | Amsterdam Airport Schiphol |
| Rotterdam | RTM | Rotterdam The Hague Airport |
양쪽 테이블에 모두 iata 값이 있던 행만 결과에 포함된 걸 볼 수 있어요. 이 쿼리는 일반 JOIN 절에 USING 키워드를 써서도 표현할 수 있어요.
SELECT * FROM city_airport JOIN airport_names USING (iata);
세미 조인과 안티 조인 (Semi and Anti Joins)
**세미 조인(semi join)**은 왼쪽 테이블에서 오른쪽 테이블에 짝이 하나라도 있는 행을 돌려줘요. 반대로 **안티 조인(anti join)**은 왼쪽 테이블에서 오른쪽 테이블에 짝이 하나도 없는 행을 돌려주죠. 세미/안티 조인을 쓰면 결과는 왼쪽 테이블보다 행이 많아지지 않아요. 세미 조인은 IN 연산자와 같은 논리이고, 안티 조인은 NOT IN 연산자와 같은 논리인데, 단 안티 조인은 오른쪽 테이블의 NULL 값을 무시해요.
세미 조인 예시
city_airport 테이블에서 공항 이름이 airport_names 테이블에 있는 도시–공항 코드 쌍을 반환해볼게요.
SELECT * FROM city_airport SEMI JOIN airport_names USING (iata);
| city_name | iata |
|---|---|
| Amsterdam | AMS |
| Rotterdam | RTM |
이 쿼리는 아래와 동일해요.
SELECT * FROM city_airport WHERE iata IN (SELECT iata FROM airport_names);
안티 조인 예시
city_airport 테이블에서 공항 이름이 airport_names 테이블에 없는 도시–공항 코드 쌍을 반환해볼게요.
SELECT * FROM city_airport ANTI JOIN airport_names USING (iata);
| city_name | iata |
|---|---|
| Eindhoven | EIN |
| Groningen | GRQ |
이 쿼리는 아래와 동일해요.
SELECT * FROM city_airport WHERE iata NOT IN (SELECT iata FROM airport_names WHERE iata IS NOT NULL);
레터럴 조인 (Lateral Joins)
LATERAL 키워드를 쓰면 FROM 절 안의 서브쿼리가 그 앞에 있는 서브쿼리를 참조할 수 있어요. 이 기능을 보통 레터럴 조인이라고 부르죠.
SELECT * FROM range(3) t(i), LATERAL (SELECT i + 1) t2(j);
| i | j |
|---|---|
| 0 | 1 |
| 2 | 3 |
| 1 | 2 |
레터럴 조인은 상관 서브쿼리(correlated subquery)의 일반화예요. 상관 서브쿼리가 입력 값 하나당 값 하나만 돌려주는 반면, 레터럴 조인은 입력 값 하나당 여러 값을 돌려줄 수 있거든요.
SELECT * FROM generate_series(0, 1) t(i), LATERAL (SELECT i + 10 UNION ALL SELECT i + 100) t2(j);
| i | j |
|---|---|
| 0 | 10 |
| 1 | 11 |
| 0 | 100 |
| 1 | 101 |
LATERAL을 루프로 생각해보면 이해하기 쉬워요. 첫 번째 서브쿼리의 행들을 하나씩 순회하면서, 그 행을 두 번째(LATERAL) 서브쿼리의 입력으로 사용하는 거죠. 위 예시에서는 테이블 t를 순회하면서 테이블 t2의 정의에서 t의 컬럼 i를 참조해요. 결과에서 t2의 행들이 컬럼 j를 이뤄요.
레터럴 서브쿼리에서 여러 컬럼을 참조하는 것도 가능해요. 첫 번째 예시의 테이블을 사용해볼게요.
CREATE TABLE t1 AS
SELECT * FROM range(3) t(i), LATERAL (SELECT i + 1) t2(j);
SELECT * FROM t1, LATERAL (SELECT i + j) t2(k) ORDER BY ALL;
| i | j | k |
|---|---|---|
| 0 | 1 | 1 |
| 1 | 2 | 3 |
| 2 | 3 | 5 |
DuckDB는
LATERAL조인이 필요할 때를 스스로 감지해서,LATERAL키워드를 생략할 수도 있어요.
포지셔널 조인 (Positional Joins)
데이터 프레임이나 같은 크기의 임베디드 테이블을 다룰 때는 물리적 순서에 따라 행들이 자연스럽게 대응할 수 있어요. 스크립팅 언어에서는 이걸 루프로 쉽게 표현하죠.
for (i = 0; i < n; i++) { f(t1.a[i], t2.b[i]); }
표준 SQL에서는 이걸 표현하기 어려워요. 관계형 테이블에는 순서라는 개념이 없거든요. 하지만 데이터 프레임처럼 불러온 테이블이나 디스크 파일(CSV, Parquet 등)은 자연스러운 순서를 갖고 있어요. 이 순서로 테이블을 연결하는 걸 포지셔널 조인이라고 해요.
CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (s VARCHAR);
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES ('a'), ('b');
SELECT * FROM t1 POSITIONAL JOIN t2;
| x | s |
|---|---|
| 1 | a |
| 2 | b |
| 3 | NULL |
포지셔널 조인은 항상 FULL OUTER 조인이에요. 즉 결과 테이블의 길이는 두 입력 테이블 중 더 긴 쪽을 따라가고, 없는 자리는 NULL로 채워져요.
ASOF 조인 (As-Of Joins)
시간 순서가 있는 데이터를 다루다 보면, 참조 테이블(예: 가격표)에서 가장 가까운(첫 번째) 이벤트를 찾아야 할 때가 자주 있어요. 이걸 as-of 조인이라고 해요.
주식 거래(trades)에 가격(prices)을 붙여볼게요.
SELECT t.*, p.price FROM trades t ASOF JOIN prices p ON t.symbol = p.symbol AND t.when >= p.when;
ASOF 조인은 정렬 필드에 대한 부등 조건이 적어도 하나 있어야 해요. 그 부등 조건은 어떤 데이터 타입이든 >=, >, <=, < 중 아무거나 될 수 있는데, 가장 흔한 형태는 시간(temporal) 타입에 대한 >=예요. 나머지 조건은 모두 **동등 비교(또는 NOT DISTINCT)**여야 해요. 그러니까 두 테이블의 왼쪽/오른쪽 순서가 중요하다는 뜻이에요.
ASOF는 왼쪽의 각 행을 오른쪽 행과 최대 하나만 짝지어요. 짝이 없는 행도 찾고 싶다면 OUTER 조인으로 지정할 수 있어요(예: 가격이 없는 거래, 또는 거래가 없는 가격).
거래에 가격 또는 NULL을 붙여볼게요.
SELECT * FROM trades t ASOF LEFT JOIN prices p ON t.symbol = p.symbol AND t.when >= p.when;
ASOF 조인은 일치하는 컬럼 이름을 USING 문법으로도 지정할 수 있어요. 단, 목록의 마지막 컬럼이 부등 조건이어야 하고, 그 조건은 크거나 같음(>=)이 돼요.
SELECT * FROM trades t ASOF JOIN prices p USING (symbol, "when");
이렇게 USING을 SELECT *와 함께 쓰면, 쿼리는 일치 항목에 대해 왼쪽(probe) 컬럼 값을 돌려주고 오른쪽(build) 컬럼 값은 돌려주지 않아요. 예시에서 prices의 시간을 얻으려면 컬럼을 직접 나열해야 해요.
SELECT t.symbol, t.when AS trade_when, p.when AS price_when, price
FROM trades t ASOF LEFT JOIN prices p USING (symbol, "when");
셀프 조인 (Self-Joins)
DuckDB는 모든 종류의 조인에 대해 셀프 조인을 허용해요. 다만 테이블에 **별칭(alias)**을 붙여야 해요. 같은 테이블 이름을 별칭 없이 쓰면 오류가 나요.
CREATE TABLE t (x INTEGER);
SELECT * FROM t JOIN t USING (x);
Binder Error: Duplicate alias "t" in query!
별칭을 추가하면 쿼리가 정상적으로 파싱돼요.
SELECT * FROM t AS t1 JOIN t AS t2 USING (x);
JOIN 절의 축약 표현 (Shorthands in the JOIN Clause)
JOIN 절 안에서 컬럼 이름을 직접 지정할 수 있어요.
CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (y INTEGER);
INSERT INTO t1 VALUES (1), (2), (4);
INSERT INTO t2 VALUES (2), (3);
SELECT * FROM t1 NATURAL JOIN t2 t2 (x);
| x |
|---|
| 2 |
JOIN 절 안에서 VALUES 절을 쓸 수도 있어요.
SELECT * FROM t1 NATURAL JOIN (VALUES (2), (4)) _(x);
| x |
|---|
| 2 |
| 4 |