# equi join {#equi-join}

**Parent topic:**[Table joiners](../../../Programming/SQLStatements/TableJoiners/TableJoiners.md)

## Syntax {#syntax}

-   *equi join \(ej\)*

    ``` {#codeblock_lxx_5dl_41c}
    ej(leftTable, rightTable, matchingCols, [rightMatchingCols], [leftFilter], [rightFilter])
    ```

    SQL-compatible syntax

    ``` {#codeblock_msk_h2l_41c}
    select * 
    from leftTable 
    [inner] join rightTable on t1.matchingCol=t2.rightMatchingCol 
    [where leftFilter and rightFilter]
    ```

-   *semantically equi join \(sej\)*

    ``` {#codeblock_yl4_32l_41c}
    sej(leftTable, rightTable, matchingCols, [rightMatchingCols], [leftFilter], [rightFilter])
    ```

    SQL-compatible syntax

    ``` {#codeblock_ydj_j2l_41c}
    select * 
    from leftTable 
    [inner] join rightTable on leftTable.matchingCol=rightTable.rightMatchingCol 
    [where leftFilter and rightFilter] 
    order by matchingCol 
    ```


## Arguments {#arguments}

**leftTable** and **rightTable** are the tables to be joined.

**matchingCols** a string scalar/vector indicating matching columns.

**rightMatchingCols** a string scalar/vector indicating all the matching columns in *rightTable* . This optional argument must be specified if at least one of the matching columns has different names in *leftTable* and *rightTable* . The joining column names in the result will be the joining column names from the left table.

**leftFilter** and **rightFilter** are condition expressions used as filter conditions for the columns in the left and right tables. Use "and" or "or" to join multiple conditions.

Note: If parameter *leftTable* / *rightTable* is specified as a dimension table or partitioned table, parameters *leftFilter* and *rightFilter* must not be specified.

## Details {#details}

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

## Examples {#examples}

Example 1. Equi join two tables with no common column names except the joining column\(s\)

```
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);
t1;
```

|id|value|
|---|-----|
|1|7.8|
|2|4.6|
|3|5.1|
|3|0.1|

```
t2;
```

|id|qty|
|---|---|
|5|300|
|3|500|
|1|800|

``` {#codeblock_gwq_t2l_41c}
ej(t1, t2,`id);
```

Or

```
select * from t1 inner join t2 on t1.id=t2.id 
```

|id|value|qty|
|---|-----|---|
|1|7.8|800|
|3|5.1|500|
|3|0.1|500|

``` {#codeblock_ewh_y2l_41c}
 ej(t2, t1,`id);
```

Or

```
select * from t2 inner join t1 on t2.id=t1.id
```

|id|value|qty|
|---|-----|---|
|3|5.1|500|
|3|0.1|500|
|1|7.8|800|

``` {#codeblock_ip1_1fl_41c}
ej(t1, t2,`id,, t1.id==3);
```

Or

```
select * from t1 inner join t2 on t1.id=t2.id  where t1.id=3
```

|id|value|qty|
|---|-----|---|
|3|5.1|500|
|3|0.1|500|

Example 2. Equi join two tables with common variables that are not the joining column\(s\)

```
t1 = table(1 2 3 3 as id, 7.8 4.6 5.1 0.1 as value, 4 3 2 1 as x);
t1;
```

|id|value|x|
|---|-----|---|
|1|7.8|4|
|2|4.6|3|
|3|5.1|2|
|3|0.1|1|

```
t2 = table(5 3 1 as id,  300 500 800 as qty, 44 66 88 as x) ;
t2;
```

|id|qty|x|
|---|---|---|
|5|300|44|
|3|500|66|
|1|800|88|

``` {#codeblock_lfn_cfl_41c}
select id, value, qty, x from ej(t1, t2, `id);
```

Or

```
select id, value, qty, x from t1 inner join t2 on t1.id=t2.id
```

|id|value|qty|x|
|---|-----|---|---|
|1|7.8|800|4|
|3|5.1|500|2|
|3|0.1|500|1|

Note that we don't need to specify which table the columns "value" and "qty" come from. The system tries to locate a column in the left table. If it is not in the left table, then the system will locate it in the right table.

``` {#codeblock_e1z_dfl_41c}
select id, value, qty, t2.x from ej(t1, t2, `id);
```

Or

```
select id, value, qty, t2.x from t1 inner join t2 on t1.id=t2.id
```

|id|value|qty|x|
|---|-----|---|---|
|1|7.8|800|88|
|3|5.1|500|66|
|3|0.1|500|66|

```
ej(t1, t2, `id);
```

Or

``` {#codeblock_fpg_ffl_41c}
select * from t1 inner join t2 on t1.id=t2.id
```

|id|value|x|qty|t2\_x|
|---|-----|---|---|-----|
|1|7.8|4|800|88|
|3|5.1|2|500|66|
|3|0.1|1|500|66|

Note here ej selects x from both t1 and t2, and renames x from t2 as t2\_x.

Example 3. Multiple joining columns

```
t1=table(1 1 2 2 3 3 as x, 1 2 2 3 3 4 as y, 1..6 as a);
t2=table(0 1 1 2 2 3 as x, 1 2 3 3 4 5 as y, 11..16 as b);
t1;
```

|x|y|a|
|---|---|---|
|1|1|1|
|1|2|2|
|2|2|3|
|2|3|4|
|3|3|5|
|3|4|6|

```
t2;
```

|x|y|b|
|---|---|---|
|0|1|11|
|1|2|12|
|1|3|13|
|2|3|14|
|2|4|15|
|3|5|16|

```
ej(t1, t2, `x`y);
```

Or

``` {#codeblock_rjy_hfl_41c}
select * from t1 inner join t2 on t1.x=t2.x and t1.y=t2.y
```

|x|y|a|b|
|---|---|---|---|
|1|2|2|12|
|2|3|4|14|

```
t2.rename!(`x`y, `x2`y2);
```

|x2|y2|b|
|---|---|---|
|0|1|11|
|1|2|12|
|1|3|13|
|2|3|14|
|2|4|15|
|3|5|16|

```
ej(t1, t2, `x`y, `x2`y2);
```

Or

``` {#codeblock_wnj_jfl_41c}
select * from t1 inner join t2 on t1.x=t2.x2 and t1.y=t2.y2 
```

|x|y|a|b|
|---|---|---|---|
|1|2|2|12|
|2|3|4|14|

Example 4. table aliases in table joins

```
table1=table(1 1 2 2 3 3 as x, 1 2 2 3 3 4 as y, 1..6 as a, 21..26 as c)
table2=table(0 1 1 2 2 3 as x, 1 2 3 3 4 5 as y, 4..9 as a, 11..16 as b);
select * from ej(table1 as t1, table2 as t2, `x`y) where t2.a<7;
```

|x|y|a|c|t2\_a|b|
|---|---|---|---|-----|---|
|1|2|2|22|5|12|

We must use table aliases in a self join:

```
t = table(`A`A`A`A`B`B`B`B as id, 1 3 6 9 1 9 12 17 as time, 1 2 6 3 5 9 4 0 as x)
select * from ej(t as a, t as b, `id) where a.time=b.time+3;
```

Or

``` {#codeblock_w2m_kfl_41c}
select * from t as a inner join t as b on a.id=b.id where a.time=b.time+3
```

|id|time|x|b\_time|b\_x|
|---|----|---|-------|----|
|A|6|6|3|2|
|A|9|3|6|6|
|B|12|4|9|9|

Example 5. Specify filter conditions

```
t1= table(1 2 3 3 as id1, 7.8 4.6 5.1 0.1 as value)
t2 = table(5 3 1 as id2, 300 500 800 as qty);
select * from ej(t1, t2, `id1, `id2, t1.value>1 and t1.value<6, t2.qty>300)
```

Or

``` {#codeblock_eng_lfl_41c}
select * from t1 inner join t2 on t1.id1=t2.id2 where t1.value>1 and t1.value<6 and t2.qty>300
```

|id1|value|qty|
|---|-----|---|
|3|5.1|500|

