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 optimizer determines the best execution order and algorithms.
There are no known limitations on the size of tables or sub-queries used in joins and there are no limitations on the number of joins, either.
selectClause- see SELECT for more information.
whereClause- see WHERE for more information.
The specific syntax for
joinClausedepends on the type of
operator, in the mandatory
JOINpredicate in the optional
CROSS JOINdoes not allow the
Columns from joined tables are combined in a single row. Columns with the same name originating from different tables will be automatically aliased to create a unique column namespace of the resulting set.
Though it is usually preferable to explicitly specify join conditions, QuestDB
WHERE clauses for implicit join conditions and will derive
transient join conditions where necessary.
When tables are joined on a column that has the same name in both tables you can
ON (column) shorthand.
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
and may be either an
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.
JOIN is interpreted as
INNER JOIN by default, making the
INNER keyword implicit.
The following query will return the
movieId and the average rating from table
ratings. It will also add a column for the
title from the table
The corresponding title will be identified based on the
movieId in the
ratings table matching an
id in the
LEFT OUTER JOIN or simply
LEFT 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 will return
NULL values in right table fields.
The general syntax is as follows:
LEFT OUTER JOIN query can also be used to select all rows in the left table
that do not exist in the right table.
CROSS JOIN will return the Cartesian product of the two tables being joined
and can be used to create a table with all possible combinations of columns. The
following query will return all possible combinations of
CROSS JOIN does not have an
ASOF joins are used on time series data to join two tables based on timestamps
that 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 tables must have a
designated timestamp column.
ASOF join is performed on tables or result sets that are ordered by time. When
a table is created as ordered by time, the order of records is enforced and the
timestamp column name is in the table metadata.
ASOF join will use this
timestamp column from metadata.
Given the following tables:
ASOF JOIN query can look like the following:
The above query returns these results:
Note that there are no records from the
asks table at timestamp
ASOF JOIN will look for the value in the
bids table that has the closest timestamp prior to or equal to the target
In case tables do not have a designated timestamp column, but data is in chronological order, timestamp columns can be specified at runtime:
The query above assumes that there is only one instrument in
tables and therefore does not use the optional
ON clause. If both tables store
data for multiple instruments
ON clause will allow you to find bids for asks
with matching instrument value:
ASOF join does not check timestamp order, if data is not in chronological
order, the join result is non-deterministic.
LT join is very similar to
ASOF, except that it searches for the last row
from the right table strictly before the row from the left table. There will be
one or no rows joined from the right table per each row from the left table.
Consider the following tables:
LT JOIN can be built using the following query:
The query above returns the following results:
LT join is often useful to join a table to itself in order to get preceding
values for every row.
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:
SPLICE JOIN can be built as follows:
This query returns 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, the
ON clause can be used as