ClickHouse는 표준 SQL 조인을 완벽하게 지원하여 효율적인 데이터 분석이 가능합니다.
이 가이드에서는 벤 다이어그램과 relational dataset repository에서 가져온 정규화된 IMDB 데이터셋에 대한 예시 쿼리를 통해, 일반적으로 자주 사용되는 조인 유형 몇 가지와 그 사용 방법을 살펴봅니다.
테이블을 생성하고 로드하는 방법은 여기에서 확인할 수 있습니다.
로컬에서 테이블을 생성하고 로드하지 않으려면 Playground에서도 데이터셋을 사용할 수
있습니다.
다음 4개의 테이블을 예시 데이터셋에서 사용합니다:
이 4개 테이블의 데이터는 하나 이상의 장르를 가질 수 있는 영화를 나타냅니다.
영화 속 배역은 배우가 연기합니다.
위 다이어그램의 화살표는 외래 키와 프라이머리 키 간의 관계를 나타냅니다. 예를 들어 genres 테이블의 한 행에 있는 movie_id 컬럼에는 movies 테이블의 한 행에 있는 id 값이 들어 있습니다.
영화와 배우 사이에는 다대다 관계가 있습니다.
이 다대다 관계는 roles 테이블을 사용해 두 개의 일대다 관계로 정규화됩니다.
roles 테이블의 각 행에는 movies 테이블과 actors 테이블의 id 컬럼 값이 들어 있습니다。
ClickHouse에서 지원하는 JOIN 유형
INNER JOIN은 조인 키(join keys)가 일치하는 각 행 쌍에 대해 왼쪽 테이블 행의 컬럼 값과 오른쪽 테이블 행의 컬럼 값을 결합해 반환합니다.
한 행에 일치하는 항목이 둘 이상 있으면 해당하는 모든 항목이 반환됩니다(즉, 조인 키가 일치하는 행에 대해서는 카테시안 곱이 생성됩니다).
이 쿼리는 movies 테이블과 genres 테이블을 조인하여 각 영화의 장르를 찾습니다:
다음의 다른 JOIN 유형 중 하나를 사용하면 INNER JOIN의 동작을 확장하거나 변경할 수 있습니다.
(LEFT / RIGHT / FULL) OUTER JOIN
LEFT OUTER JOIN은 INNER JOIN처럼 동작하지만, 여기에 더해 왼쪽 테이블에서 일치하는 행이 없는 경우 ClickHouse는 오른쪽 테이블의 컬럼에 기본값을 반환합니다.
RIGHT OUTER JOIN 쿼리도 비슷하며, 오른쪽 테이블에서 일치하는 행이 없는 값과 함께 왼쪽 테이블 컬럼의 기본값도 반환합니다.
FULL OUTER JOIN 쿼리는 LEFT와 RIGHT OUTER JOIN을 결합한 것으로, 왼쪽 및 오른쪽 테이블에서 일치하는 행이 없는 값을 각각 반대쪽 테이블 컬럼의 기본값과 함께 반환합니다.
ClickHouse는 기본값 대신 NULL을 반환하도록 구성할 수 있습니다(다만 성능상의 이유로 권장되지 않습니다). genres 테이블에 일치하는 항목이 없는 movies 테이블의 모든 행을 조회하여 장르가 없는 모든 영화를 찾습니다. 따라서 movie_id 컬럼에는 쿼리 시점에 기본값 0이 적용됩니다:
CROSS JOIN은 조인 키를 고려하지 않고 두 테이블의 전체 카테시안 곱을 생성합니다.
왼쪽 테이블의 각 행은 오른쪽 테이블의 각 행 각각과 결합됩니다.
따라서 다음 쿼리는 movies 테이블의 각 행을 genres 테이블의 각 행과 결합합니다:
이전의 예시 쿼리만으로는 큰 의미가 없지만, 각 영화의 장르를 찾기 위해 일치하는 행을 연결해 INNER JOIN 동작을 재현할 수 있도록 WHERE 절을 추가해 확장할 수 있습니다:
CROSS JOIN의 대체 구문에서는 FROM 절에 여러 테이블을 쉼표로 구분해 지정합니다.
ClickHouse는 쿼리의 WHERE 절에 조인 표현식이 있으면 CROSS JOIN을 INNER JOIN으로 재작성합니다.
예시 쿼리의 경우 EXPLAIN SYNTAX를 통해 이를 확인할 수 있습니다(이 구문은 쿼리가 실행되기 전에 재작성되는 구문적으로 최적화된 버전을 반환합니다):
구문적으로 최적화된 CROSS JOIN 쿼리 버전의 INNER JOIN 절에는 ALL 키워드가 포함됩니다. 이는 INNER JOIN으로 다시 작성되더라도 CROSS JOIN의 카테시안 곱 의미를 유지하기 위해 명시적으로 추가된 것입니다. INNER JOIN에서는 카테시안 곱을 비활성화할 수 있습니다.
또한 앞서 언급했듯이 RIGHT OUTER JOIN에서는 OUTER 키워드를 생략할 수 있고, 선택 사항인 ALL 키워드를 추가할 수도 있으므로 ALL RIGHT JOIN이라고 작성해도 문제없이 동작합니다.
LEFT SEMI JOIN 쿼리는 오른쪽 테이블에서 조인 키가 하나 이상 일치하는 왼쪽 테이블의 각 행에 대해 컬럼 값을 반환합니다.
첫 번째로 찾은 일치 항목만 반환됩니다(카테시안 곱은 비활성화됨).
RIGHT SEMI JOIN 쿼리도 이와 비슷하며, 왼쪽 테이블에서 하나 이상 일치하는 오른쪽 테이블의 모든 행에 대한 값을 반환하지만, 첫 번째로 찾은 일치 항목만 반환됩니다.
이 쿼리는 2023년에 영화에 출연한 모든 배우를 찾습니다.
일반적인 (INNER) 조인을 사용하면, 2023년에 역할이 2개 이상인 동일한 배우가 여러 번 표시될 수 있다는 점에 유의하십시오:
LEFT ANTI JOIN은 왼쪽 테이블에서 일치하는 항목이 없는 모든 행의 컬럼 값을 반환합니다.
마찬가지로 RIGHT ANTI JOIN은 오른쪽 테이블에서 일치하는 항목이 없는 모든 행의 컬럼 값을 반환합니다.
앞의 외부 조인 예시 쿼리는 anti join을 사용해 데이터셋에 장르가 없는 영화를 찾는 방식으로도 표현할 수 있습니다:
(LEFT / RIGHT / INNER) ANY JOIN
LEFT ANY JOIN은 LEFT OUTER JOIN과 LEFT SEMI JOIN을 결합한 형태입니다. 즉, ClickHouse는 왼쪽 테이블의 각 행에 대해 오른쪽 테이블에서 일치하는 행이 있으면 해당 행의 컬럼 값과 결합된 결과를 반환하고, 일치하는 행이 없으면 오른쪽 테이블의 기본 컬럼 값과 결합된 결과를 반환합니다.
왼쪽 테이블의 한 행이 오른쪽 테이블에서 둘 이상의 행과 일치하는 경우, ClickHouse는 처음 찾은 일치 항목과 결합된 컬럼 값만 반환합니다(카테시안 곱은 비활성화됨).
마찬가지로 RIGHT ANY JOIN은 RIGHT OUTER JOIN과 RIGHT SEMI JOIN을 결합한 형태입니다.
또한 INNER ANY JOIN은 카테시안 곱이 비활성화된 INNER JOIN입니다.
다음 예시는 values 테이블 함수로 생성한 두 개의 임시 테이블(left_table 및 right_table)을 사용해 LEFT ANY JOIN을 추상적인 예로 보여줍니다:
다음은 RIGHT ANY JOIN을 사용한 동일한 쿼리입니다:
INNER ANY JOIN을 사용한 쿼리는 다음과 같습니다:
ASOF JOIN은 정확히 일치하지 않는 값도 매칭할 수 있습니다.
왼쪽 테이블의 행에 대해 오른쪽 테이블에서 정확히 일치하는 값이 없으면, 대신 오른쪽 테이블에서 가장 가까운 행을 매칭에 사용합니다.
이는 특히 시계열(time-series) 분석에 유용하며, 쿼리 복잡도를 크게 줄일 수 있습니다.
다음 예시는 주식 시장 데이터에 대해 시계열 분석을 수행합니다.
quotes 테이블에는 하루 중 특정 시각의 주식 심볼 시세가 저장됩니다.
예시 데이터에서는 가격이 10초마다 갱신됩니다.
trades 테이블에는 심볼 거래 내역이 있으며, 특정 시각에 특정 수량의 심볼이 매수됩니다:
각 거래의 실제 비용을 계산하려면 각 거래를 가장 가까운 시세 시각과 매칭해야 합니다.
이 작업은 ASOF JOIN을 사용하면 쉽고 간결하게 처리할 수 있습니다. ON 절은 정확히 일치하는 조건을 지정하고, AND 절은 가장 가까운 값을 찾는 조건을 지정합니다. 즉, 특정 심볼(정확 일치)에 대해 해당 심볼의 거래 시각과 같거나 그보다 이전인 시각(비정확 일치)을 가진 quotes 테이블의 행 중에서 시간이 가장 가까운 행을 찾습니다:
ASOF JOIN의 ON 절은 필수이며, AND 절의 비정확 일치 조건에 더해 정확 일치 조건을 지정합니다.
