v.23.12New Feature

Introduce PASTE JOIN for Table Row Number Joining

Official CH changelog text
Introduce PASTE JOIN, which allows users to join tables without ON clause simply by row numbers. Example: 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. #57995 (Yarik Briukhovetskyi).
PASTE JOIN is a new join type in ClickHouse that enables joining tables by their row numbers without requiring an ON clause.

Why it matters

This feature solves the problem of performing joins purely based on the order of rows, eliminating the need for specifying join conditions. It simplifies and optimizes use cases where users want to combine tables side-by-side according to their row positions, improving query expressiveness and usability.

How to use it

Users can apply the PASTE JOIN by writing a query using the PASTE JOIN syntax between two table expressions. For example:

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

Related resources

PreviousNext