QuestDB supports the following types of joins:
FULL joins are not yet implemented and are on our roadmap. All
supported join types can be combined in a single SQL statement; QuestDB SQL's
optimiser determines the best execution order and algorithms.
There are no known limitations on size of tables or sub-queries participating in joins and there are no limitations on number of joins either.
Following data join columns from joined tables are combined in single row. Same name columns originating from different tables will be automatically aliased to create unique column namespace of the result set.
Though it is a best practice to diligently specify join conditions, QuestDB will
WHERE clause for implicit join condition and will derive
transient join conditions where necessary.
When tables are joined on column that has the same name in both tables you can
ON (column) clause
Join operations are performed in order of their appearance in a SQL query. The following query performs a join on a table with one million rows based on a column from a smaller table with one hundred rows:
The performance of this query can be improved by rewriting the query as follows:
It is possible to join two tables using the following syntax:
The type of join as well as the column will be inferred from the where clause,
and may end up being either
CROSS join. For the example above, the
equivalent explicit statement would be:
(INNER) JOIN is used to return rows from 2 tables where the records on the
compared column have matching values in both tables
The following query will return the movieId and the average rating from table
ratings. It will also add a column for the
title from table
corresponding title will be identified based on the
movieId in the
table matching an
id in the
JOIN is interpreted as
INNER JOIN. Therefore
INNER does not
need to be specified.
OUTER JOIN will return all records from the LEFT table, and if matched,
the records of the RIGHT table. When there is no match for the RIGHT table, it
NULL values in right table fields
General syntax is as follows:
OUTER JOIN query can also be used to select all rows in left table that do not
exist in right table.
CROSS JOIN will return the cartesian product of the two tables being joined.
It can be used to a table with all possible combinations.
CROSS JOIN does not have
The following will return all possible combinations of starters and deserts
ASOF joins are used on time series data to join two tables based on timestamp
where timestamps do not exactly match. For a given record at a given timestamp,
it will return the corresponding record in the other table at the closest
timestamp prior to the timestamp in the first table.
To be able to leverage
ASOF JOIN, both joined table must have a designated
timestamp column. To designate a column as
timestamp, please refer to the
CREATE TABLE section.
ASOF join is performed on tables or result sets that are ordered by time. When
table is created as ordered by time order of records is enforced and timestamp
column name is in table metadata.
ASOF join will use timestamp column from
Consider the following tables.
Therefore the following query:
Will return the following:
There is no
ASKS at timestamp
will look for the value in the
BIDS table that has the closest timestamp
inferior or equal to the target timestamp.
In case tables do not have designated timestamp column, but data is in chronological order, timestamp columns can be specified at runtime:
ASOF join does not check timestamp order, if data is not in chronological
order join result is non-deterministic
Above query assumes that there is only one instrument in
tables and therefore does not use the optional
If both tables store data for multiple instruments
ON clause will allow you to
find bids for asks with matching instrument value.
SPLICE JOIN is a full
ASOF JOIN. It will return all the records from both
tables. For each record from left table splice join will find prevailing record
from right table and for each record from right table - prevailing record from
Considering the following tables.
Will return the following results
Note that the above query does not use the optional
ON clause. In case you
need additional filtering on the two tables, you can use the
ON clause as