Join types

Requirements

  • Β Joins and lookups in workbooks and datasets must use sources from the same connection.Β 

Lookup join

Lookup returns all of the rows in the current data and data from the matching rows in the joined data while maintaining the number of rows in the current data. If Sigma finds more than one match for the current data in the joined data, it returns a * in the corresponding row. Lookup functions like a VLOOKUP Excel function.

See Lookup join.

Lookup Venn diagram

Inner join

The inner join returns the rows that exist in both the current data and the joined data. Removes all rows that do not have data in both the current data and joined data.

See Inner join.

Inner join Venn diagram

Left outer join

The left outer join returns all of the rows in the current data, and all the data from the matching rows in the joined data.

It adds rows when there is more than one match. This can result in an expanded row count.

See Left outer join.

Left outer join Venn diagram

R‍ight outer join

The right outer join returns all of the rows from the joined data, and the data from the matching rows in the current data.

It adds rows when there is more than one match. When the current data has more than one row that matches to joined data, all of the matches rows are retained.

See Right outer join.

Right outer join Venn diagram

Full outer join

A full outer join returns all of the rows from the current data and all of the rows in the joined data, making matches where possible.

See Full outer join.

Full outer join Venn diagram