# Table joiners {#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.
-   When performing a table join, all participating tables or subquery results must have unique names or aliases; otherwise, an error will be raised.

Starting from version 1.30.212.00.9:

-   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`\).


-   **[asof join](../../../Programming/SQLStatements/TableJoiners/asofjoin.md)**  

-   **[cross join](../../../Programming/SQLStatements/TableJoiners/crossjoin.md)**  

-   **[equi join](../../../Programming/SQLStatements/TableJoiners/equijoin.md)**  

-   **[full join/full outer join](../../../Programming/SQLStatements/TableJoiners/fulljoin.md)**  

-   **[inner join](../../../Programming/SQLStatements/TableJoiners/innerjoin.md)**  

-   **[JOIN](../../../Programming/SQLStatements/TableJoiners/join.md)**  

-   **[left join/left outer join](../../../Programming/SQLStatements/TableJoiners/leftjoin.md)**  

-   **[prefix join](../../../Programming/SQLStatements/TableJoiners/prefixjoin.md)**  

-   **[right join/right outer join](../../../Programming/SQLStatements/TableJoiners/rightjoin.md)**  

-   **[window join](../../../Programming/SQLStatements/TableJoiners/windowjoin.md)**  


**Parent topic:**[SQL Reference](../../../Programming/SQLStatements/SQLStatement.md)

## Types of Table Joiners {#topic_d5v_3xh_n1c}

**inner join**

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

``` {#codeblock_olx_pxh_n1c}
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.

``` {#codeblock_plx_pxh_n1c}
select * from leftTable left join rightTable on leftTable.column = rightTable.column;
```

**right join/right outer join**

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

``` {#codeblock_qlx_pxh_n1c}
select * from leftTable right 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.

``` {#codeblock_rlx_pxh_n1c}
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.

``` {#codeblock_slx_pxh_n1c}
select * from leftTable join rightTable on leftTable.column = rightTable.column;
```

**cross join**

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

``` {#codeblock_tlx_pxh_n1c}
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.

``` {#codeblock_ulx_pxh_n1c}
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.

``` {#codeblock_vlx_pxh_n1c}
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.

``` {#codeblock_wlx_pxh_n1c}
pj(leftTable, rightTable, matchingCols, [rightMatchingCols])
```

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

