# inner join {#inner-join}

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

## Syntax {#syntax}

```
select column_name(s) from leftTable inner join rightTable on leftTable.matchingCol=rightTable.rightMatchingCol
```

or

```
select column_name(s)
from table1 inner join table2
on table1.column_name=table2.column_name and [filter]
```

## Arguments {#arguments}

**filter** is condition expression\(s\) used as filter condition\(s\) for the join. Use "and" or "or" to join multiple conditions.

## Details {#details}

Return only the rows that have equivalent values for the matching columns. This function returns the same result as [equi join](equijoin.md).

Note:

1.  You can use `and` to combine multiple conditions for a table join.

2.  It cannot be used with the keyword `update`.

3.  If the left table is not a DFS table, its right table cannot be a DFS table either.


## Examples {#examples}

Example 1. Inner join two tables with no common column names except the matching 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);
select id, value, qty 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|

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

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

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

```
t3 = table(1 2 3 3 as id, 7.8 4.6 5.1 0.1 as value, 64 73 52 66 as x);
t4 = table(5 3 1 as id,  300 500 800 as qty, 44 66 88 as x) ;
select id, value, qty, x from t3 inner join t4 on t3.id=t4.id
```

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

If you do not specify which table the columns "value" and "qty" come from, the system will first 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.

```
select id, value, qty, t4.x from t3 inner join t4 on t3.id=t4.id
```

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

Example 3. Multiple join columns:

```
select id, value, qty, x from t3 inner join t4 on t3.id=t4.id and t3.x=t4.x
```

|id|value|qty|x|
|---|-----|---|---|
|3|0.1|500|66|

Example 4. Inner join DFS tables:

```
dbName1="dfs://sql_inner_join"
if(existsDatabase(dbName1)){
  dropDatabase(dbName1)
}
db1=database(dbName1, RANGE, 1 30 70 101)
t1=table("A"+string(1..100) as sym, 1..100 as val)
pt1=db1.createPartitionedTable(t1, `pt1, `val).append!(t1)
t2=table("A"+string(1..20) as sym, 1..20 as val)
pt2=db1.createPartitionedTable(t2, `pt2, `val).append!(t2)

select * from pt1 inner join pt2 on pt1.val=pt2.val
```

|sym|val|pt2\_sym|
|---|---|--------|
|A1|1|A1|
|A2|2|A2|
|A3|3|A3|
|A4|4|A4|
|A5|5|A5|
|A6|6|A6|
|A7|7|A7|
|A8|8|A8|
|A9|9|A9|
|A10|10|A10|
|A11|11|A11|
|A12|12|A12|
|A13|13|A13|
|A14|14|A14|
|A15|15|A15|
|A16|16|A16|
|A17|17|A17|
|A18|18|A18|
|A19|19|A19|
|A20|20|A20|

Example 5. Specify filter conditions

```
t1= table(1 2 3 3 6 8 as id, 7.8 4.6 5.1 0.1 0.5 1.2 as value)
t2 = table(5 3 1 2 6 8 as id, 300 500 800 400 600 700 as qty);
select * from t1 inner join t2 on t1.id=t2.id and t1.id>=3
```

|id|value|qty|
|---|-----|---|
|3|5.1|500|
|3|0.1|500|
|6|0.5|600|
|8|1.2|700|

