In implementing the table joins, you can choose to use the Table.Join() and Table.NestedJoin() functions
All six join types are supported by both functions: inner, left outer, right outer, full outer, left anti, right anti
Table.NestedJoin() enters the results of the join (the right or second table) into a new column of table values and will use local resources to execute the join operation, unless the Table.ExpandTableColumn() function is used to replace this new column with columns from the right table
A left outer join type is the default if the JoinKind parameter is not specified.
Table.Join() automatically expands the left table with the columns from the right table input (a flat join) and defaults to an inner join if the JoinKind parameter is not specified
This function will get folded to the source without any additional functions but requires that there are no matching column names between the joined tables for JoinKinds other than inner join
For inner joins, the matching column names from both tables must be specified in the join key parameters
An additional Table.SelectColumns() functions is required to exclude any columns from the right table added with the join
For performance reasons, Table.NestedJoin() should not be used without a Table.ExpandTableColumn() function removing the column of tables
Whether implemented via Table.NestedJoin() or Table.Join(), developers should look to use inner joins if the source tables have referential integrity such as with foreign key constraints and this meets requirements
For joins against larger tables, developers should confirm that query folding is occurring and can evaluate different query plans generated and performance by alternative retrieval designs