CODEWITHSUNDEEP
sqlclickhouse
mrkoloev
mrkoloev

Reputation: 17

Join tables in ClickHouse with between condition

I found out join in ClickHouse supports only equal expressions. But I need to join two big tables with 'between' condition in ClickHouse.

How to implement this logic?

select a.*, b.name
from a
join b
  on a.id = b.id
  and a.start_dt between b.start_dt and b.end_dt;

got error

Code: 403, e.displayText() = DB::Exception: Invalid expression for JOIN ON. Expected equals expression...

Upvotes: 0

Views: 5348

Answers (1)

vladimir
vladimir

Reputation: 15218

Try this one:

select a.*, b_name
from (
  select a.*, b.name AS b_name, b.start_dt AS b_start_dt, b.end_dt AS b_end_dt
  from a join b using id
  where a.start_dt between b_start_dt and b_end_dt
  )

Look at some JOIN specifics in Clickhouse join with condition.

Upvotes: 2

Related Questions