Table joiners

Table joiners combine data from multiple tables. With the join condition specified, related data from different tables can be obtained for complex queries. This section introduces the basic usage of table joiners in DolphinDB, improving data use efficiency.

Note: Tables cannot be joined on columns of array vectors (ARRAY VECTOR), tuples (ANY VECTOR), or BLOB values.

Starting from version 1.30.21:

  • When using join statements that are not compatible with ANSI SQL-92, ensure the tables to be joined have different names. For example: select * from wj(t1, t2, -5s:0s, <avg(price) as avg>, `sym`time);
  • When joining a table or the result of a SQL query (including nested joins), you can specify an alias for the table or SQL query result. You can also set aliases for dimension tables when joining. 
    t1= table(1 2 3 3 as id, 7.8 4.6 5.1 0.1 as value)
t2 = table(5 3 1 as id, 300 500 800 as qty);

select * from t1 a inner join t2 b on a.id = b.id
select * from t1 as a inner join t2 as b on a.id = b.id

select * from t1 a inner join (select * from t2 where id=3) b on a.id = b.id

In the aforementioned script:

  1. select * from t1 a inner join t2 b on a.id = b.id: Join tables t1 (alias: a) and t2 (alias: b) on the column "id" and return records with the same "id" value from both tables.

  2. select * from t1 as a inner join t2 as b on a.id = b.id: Same as the query above, except using the aliases a and b for t1 and t2.

  3. select * from t1 a inner join (select * from t2 where id=3) b on a.id = b.id: Select rows where "id" equals 3 from t2 and join the result with t1 (join condition: a.id = b.id).

Types of Table Joiners

inner join

Return only the records that have equivalent values for the matching columns. 

select * from leftTable inner join rightTable on leftTable.column = rightTable.column;

left join/left outer join

Return all records from the left table and the matched records from the right table. The result is NULL from the right table if there is no match.

select * from leftTable left join rightTable on leftTable.column = rightTable.column;

full join/full outer join

Return all records from both tables. The result is NULL if there is no match.

select * from leftTable full join rightTable on leftTable.column = rightTable.column;

equi join

Return only the records that have equivalent values for the matching columns. An equal sign (=) is normally used as a comparison operator.

select * from leftTable join rightTable on leftTable.column = rightTable.column;

cross join

Return the Cartesian product of two tables with no join condition.

select * from leftTable cross join rightTable;

asof join

asof join is a special table joiner used for joining time series data based on temporal proximity. The join condition is a time column.

aj(leftTable, rightTable, matchingCols, [rightMatchingCols])

window join

Join both tables based on a specified window and apply aggregate functions on the data in the window.

wj(leftTable, rightTable, window, aggs, matchingCols, [rightMatchingCols])

prefix join

Return the records in the left table whose joining column value starts with the joining column value in the right table. It is normally used for tables with similar column names.

pj(leftTable, rightTable, matchingCols, [rightMatchingCols])

For detailed usages of these table joiners, refer to sections in this chapter.