About data joins

You can create a dataset from data sources (tables) that are related by one or more common columns. To create a dataset, you need to combine these tables based on their common columns. The method used for combining the tables is called data joining. The result of the join is a virtual table that contains data from the tables that are parts of the join.

A join can be based on one or more common columns.

Example – Join based on two common columns

You have two data sources. Each of these data sources includes one column for the year and one column for the month name. If you need to join these two data sources on the date, you must join them by both the year and month columns.

There are four join types:

• Inner join
• Left join
• Right join
• Full outer join

Inner join

When using an inner join to combine two tables, the result is a table that contains only the rows with values in both tables.

Example – Inner join

You have the following tables.

Order ID Customer name 1 George Williams 2 John Smith 3 Mary Jones

Order ID Order amount 2 10.55 3 8.99 4 15.24

The result of the inner join is the following table.

Order ID	Customer name	Order amount
2	John Smith	10.55
3	Mary Jones	8.99

Left join

When using a left join to combine two tables, the result is a table that contains all the rows from the left table and corresponding values from the right table. If a row from the left table does not have values in the right table, then null values appear in the resulting table.

Example – Left join

You have the following tables.

Order ID Customer name 1 George Williams 2 John Smith 3 Mary Jones

Order ID Order amount 2 10.55 3 8.99 4 15.24

The result of the left join is the following table.

Order ID	Customer name	Order amount
1	George Williams
2	John Smith	10.55
3	Mary Jones	8.99

Right join

When using the right join to combine two tables, the result is a table that contains all the rows from the right table and corresponding values from the left table. If a row from the right table does not have values in the left table, then null values appear in the resulting table.

Example – Right join

You have the following tables.

Order ID Customer name 1 George Williams 2 John Smith 3 Mary Jones

Order ID Order amount 2 10.55 3 8.99 4 15.24

The result of the right join is the following table.

Order ID	Customer name	Order amount
2	John Smith	10.55
3	Mary Jones	8.99
4		15.24

Full outer join

When using a full outer join to combine two tables, the result is a table that contains all rows from both tables. If a row from any of the tables does not have values in the other table, then null values appear in the resulting table.

Example – Full outer join

You have the following tables.

Order ID Customer name 1 George Williams 2 John Smith 3 Mary Jones

Order ID Order amount 2 10.55 3 8.99 4 15.24

The result of the full outer join is the following table.

Order ID	Customer name	Order amount
1	George Williams
2	John Smith	10.55
3	Mary Jones	8.99
4		15.24