FROM / JOIN 절

FROM / JOIN 절 (FROM and JOIN Clauses)

FROM 절은 쿼리의 나머지 부분이 동작할 데이터의 소스를 지정해요. 논리적으로 FROM 절이 쿼리 실행의 시작점이에요. FROM 절은 단일 테이블, JOIN 절로 결합된 여러 테이블의 조합, 또는 서브쿼리 노드 안의 또 다른 SELECT 쿼리를 담을 수 있어요. DuckDB는 SELECT 문 없이도 쿼리할 수 있는 선택적 FROM-first 문법도 제공한답니다.

출처: 문서

본문

FROM 절은 쿼리의 나머지 부분이 동작할 데이터의 소스를 지정해요. 논리적으로 FROM 절이 쿼리 실행이 시작되는 곳이에요. FROM 절은 단일 테이블, JOIN 절로 결합된 여러 테이블의 조합, 또는 서브쿼리 노드 안의 또 다른 SELECT 쿼리를 담을 수 있어요. DuckDB는 SELECT 문 없이도 쿼리할 수 있는 선택적 FROM-first 문법도 제공해요.

예시 (Examples)

tbl이라는 테이블의 모든 컬럼 선택:

SELECT *
FROM tbl;

FROM-first 문법으로 테이블의 모든 컬럼 선택:

FROM tbl
SELECT *;

FROM-first 문법을 쓰고 SELECT 절을 생략해서 모든 컬럼 선택:

FROM tbl;

별칭 tn을 통해 tbl이라는 테이블의 모든 컬럼 선택:

SELECT tn.*
FROM tbl tn;

접두사 별칭 사용:

SELECT tn.*
FROM tn: tbl;

스키마 schema_name의 테이블 tbl에서 모든 컬럼 선택:

SELECT *
FROM schema_name.tbl;

테이블 함수 range에서 컬럼 i 선택 — range 함수의 첫 컬럼이 i로 이름 변경됨:

SELECT t.i
FROM range(100) AS t(i);

test.csv라는 CSV 파일의 모든 컬럼 선택:

SELECT *
FROM 'test.csv';

서브쿼리의 모든 컬럼 선택:

SELECT *
FROM (SELECT * FROM tbl);

테이블의 전체 행을 struct로 선택:

SELECT t
FROM t;

서브쿼리의 전체 행을 struct(즉, 단일 컬럼)로 선택:

SELECT t
FROM (SELECT unnest(generate_series(41, 43)) AS x, 'hello' AS y) t;

두 테이블 조인:

SELECT *
FROM tbl
JOIN other_table
  ON tbl.key = other_table.key;

테이블에서 10% 표본 선택:

SELECT *
FROM tbl
TABLESAMPLE 10%;

테이블에서 10행 표본 선택:

SELECT *
FROM tbl
TABLESAMPLE 10 ROWS;

WHERE 절과 집계와 함께 FROM-first 문법 사용:

FROM range(100) AS t(i)
SELECT sum(t.i)
WHERE i % 2 = 0;

테이블 함수 (Table Functions)

DuckDB의 일부 함수는 개별 값 대신 전체 테이블을 반환해요. 이런 함수는 그에 맞게 _테이블 함수(table functions)_라고 불리며, 일반 테이블 참조처럼 FROM 절에서 사용할 수 있어요. 예로는 read_csv, read_parquet, range, generate_series, repeat, unnest, glob이 있어요(여기 예시 중 일부는 스칼라 함수와 테이블 함수 둘 다로 쓸 수 있다는 점에 주의해요).

예를 들어,

SELECT *
FROM 'test.csv';

는 암묵적으로 read_csv 테이블 함수 호출로 변환돼요:

SELECT *
FROM read_csv('test.csv');

모든 테이블 함수는 WITH ORDINALITY 접미사를 지원해요. 이 접미사는 생성된 행을 1부터 열거하는 정수 컬럼 ordinality를 반환 테이블에 추가해요.

SELECT * 
FROM read_csv('test.csv') WITH ORDINALITY;

같은 결과는 row_number 윈도우 함수로도 얻을 수 있다는 점을 참고해요. 하지만 조인이 있는 경우, WITH ORDINALITY는 서브쿼리를 사용하지 않고 최종 결과 집합 대신 조인의 한쪽을 열거할 수 있게 해줘요.

조인 (Joins)

조인은 두 테이블 또는 릴레이션을 수평으로 연결하는 데 사용하는 기본적인 관계 연산이에요. 릴레이션은 조인 절에 쓰인 방식에 따라 조인의 _왼쪽_과 오른쪽 변이라고 불러요. 각 결과 행은 두 릴레이션의 컬럼을 모두 가져요.

조인은 각 릴레이션에서 행 쌍을 매칭하는 규칙을 사용해요. 종종 이는 프레디킷이지만, 지정될 수 있는 다른 암시적 규칙도 있어요.

아우터 조인 (Outer Joins)

OUTER 조인이 지정되면 매치가 없는 행도 여전히 반환될 수 있어요. 아우터 조인은 다음 중 하나일 수 있어요:

• LEFT (왼쪽 릴레이션의 모든 행이 최소한 한 번 나타남)
• RIGHT (오른쪽 릴레이션의 모든 행이 최소한 한 번 나타남)
• FULL (양쪽 릴레이션의 모든 행이 최소한 한 번 나타남)

OUTER가 아닌 조인은 INNER예요(쌍을 이룬 행만 반환됨).

쌍을 이루지 않은 행이 반환될 때 다른 테이블의 속성은 NULL로 설정돼요.

크로스 프로덕트 조인 (카테시안 곱) (Cross Product Joins)

가장 단순한 유형의 조인은 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)
);

국가(nations)에 대한 지역(regions) 반환:

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);

표현식이 등식이어야 할 필요는 없어요 — 어떤 프레디킷이든 사용할 수 있어요:

더 오래 걸렸지만 비용은 덜 든 작업 쌍 반환:

SELECT s1.t_id, s2.t_id
FROM west s1, west s2
WHERE s1.time > s2.time
  AND s1.cost < s2.cost;

내추럴 조인 (Natural Joins)

내추럴 조인은 같은 이름을 공유하는 속성을 기준으로 두 테이블을 조인해요.

예를 들어 도시, 공항 코드, 공항 이름이 있는 다음 예시를 들어 볼게요. 두 테이블 모두 의도적으로 불완전해서, 다른 테이블에 매칭 쌍이 없어요.

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 속성이 있는 행만 결과에 포함됐다는 점을 참고해요.

이 쿼리는 USING 키워드와 함께 기본 JOIN 절로도 표현할 수 있어요:

SELECT *
FROM city_airport
JOIN airport_names
USING (iata);

세미/안티 조인 (Semi and Anti Joins)

세미 조인은 오른쪽 테이블에 매치가 하나 이상 있는 왼쪽 테이블의 행을 반환해요. 안티 조인은 오른쪽 테이블에 매치가 없는 왼쪽 테이블의 행을 반환해요. 세미 또는 안티 조인을 사용하면 결과가 왼쪽 테이블보다 행이 더 많아지지 않아요. 세미 조인은 IN 연산자 문과 같은 논리를 제공해요. 안티 조인은 NOT IN 연산자와 같은 논리를 제공하되, 안티 조인은 오른쪽 테이블의 NULL 값을 무시한다는 차이가 있어요.

세미 조인 예시 (Semi Join Example)

airport_names 테이블에 공항 이름이 있는 city_airport 테이블의 도시-공항 코드 쌍 목록 반환:

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);

안티 조인 예시 (Anti Join Example)

airport_names 테이블에 공항 이름이 없는 city_airport 테이블의 도시-공항 코드 쌍 목록 반환:

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

래터럴 조인은 상관 서브쿼리의 일반화예요 — 입력 값마다 단일 값만이 아니라 여러 값을 반환할 수 있기 때문이에요.

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의 정의에서 그 컬럼 i를 참조해요. t2의 행이 결과의 컬럼 j를 형성해요.

LATERAL 서브쿼리에서 여러 속성을 참조하는 것도 가능해요. 첫 번째 예시의 테이블을 사용해서:

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 값으로 채워져요.

As-Of 조인 (As-Of Joins)

시간(temporal) 또는 유사하게 정렬된 데이터를 다룰 때 흔한 연산은 참조 테이블(예: 가격)에서 가장 가까운(첫 번째) 이벤트를 찾는 것이에요. 이를 _as-of 조인_이라고 해요:

주식 거래에 가격을 붙이기:

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");

symbol, trades.when, price를 반환해요(하지만 prices.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는 모든 유형의 조인에 대해 셀프 조인을 허용해요. 테이블에 별칭을 붙여야 한다는 점을 참고하세요 — 별칭 없이 같은 테이블 이름을 사용하면 에러가 발생해요:

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

FROM-First 문법

DuckDB의 SQL은 FROM-first 문법을 지원해요. 즉 FROM 절을 SELECT 절 앞에 놓거나 SELECT 절을 완전히 생략할 수 있어요. 다음 예시로 이를 보여드릴게요:

CREATE TABLE tbl AS
    SELECT *
    FROM (VALUES ('a'), ('b')) t1(s), range(1, 3) t2(i);

SELECT 절이 있는 FROM-First 문법

다음 문은 FROM-first 문법의 사용을 보여줘요:

FROM tbl
SELECT i, s;

이는 다음와 동일해요:

SELECT i, s
FROM tbl;

i	s
1	a
2	a
1	b
2	b

SELECT 절이 없는 FROM-First 문법

다음 문은 선택적 SELECT 절의 사용을 보여줘요:

FROM tbl;

이는 다음와 동일해요:

SELECT *
FROM tbl;

s	i
a	1
a	2
b	1
b	2

문법 (Syntax)

더 알아보기 (Learn more)

• GROUP BY 절 — 그룹핑과 집계.
• 집계 함수 — 조인과 함께 쓰는 함수들.