JOIN

JOIN 절은 각 테이블에 공통으로 존재하는 값을 사용해 하나 이상의 테이블에서 컬럼을 결합하고, 그 결과로 새 테이블을 만듭니다. 이는 SQL을 지원하는 데이터베이스에서 흔히 사용되는 연산이며, 관계대수의 조인에 해당합니다. 하나의 테이블을 자기 자신과 조인하는 특수한 경우는 보통 “self-join”이라고 합니다. 구문

SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ALL|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...

ON 절의 표현식과 USING 절의 컬럼을 “조인 키”라고 합니다. 별도로 명시하지 않는 한, JOIN은 “조인 키”가 일치하는 행들로 카테시안 곱을 생성하므로, 원본 테이블보다 훨씬 많은 행이 결과에 포함될 수 있습니다.

지원되는 JOIN 유형

모든 표준 SQL JOIN 유형을 지원합니다:

유형	설명
`INNER JOIN`	일치하는 행만 반환됩니다.
`LEFT OUTER JOIN`	일치하는 행과 함께 왼쪽 테이블의 비일치 행도 반환됩니다.
`RIGHT OUTER JOIN`	일치하는 행과 함께 오른쪽 테이블의 비일치 행도 반환됩니다.
`FULL OUTER JOIN`	일치하는 행과 함께 양쪽 테이블의 비일치 행도 반환됩니다.
`CROSS JOIN`	전체 테이블의 카테시안 곱을 생성하며, “조인 키”는 지정하지 않습니다.
`NATURAL JOIN`	두 테이블에서 이름이 같은 모든 컬럼을 기준으로 자동으로 조인하며, 각 공통 컬럼은 결과에 한 번만 나타납니다. `INNER`(기본값), `LEFT`, `RIGHT`, `FULL` 변형을 지원합니다. 컬럼 목록이 자동으로 도출된다는 점만 제외하면 `JOIN ... USING (col1, col2, ...)`와 동일합니다.

유형을 지정하지 않은 JOIN은 INNER를 의미합니다.
OUTER 키워드는 생략해도 됩니다.
CROSS JOIN의 대체 구문으로, FROM 절에서 여러 테이블을 쉼표로 구분해 지정할 수 있습니다.
NATURAL JOIN에 일치하는 컬럼이 없으면 CROSS JOIN처럼 동작합니다.

ClickHouse에서 추가로 지원하는 조인 유형은 다음과 같습니다:

유형	설명
`LEFT SEMI JOIN`, `RIGHT SEMI JOIN`	카테시안 곱을 생성하지 않고 “조인 키”에 대해 허용 목록을 적용합니다.
`LEFT ANTI JOIN`, `RIGHT ANTI JOIN`	카테시안 곱을 생성하지 않고 “조인 키”에 대해 거부 목록을 적용합니다.
`LEFT ANY JOIN`, `RIGHT ANY JOIN`, `INNER ANY JOIN`	표준 `JOIN` 유형의 카테시안 곱을 부분적으로(`LEFT` 및 `RIGHT`의 반대편) 또는 완전히(`INNER` 및 `FULL`) 비활성화합니다.
`ASOF JOIN`, `LEFT ASOF JOIN`	정확히 일치하지 않는 조건으로 시퀀스를 조인합니다. `ASOF JOIN` 사용법은 아래에 설명되어 있습니다.
`PASTE JOIN`	두 테이블을 가로로 이어 붙입니다.

analyzer를 사용할 때 semi_join_include_columns_from_both_sides 또는 anti_join_include_columns_from_both_sides 설정을 비활성화하면, 조인 결과가 만들어진 이후에 해석되는 표현식에서는 해당 조인의 보존되는 쪽 컬럼만 노출됩니다.

LEFT SEMI JOIN 및 LEFT ANTI JOIN은 왼쪽 컬럼만 노출합니다.
RIGHT SEMI JOIN 및 RIGHT ANTI JOIN은 오른쪽 컬럼만 노출합니다.
이 동작은 SELECT, PREWHERE, WHERE, GROUP BY, HAVING, QUALIFY, ORDER BY, LIMIT BY 등의 절에 적용되며, t1.*와 같은 한정된 와일드카드에도 적용됩니다.
같은 JOIN의 ON 표현식에서는 여전히 양쪽을 모두 참조할 수 있습니다.

이 설정이 활성화되어 있으면(기본값) ClickHouse는 레거시 동작을 유지합니다. 즉, 양쪽 모두에 접근할 수 있으며 SELECT *는 두 테이블의 컬럼을 모두 확장합니다.

join_algorithm이 partial_merge로 설정된 경우, RIGHT JOIN 및 FULL JOIN은 ALL 엄격성에서만 지원됩니다(SEMI, ANTI, ANY, ASOF는 지원되지 않음).

LATERAL JOIN

JOIN LATERAL을 사용하면 조인 오른쪽의 서브쿼리에서 왼쪽에 있는 테이블 표현식의 컬럼을 참조할 수 있습니다. 서브쿼리는 참조하는 왼쪽 컬럼 값의 고유한 조합마다 한 번씩 평가되며, 그 결과는 해당 조합을 가진 모든 왼쪽 행과 조인됩니다:

SELECT ...
FROM <left_table>
[INNER|LEFT] JOIN LATERAL (SELECT ... WHERE <expr referencing left_table>) AS <alias> ON true

ON true 프레디케이트는 다른 INNER 또는 LEFT JOIN과 마찬가지로 필수이며, 생략하면 구문 오류가 발생합니다. 이 기능은 Experimental 기능이며 기본적으로 비활성화되어 있습니다. allow_experimental_lateral_join 설정으로 활성화하십시오. 현재는 다음 범위만 지원되며, 그 외의 사용은 모두 오류로 거부됩니다.

INNER JOIN LATERAL과 LEFT JOIN LATERAL만 지원됩니다. RIGHT, FULL, PASTE, NATURAL 조인은 지원되지 않으며, LATERAL은 CROSS 조인이나 쉼표 조인과는 함께 사용할 수 없습니다.
기본 ALL 엄격성만 지원됩니다. ANY, SEMI, ANTI, ASOF는 지원되지 않습니다.
ON true(ON 1도 허용됨) 이외의 조인 프레디케이트는 사용할 수 없습니다. USING은 지원되지 않으며, 프레디케이트를 생략할 수도 없습니다. 양쪽을 연관시키는 필터는 lateral 서브쿼리의 WHERE 절에 작성하십시오.
GLOBAL 및 LOCAL 조인 수정자는 지원되지 않습니다.
lateral 서브쿼리는 왼쪽의 컬럼을 하나 이상 참조해야 합니다. 상관관계가 없는 서브쿼리에는 일반 조인을 사용하십시오.
lateral 서브쿼리는 왼쪽 행마다 한 번씩이 아니라, 참조하는 왼쪽 컬럼의 고유 값마다 한 번씩 평가됩니다. 따라서 rand나 generateUUIDv4처럼 쿼리 내에서 비결정적인 함수나, generateRandom처럼 무작위 행을 생성하는 테이블 함수를 포함해서는 안 됩니다. now처럼 쿼리 내에서 값이 일정한 함수는 사용할 수 있습니다.
lateral 테이블 표현식으로는 서브쿼리만 지원됩니다. PostgreSQL의 테이블 소스 형식인 LATERAL unnest(...)와 CROSS JOIN UNNEST(...)는 지원되지 않으므로, 대신 ARRAY JOIN 절을 사용하십시오.
lateral 서브쿼리의 GROUP BY와 ORDER BY는 모든 평가 결과를 합쳐 한 번만 실행되므로, max_rows_to_group_by, max_rows_to_sort, max_bytes_to_sort 제한은 한 번의 평가가 아니라 모든 평가의 행을 합산하여 계산합니다. 이 제한은 throw 오버플로우 모드에서만 지원되며, any와 break는 거부됩니다.
모든 평가의 행은 단일 조인을 통해 왼쪽 행과 매칭됩니다. 다른 해시 조인과 마찬가지로 max_rows_in_join과 max_bytes_in_join은 이 조인에서 메모리에 유지되는 쪽을 제한하며, 어느 쪽을 유지할지는 플래너가 결정합니다. 기본 설정(correlated_subqueries_use_in_memory_buffer = 1)에서는 lateral 서브쿼리를 평가하기 전에 왼쪽 행을 모두 읽어야 하므로 JOIN LATERAL의 왼쪽이 유지됩니다. 그 외의 경우에는 모든 평가 결과 전체가 유지될 수 있습니다. 이 제한은 항상 join_overflow_mode가 throw인 것처럼 적용됩니다. break를 사용하면 조인이 관련 없는 왼쪽 행을 아무 경고 없이 누락시키기 때문입니다.

예시

SELECT u.id, o.total
FROM users AS u
LEFT JOIN LATERAL
(
    SELECT total
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY total DESC
    LIMIT 1
) AS o ON true
SETTINGS allow_experimental_lateral_join = 1;

설정

기본 조인 유형은 join_default_strictness 설정으로 재정의할 수 있습니다. ANY JOIN 작업에서 ClickHouse 서버의 동작은 any_join_distinct_right_table_keys 설정에 따라 달라집니다. 관련 항목

ClickHouse가 CROSS JOIN을 INNER JOIN으로 재작성하지 못할 때의 동작은 cross_to_inner_join_rewrite 설정으로 지정할 수 있습니다. 기본값은 1이며, 이 경우 조인은 계속 수행되지만 더 느려집니다. 오류를 발생시키려면 cross_to_inner_join_rewrite를 0으로 설정하고, 크로스 조인을 실행하지 않고 대신 모든 쉼표 조인/크로스 조인을 재작성하도록 강제하려면 2로 설정하십시오. 값이 2일 때 재작성에 실패하면 “Please, try to simplify WHERE section”라는 오류 메시지가 표시됩니다.

`ON` 절의 조건

ON 절에는 AND 및 OR 연산자로 결합된 여러 조건을 포함할 수 있습니다. 조인 키를 지정하는 조건은 다음 요구 사항을 충족해야 합니다.

왼쪽 테이블과 오른쪽 테이블을 모두 참조해야 합니다
동등 연산자를 사용해야 합니다

그 밖의 조건에서는 다른 연산자를 사용할 수 있으며, 쿼리의 왼쪽 테이블이나 오른쪽 테이블 중 하나만 참조하거나 둘 다 참조할 수 있습니다. 전체 복합 조건이 충족되면 행이 조인됩니다. 조건이 충족되지 않더라도 JOIN 유형에 따라 행이 결과에 포함될 수 있습니다. 같은 조건을 WHERE 절에 넣었는데 조건이 충족되지 않으면, 행은 항상 결과에서 필터링된다는 점에 유의하십시오. ON 절 내부의 OR 연산자는 해시 조인 알고리즘을 사용해 동작합니다. 즉, JOIN의 조인 키가 있는 각 OR 인수마다 별도의 해시 테이블이 생성되므로, ON 절의 OR 표현식 수가 늘어날수록 메모리 활용량과 쿼리 실행 시간은 선형적으로 증가합니다.

동등 연산자(=)를 사용한 조건만 조인 키가 됩니다. 서로 다른 테이블의 컬럼 간에 다른 연산자를 사용하는 것도 지원되지만, 동등 조건이 하나도 없으면 조인 시 모든 행 쌍을 검사해야 하므로 훨씬 느려집니다. 자세한 내용은 임의의 ON 조건을 사용하는 조인을 참조하십시오.

예시 table_1 및 table_2를 가정해 보겠습니다.

┌─Id─┬─name─┐     ┌─Id─┬─text───────────┬─scores─┐
│  1 │ A    │     │  1 │ Text A         │     10 │
│  2 │ B    │     │  1 │ Another text A │     12 │
│  3 │ C    │     │  2 │ Text B         │     15 │
└────┴──────┘     └────┴────────────────┴────────┘

하나의 조인 키 조건과 table_2에 대한 추가 조건이 포함된 쿼리:

Query

SELECT name, text FROM table_1 LEFT OUTER JOIN table_2
    ON table_1.Id = table_2.Id AND startsWith(table_2.text, 'Text');

결과에는 이름이 C이고 text 컬럼이 비어 있는 행도 포함된다는 점에 유의하십시오. 이는 OUTER 조인을 사용했기 때문에 결과에 포함된 것입니다.

Response

┌─name─┬─text───┐
│ A    │ Text A │
│ B    │ Text B │
│ C    │        │
└──────┴────────┘

INNER 유형의 조인에서 여러 조건을 사용하는 쿼리:

Query

SELECT name, text, scores FROM table_1 INNER JOIN table_2
    ON table_1.Id = table_2.Id AND table_2.scores > 10 AND startsWith(table_2.text, 'Text');

Response

┌─name─┬─text───┬─scores─┐
│ B    │ Text B │     15 │
└──────┴────────┴────────┘

조인의 INNER 유형과 OR 조건을 사용하는 쿼리:

Query

CREATE TABLE t1 (`a` Int64, `b` Int64) ENGINE = MergeTree() ORDER BY a;

CREATE TABLE t2 (`key` Int32, `val` Int64) ENGINE = MergeTree() ORDER BY key;

INSERT INTO t1 SELECT number as a, -a as b from numbers(5);

INSERT INTO t2 SELECT if(number % 2 == 0, toInt64(number), -number) as key, number as val from numbers(5);

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key;

Response

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 1 │ -1 │   1 │
│ 2 │ -2 │   2 │
│ 3 │ -3 │   3 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

조인 유형이 INNER이며 조건에 OR와 AND를 사용하는 쿼리:

t1.a = t2.key AND t1.b > 0 AND t2.b > t2.c와 같이 단일 테이블의 컬럼을 사용하는 같지 않은 조건은 해당 테이블에만 적용됩니다. t1.b > 0은 t1의 컬럼만 사용하고 t2.b > t2.c는 t2의 컬럼만 사용합니다. t1.a = t2.key AND t1.b > t2.key와 같이 서로 다른 테이블의 컬럼을 비교하는 같지 않은 조건도 지원됩니다. 자세한 내용은 아래 섹션을 확인하십시오.

Query

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key AND t2.val > 3;

Response

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 2 │ -2 │   2 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

서로 다른 테이블의 컬럼에 대한 부등 조건 JOIN

ClickHouse는 동등 조건뿐 아니라 부등 조건도 포함한 ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN을 지원합니다. ON 절에 부등 조건과 함께 두 테이블 간의 동등 조건이 있으면, 동등 조건이 조인 키가 되고 부등 조건은 이 키로 매칭된 행에 대해 검사됩니다. 이러한 혼합 조건은 hash, parallel_hash 및 grace_hash 조인 알고리즘에서만 실행됩니다. ON 절에 두 테이블 간의 동등 조건이 없으면 매칭에 사용할 조인 키가 없습니다. 이러한 조인은 조건이 한 쌍의 부등 조건이면 ie_join으로 실행되고, 그렇지 않으면 블록 중첩 루프 조인(block nested loop join)으로 실행됩니다. 조인 중 평가되는 조건에는 행 수를 유지해야 하므로 arrayJoin을 포함할 수 없습니다. 대신 서브쿼리에서 ARRAY JOIN을 사용하십시오. 예시 테이블 t1:

┌─key──┬─attr─┬─a─┬─b─┬─c─┐
│ key1 │ a    │ 1 │ 1 │ 2 │
│ key1 │ b    │ 2 │ 3 │ 2 │
│ key1 │ c    │ 3 │ 2 │ 1 │
│ key1 │ d    │ 4 │ 7 │ 2 │
│ key1 │ e    │ 5 │ 5 │ 5 │
│ key2 │ a2   │ 1 │ 1 │ 1 │
│ key4 │ f    │ 2 │ 3 │ 4 │
└──────┴──────┴───┴───┴───┘

테이블 t2

┌─key──┬─attr─┬─a─┬─b─┬─c─┐
│ key1 │ A    │ 1 │ 2 │ 1 │
│ key1 │ B    │ 2 │ 1 │ 2 │
│ key1 │ C    │ 3 │ 4 │ 5 │
│ key1 │ D    │ 4 │ 1 │ 6 │
│ key3 │ a3   │ 1 │ 1 │ 1 │
│ key4 │ F    │ 1 │ 1 │ 1 │
└──────┴──────┴───┴───┴───┘

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.key = t2.key AND (t1.a < t2.a) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);

key1    a    1    1    2    key1    B    2    1    2
key1    a    1    1    2    key1    C    3    4    5
key1    a    1    1    2    key1    D    4    1    6
key1    b    2    3    2    key1    C    3    4    5
key1    b    2    3    2    key1    D    4    1    6
key1    c    3    2    1    key1    D    4    1    6
key1    d    4    7    2            0    0    \N
key1    e    5    5    5            0    0    \N
key2    a2    1    1    1            0    0    \N
key4    f    2    3    4            0    0    \N

임의의 ON 조건을 사용하는 JOIN

ON 절에는 범위 검사, 산술 비교, 스칼라 함수 호출 등 두 테이블의 컬럼에 대한 임의의 불리언 표현식을 사용할 수 있습니다. 두 테이블 간의 동등 비교가 없으면 행을 매칭하는 데 사용할 조인 키가 없습니다. 부등 조건 한 쌍으로 이루어진 조건은 join_algorithm에 ie_join 알고리즘이 포함되어 있으면(기본값에 포함되어 있음) ie_join으로 실행됩니다. 그 밖의 조건은 블록 중첩 루프 조인(block nested loop join)으로 실행됩니다. 이 방식에서는 오른쪽 테이블을 구체화한 다음, 모든 행 쌍에 대해 조건을 평가합니다. ALL INNER JOIN은 해당 조건을 필터로 사용하는 CROSS JOIN과 동일한 형태로 실행됩니다. 블록 중첩 루프 조인은 ASOF JOIN, PASTE JOIN, ANY FULL JOIN을 제외한 모든 조인 유형과 엄격성을 지원합니다. 이 알고리즘은 join_algorithm에 지정할 수 있는 값이 아니며, 다른 어떤 알고리즘으로도 조건을 실행할 수 없을 때에만 사용되는 최후의 수단입니다. EXPLAIN 출력에서는 BlockNestedLoopJoin 단계로 표시됩니다. allow_block_nested_loop_join이 비활성화되어 있으면 이 알고리즘이 필요한 쿼리는 INVALID_JOIN_ON_EXPRESSION 오류와 함께 거부됩니다. 예시

Query

CREATE TABLE orders (id UInt32, amount UInt32) ENGINE = Memory;
CREATE TABLE discounts (min_amount UInt32, max_amount UInt32, pct UInt32) ENGINE = Memory;

INSERT INTO orders VALUES (1, 50), (2, 150), (3, 300);
INSERT INTO discounts VALUES (100, 199, 5), (200, 1000, 10);

SELECT o.id, o.amount, d.pct
FROM orders AS o
LEFT JOIN discounts AS d ON o.amount BETWEEN d.min_amount AND d.max_amount
ORDER BY o.id;

Response

┌─id─┬─amount─┬─pct─┐
│  1 │     50 │   0 │
│  2 │    150 │   5 │
│  3 │    300 │  10 │
└────┴────────┴─────┘

일치하는 항목이 없는 행은 다른 모든 조인과 마찬가지로 join_use_nulls에 따라 채워집니다. 위의 예처럼 기본값으로 채워지며, 이 설정이 활성화되어 있으면 NULL로 채워집니다. 엄격성 ANY와 SEMI는 일반적으로 조인 키를 공유하는 행 그룹마다 1개의 행을 유지합니다. 여기서는 그러한 그룹이 없으므로, 조인을 주도하는 테이블의 행마다 1개의 행을 유지합니다. 즉, LEFT ANY와 LEFT SEMI는 각 왼쪽 행을 최대 한 번, RIGHT ANY와 RIGHT SEMI는 각 오른쪽 행을 최대 한 번 출력합니다. ANY INNER는 양쪽을 동시에 제한합니다. 두 테이블의 각 행은 최대 한 번만 사용되므로, 결과의 행 수는 더 작은 테이블의 행 수를 넘지 않습니다. 이러한 결과를 구성하는 쌍은 ANY가 의미하는 대로 임의로 정해지며, 동일한 쿼리를 두 번 실행해도 달라질 수 있습니다. ANY INNER에서는 이 특성이 행 수에까지 적용됩니다. 행을 어떤 순서로 검사하든 양쪽의 각 행을 탐욕적으로 짝짓다 보면 짝을 찾지 못한 행의 수가 달라질 수 있으므로, 조인 키가 없는 ANY INNER 조인에 대한 count() 결과는 재현할 수 없으며 스레드 수와 오른쪽 테이블의 물리적 순서에 따라 달라집니다. 성능 모든 행 쌍을 검사하므로 작업량은 두 테이블 행 수의 합이 아니라 곱에 비례하여 증가합니다. 따라서 블록 중첩 루프 조인(block nested loop join)은 동일한 데이터에 대한 해시 조인보다 비용이 수십~수천 배 이상 크며, 테이블이 커질수록 그 격차는 더 벌어집니다. ON 절에 동등 조건을 하나 이상 포함하여 쿼리를 작성할 수 있다면 그렇게 작성하십시오. LEFT ANY, ANY INNER, LEFT SEMI, LEFT ANTI 조인은 왼쪽 행과 처음으로 일치하는 행을 찾는 즉시 오른쪽 테이블 스캔을 중단하므로, 일반적으로 대응하는 ALL 조인보다 비용이 적습니다. 반면 오른쪽이 주도하는 대응 조인(RIGHT ANY, RIGHT SEMI, RIGHT ANTI)은 어떤 오른쪽 행이 일치했는지에 따라 결과가 달라지므로 모든 쌍을 검사합니다. join_any_take_last_row는 여기서 아무런 영향을 주지 않습니다. 조인 키가 없으므로 마지막 행을 선택할 일치 행 그룹 자체가 존재하지 않기 때문입니다. 메모리 사용량은 구체화된 오른쪽 테이블의 크기로 제한되며, 결과 크기에 따라 늘어나지 않습니다. 오른쪽 테이블에는 메모리 제한에 나열된 대로 다른 조인 알고리즘과 동일한 설정이 적용됩니다. max_rows_in_join, max_bytes_in_join, join_overflow_mode는 오른쪽 테이블의 크기를 제한하며, max_bytes_before_external_join을 설정하면 오른쪽 테이블이 디스크로 스필(spill)됩니다.

JOIN 키의 NULL 및 NaN 값

NULL은 자기 자신을 포함해 어떤 값과도 같지 않습니다. 즉, 한 테이블에서 JOIN 키의 값이 NULL이면 다른 테이블의 NULL 값과 일치하지 않습니다. 예시 테이블 A:

┌───id─┬─name────┐
│    1 │ Alice   │
│    2 │ Bob     │
│ ᴺᵁᴸᴸ │ Charlie │
└──────┴─────────┘

테이블 B:

┌───id─┬─score─┐
│    1 │    90 │
│    3 │    85 │
│ ᴺᵁᴸᴸ │    88 │
└──────┴───────┘

SELECT A.name, B.score FROM A LEFT JOIN B ON A.id = B.id

┌─name────┬─score─┐
│ Alice   │    90 │
│ Bob     │     0 │
│ Charlie │     0 │
└─────────┴───────┘

A 테이블의 Charlie가 있는 행과 B 테이블의 점수 88인 행은 JOIN 키에 NULL 값이 있기 때문에 결과에 포함되지 않습니다. NULL 값을 서로 일치시키려면 JOIN 키를 비교할 때 isNotDistinctFrom 함수를 사용하십시오.

SELECT A.name, B.score FROM A LEFT JOIN B ON isNotDistinctFrom(A.id, B.id)

┌─name────┬─score─┐
│ Alice   │    90 │
│ Bob     │     0 │
│ Charlie │    88 │
└─────────┴───────┘

float JOIN 키의 NaN 값에는 위의 NULL 규칙이 적용되지 않습니다. 두 NaN 값을 스칼라 방식으로 비교하면(NaN = NaN) 결과는 0이지만, JOIN 키는 스칼라 의미 체계로 비교되지 않으므로 NaN 키가 일치할 수 있습니다. 실제로 일치하는지는 구현 세부 사항이며 조인 알고리즘, 키 유형 및 세션 설정에 따라 달라집니다. 특정 동작에 의존하지 마십시오. NaN 행이 일치하지 않아야 한다면 NULL 값으로 매핑하십시오: ON if(isNaN(A.id), NULL, A.id) = B.id. ASOF JOIN에서는 가장 가까운 일치 항목의 컬럼을 정렬 순서로 비교하는데, NaN은 이를 지원하지 않으므로 양쪽에서 이러한 행을 필터링하십시오.

ASOF JOIN 사용

정확히 일치하는 항목이 없는 레코드를 조인해야 할 때 ASOF JOIN이 유용합니다. 이 JOIN 알고리즘을 사용하려면 테이블에 특수한 컬럼이 필요합니다. 이 컬럼은 다음 조건을 충족해야 합니다.

정렬된 시퀀스를 포함해야 합니다.
다음 타입 중 하나여야 합니다: Int, UInt, Float, Date, DateTime, Decimal.
hash 조인 알고리즘에서는 JOIN 절의 유일한 컬럼일 수 없습니다.

구문 ASOF JOIN ... ON:

SELECT expressions_list
FROM table_1
ASOF LEFT JOIN table_2
ON equi_cond AND closest_match_cond

등가 조건은 개수 제한 없이 사용할 수 있으며, 가장 가까운 일치 조건은 정확히 하나만 사용할 수 있습니다. 예를 들어, SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.t. 가장 가까운 일치에 지원되는 조건: >, >=, <, <=. 구문 ASOF JOIN ... USING:

SELECT expressions_list
FROM table_1
ASOF JOIN table_2
USING (equi_column1, ... equi_columnN, asof_column)

ASOF JOIN은 동등 조건으로 조인할 때 equi_columnX를 사용하고, table_1.asof_column >= table_2.asof_column 조건에서 가장 가까운 값을 기준으로 조인할 때는 asof_column을 사용합니다. asof_column 컬럼은 항상 USING 절의 마지막에 위치합니다. 예시로, 다음 테이블을 살펴보겠습니다.

         table_1                           table_2
      event   | ev_time | user_id       event   | ev_time | user_id
    ----------|---------|----------   ----------|---------|----------
                  ...                               ...
    event_1_1 |  12:00  |  42         event_2_1 |  11:59  |   42
                  ...                 event_2_2 |  12:30  |   42
    event_1_2 |  13:00  |  42         event_2_3 |  13:00  |   42
                  ...                               ...

ASOF JOIN은 table_1의 사용자 이벤트 타임스탬프를 기준으로, 가장 가까운 일치 조건에 맞으면서 table_1의 이벤트 타임스탬프와 가장 가까운 타임스탬프를 가진 table_2의 이벤트를 찾을 수 있습니다. 같은 타임스탬프 값이 있으면 그것이 가장 가까운 값으로 선택됩니다. 여기서 user_id 컬럼은 동등 조건 조인에 사용할 수 있고, ev_time 컬럼은 최근접 일치 조인에 사용할 수 있습니다. 이 예시에서는 event_1_1은 event_2_1과 조인할 수 있고 event_1_2는 event_2_3과 조인할 수 있지만, event_2_2는 조인할 수 없습니다.

ASOF JOIN은 hash 및 full_sorting_merge 조인 알고리즘에서만 지원됩니다. Join 테이블 엔진에서는 지원되지 않습니다.

PASTE JOIN 사용법

PASTE JOIN의 결과는 왼쪽 서브쿼리의 모든 컬럼 뒤에 오른쪽 서브쿼리의 모든 컬럼이 이어지는 테이블입니다. 행은 원본 테이블에서의 위치를 기준으로 대응됩니다(행 순서가 정의되어 있어야 합니다). 서브쿼리가 반환하는 행 수가 서로 다르면 초과된 행은 잘립니다. 예시:

SELECT *
FROM
(
    SELECT number AS a
    FROM numbers(2)
) AS t1
PASTE JOIN
(
    SELECT number AS a
    FROM numbers(2)
    ORDER BY a DESC
) AS t2

┌─a─┬─t2.a─┐
│ 0 │    1 │
│ 1 │    0 │
└───┴──────┘

참고: 이 경우 병렬로 읽으면 결과가 비결정적일 수 있습니다. 예시는 다음과 같습니다:

SELECT *
FROM
(
    SELECT number AS a
    FROM numbers_mt(5)
) AS t1
PASTE JOIN
(
    SELECT number AS a
    FROM numbers(10)
    ORDER BY a DESC
) AS t2
SETTINGS max_block_size = 2;

┌─a─┬─t2.a─┐
│ 2 │    9 │
│ 3 │    8 │
└───┴──────┘
┌─a─┬─t2.a─┐
│ 0 │    7 │
│ 1 │    6 │
└───┴──────┘
┌─a─┬─t2.a─┐
│ 4 │    5 │
└───┴──────┘

분산 JOIN

분산 테이블이 포함된 JOIN을 실행하는 방법은 두 가지입니다.

일반 JOIN을 사용하면 쿼리가 원격 서버로 전송됩니다. 오른쪽 테이블을 만들기 위해 각 서버에서 서브쿼리가 실행되며, 이렇게 만들어진 테이블로 조인이 수행됩니다. 즉, 오른쪽 테이블은 각 서버에서 각각 구성됩니다.
GLOBAL ... JOIN을 사용하면 먼저 요청 서버가 서브쿼리를 실행해 조인의 한쪽을 계산하고, 그 결과를 임시 테이블에 수집합니다. 그런 다음 이 임시 테이블이 각 원격 서버로 전달되고, 전송된 임시 데이터를 사용해 각 서버에서 쿼리가 실행됩니다. LEFT 및 INNER 조인의 경우 오른쪽 테이블이 서브쿼리로 계산됩니다. RIGHT 조인의 경우 유지되는 쪽은 오른쪽 테이블이며 세그먼트에서 읽어야 하므로, 대신 왼쪽 테이블이 계산됩니다.

GLOBAL을 사용할 때는 주의하십시오. 자세한 내용은 분산 서브쿼리 섹션을 참조하십시오.

암시적 타입 변환

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN 쿼리는 “조인 키”에 대한 암시적 타입 변환을 지원합니다. 하지만 왼쪽 테이블과 오른쪽 테이블의 join 키를 하나의 타입으로 변환할 수 없으면 쿼리를 실행할 수 없습니다(예를 들어 UInt64와 Int64의 모든 값을 모두 표현할 수 있는 데이터 타입이 없거나, String과 Int32를 함께 표현할 수 있는 데이터 타입이 없는 경우). 예시 테이블 t_1을 살펴보겠습니다:

┌─a─┬─b─┬─toTypeName(a)─┬─toTypeName(b)─┐
│ 1 │ 1 │ UInt16        │ UInt8         │
│ 2 │ 2 │ UInt16        │ UInt8         │
└───┴───┴───────────────┴───────────────┘

그리고 t_2 테이블:

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│ -1 │    1 │ Int16         │ Nullable(Int64) │
│  1 │   -1 │ Int16         │ Nullable(Int64) │
│  1 │    1 │ Int16         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

쿼리

SELECT a, b, toTypeName(a), toTypeName(b) FROM t_1 FULL JOIN t_2 USING (a, b);

다음 집합을 반환합니다:

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│  1 │    1 │ Int32         │ Nullable(Int64) │
│  2 │    2 │ Int32         │ Nullable(Int64) │
│ -1 │    1 │ Int32         │ Nullable(Int64) │
│  1 │   -1 │ Int32         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

사용 권장 사항

비어 있거나 NULL인 셀 처리

테이블을 조인하는 동안 빈 셀이 나타날 수 있습니다. join_use_nulls 설정은 ClickHouse가 이러한 셀을 어떻게 채울지 결정합니다. JOIN 키가 널 허용 필드인 경우, 키 중 하나 이상이 NULL 값인 행은 조인되지 않습니다.

구문

USING에 지정된 컬럼은 두 서브쿼리에서 이름이 같아야 하며, 나머지 컬럼은 서로 다른 이름이어야 합니다. 별칭을 사용해 서브쿼리의 컬럼 이름을 변경할 수 있습니다. USING 절은 조인할 하나 이상의 컬럼을 지정하며, 이를 통해 해당 컬럼들이 서로 같은 값으로 매칭됩니다. 컬럼 목록은 괄호 없이 지정합니다. 더 복잡한 조인 조건은 지원되지 않습니다.

구문 제한 사항

단일 SELECT 쿼리에서 여러 JOIN 절을 사용하는 경우:

*로 모든 컬럼을 가져오는 것은 테이블을 조인한 경우에만 가능하며, 서브쿼리에는 사용할 수 없습니다.
PREWHERE 절은 사용할 수 없습니다.
USING 절은 사용할 수 없습니다.

ON, WHERE, GROUP BY 절:

ON, WHERE, GROUP BY 절에서는 임의의 표현식을 사용할 수 없지만, SELECT 절에서 표현식을 정의한 뒤 별칭을 통해 해당 절에서 사용할 수 있습니다.

성능

JOIN을 실행할 때는 쿼리의 다른 단계와의 관계에서 실행 순서가 최적화되지 않습니다. join(오른쪽 테이블을 검색하는 작업)은 WHERE 필터링보다 먼저, 그리고 집계보다 먼저 실행됩니다. 동일한 JOIN으로 쿼리를 실행할 때마다 결과가 캐시되지 않으므로 서브쿼리가 매번 다시 실행됩니다. 이를 방지하려면 항상 RAM에 상주하는, 조인용으로 미리 준비된 배열인 특수한 Join 테이블 엔진을 사용하십시오. 경우에 따라 JOIN 대신 IN을 사용하는 편이 더 효율적입니다. 차원 테이블(예: 광고 캠페인 이름과 같은 차원 속성을 담고 있는 비교적 작은 테이블)과 조인하기 위해 JOIN이 필요한 경우, 모든 쿼리마다 오른쪽 테이블에 다시 접근해야 하므로 JOIN이 그리 편리하지 않을 수 있습니다. 이런 경우에는 JOIN 대신 “딕셔너리” 기능을 사용하는 것이 좋습니다. 자세한 내용은 Dictionaries 섹션을 참조하십시오.

메모리 제한

기본적으로 ClickHouse는 해시 조인 알고리즘을 사용합니다. ClickHouse는 right_table을 가져와 이를 위한 해시 테이블을 RAM에 생성합니다. join_algorithm = 'auto'가 활성화되어 있으면, 메모리 활용량이 일정 임계값에 도달한 후 ClickHouse는 머지 조인 알고리즘으로 전환합니다. JOIN 알고리즘에 대한 설명은 join_algorithm 설정을 참조하십시오. JOIN 연산의 메모리 활용량을 제한해야 하는 경우 다음 설정을 사용하십시오.

max_rows_in_join — 해시 테이블의 행 수를 제한합니다.
max_bytes_in_join — 해시 테이블의 크기를 제한합니다.

이러한 제한 중 하나에 도달하면 ClickHouse는 join_overflow_mode 설정에 따라 동작합니다. 이 두 설정은 하드 캡이며 조인을 디스크로 스필시키지 않습니다. 따라서 이 값을 스필 임계값 이하로 설정하면 일반적으로 스필이 발생하기 전에 쿼리가 중단됩니다. 다만 다음 두 설정은 이 동작을 바꿉니다. enable_adaptive_memory_spill_scheduler는 아래의 임계값에 의해 스필이 가능해진 조인을 여전히 스필시킬 수 있으며, legacy_join_size_limits_trigger_spilling은 이 두 하드 캡을 다시 디스크 스필 트리거로 되돌립니다. 조인이 실패하는 대신 오른쪽 입력을 디스크로 스필하면서 계속 실행되도록 하려면 다음을 사용하십시오.

max_bytes_before_external_join — 절대적인 스필 임계값입니다.
max_bytes_ratio_before_external_join — 사용 가능한 메모리에 대한 비율로 지정하는 스필 임계값입니다.

이 설정들은 grace_hash를 포함한 모든 해시 기반 알고리즘에 적용되는 임계값 기반 스필 트리거입니다. 메모리 부하가 있을 때 enable_adaptive_memory_spill_scheduler는 이 설정들이 요구하는 시점보다 더 일찍 스필할 수 있지만, 이는 둘 중 하나가 0이 아닌 경우에만 해당합니다. 임계값이 전혀 없는 조인은 결코 스필하지 않기 때문입니다. 선택한 join_algorithm은 조인이 어떤 방식으로 스필하는지를 결정할 뿐이며 — grace_hash는 첫 번째 블록부터 오른쪽 테이블을 파티셔닝하고, hash와 parallel_hash는 이를 메모리에 모았다가 임계값을 넘으면 전환합니다 — 이 설정들의 적용 여부를 결정하지는 않습니다. 단 하나의 예외는 legacy_join_size_limits_trigger_spilling입니다. 이 설정이 켜져 있으면 독립형 grace_hash는 두 임계값을 무시하고 대신 두 하드 캡을 기준으로 스필합니다.

예시

예시:

SELECT
    CounterID,
    hits,
    visits
FROM
(
    SELECT
        CounterID,
        count() AS hits
    FROM test.hits
    GROUP BY CounterID
) ANY LEFT JOIN
(
    SELECT
        CounterID,
        sum(Sign) AS visits
    FROM test.visits
    GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10

┌─CounterID─┬───hits─┬─visits─┐
│   1143050 │ 523264 │  13665 │
│    731962 │ 475698 │ 102716 │
│    722545 │ 337212 │ 108187 │
│    722889 │ 252197 │  10547 │
│   2237260 │ 196036 │   9522 │
│  23057320 │ 147211 │   7689 │
│    722818 │  90109 │  17847 │
│     48221 │  85379 │   4652 │
│  19762435 │  77807 │   7026 │
│    722884 │  77492 │  11056 │
└───────────┴────────┴────────┘

SQL 참고

데이터 타입

엔진

함수

포맷

설정

시스템 테이블

데이터 레이크

지원되는 JOIN 유형

LATERAL JOIN

설정

`ON` 절의 조건

서로 다른 테이블의 컬럼에 대한 부등 조건 JOIN

임의의 ON 조건을 사용하는 JOIN

JOIN 키의 NULL 및 NaN 값

ASOF JOIN 사용

PASTE JOIN 사용법

분산 JOIN

암시적 타입 변환

사용 권장 사항

비어 있거나 NULL인 셀 처리

구문

구문 제한 사항

성능

메모리 제한

예시

​지원되는 JOIN 유형

​LATERAL JOIN

​설정

​ON 절의 조건

​서로 다른 테이블의 컬럼에 대한 부등 조건 JOIN

​임의의 ON 조건을 사용하는 JOIN

​JOIN 키의 NULL 및 NaN 값

​ASOF JOIN 사용

​PASTE JOIN 사용법

​분산 JOIN

​암시적 타입 변환

​사용 권장 사항

​비어 있거나 NULL인 셀 처리

​구문

​구문 제한 사항

​성능

​메모리 제한

​예시

​관련 콘텐츠

지원되는 JOIN 유형

LATERAL JOIN

설정

`ON` 절의 조건

서로 다른 테이블의 컬럼에 대한 부등 조건 JOIN

임의의 ON 조건을 사용하는 JOIN

JOIN 키의 NULL 및 NaN 값

ASOF JOIN 사용

PASTE JOIN 사용법

분산 JOIN

암시적 타입 변환

사용 권장 사항

비어 있거나 NULL인 셀 처리

구문

구문 제한 사항

성능

메모리 제한

예시

관련 콘텐츠