Any Inner join doesn't work to combine two tables using date-CodePudding

I use clickhouse. The following code doesn't work, although this type of writing the query works in other SQL databases.

    select * 
    from 
        ((select event_date, uniq(attraction_hash) 
          from table_1
          where event_date >= toDate('2021-07-13 00:00:00') - 30
            and event_date <= toDate('2021-10-13 00:00:00')
            and trans_type in ('initial', 'trial')
group by event_date)
    any inner join
        (select event_date, uniq(feid) 
         from table_2
         where event_date >= toDate('2021-07-13 00:00:00') - 30
           and event_date <= toDate('2021-10-13 00:00:00')
         group by event_date)
    using event_date)

The following error occurs:

Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 302 ('any') (line 7, col 1): any inner join (select event_date, uniq(feid) from table_1 WHERE event_date >= toDate('2021-07-13 00:00:00') - 30 AND eve. Expected UNION ALL (version 20.9.2.20 (official build))

Please help

CodePudding user response：

Try follow this way:

SELECT
    number,
    c,
    u
FROM
(
    SELECT
        number,
        count() AS c
    FROM numbers(100)
    WHERE number > 10
    GROUP BY number
) AS t1
ANY INNER JOIN
(
    SELECT
        number,
        uniq(number % 2) AS u
    FROM numbers(16)
    WHERE number > 10
    GROUP BY number
) AS t2 USING (number)

/*
┌─number─┬─c─┬─u─┐
│     14 │ 1 │ 1 │
│     15 │ 1 │ 1 │
│     12 │ 1 │ 1 │
│     11 │ 1 │ 1 │
│     13 │ 1 │ 1 │
└────────┴───┴───┘
*/