When deciding whether to use a right outer join or a left outer join, which factor matters the most?

8.2.1.8 Outer Join Optimization

Outer joins include LEFT JOIN and RIGHT JOIN.

MySQL implements an A LEFT JOIN B join_specification as follows:

• Table B is set to depend on table A and all tables on which A depends.

• Table A is set to depend on all tables (except B) that are used in the LEFT JOIN condition.

• The LEFT JOIN condition is used to decide how to retrieve rows from table B. (In other words, any condition in the WHERE clause is not used.)

• All standard join optimizations are performed, with the exception that a table is always read after all tables on which it depends. If there is a circular dependency, an error occurs.

• All standard WHERE optimizations are performed.

• If there is a row in A that matches the WHERE clause, but there is no row in B that matches the ON condition, an extra B row is generated with all columns set to NULL.

• If you use LEFT JOIN to find rows that do not exist in some table and you have the following test: col_name IS NULL in the WHERE part, where col_name is a column that is declared as NOT NULL, MySQL stops searching for more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN condition.

The RIGHT JOIN implementation is analogous to that of LEFT JOIN with the table roles reversed. Right joins are converted to equivalent left joins, as described in Section 8.2.1.9, “Outer Join Simplification”.

For a LEFT JOIN, if the WHERE condition is always false for the generated NULL row, the LEFT JOIN is changed to an inner join. For example, the WHERE clause would be false in the following query if t2.column1 were NULL:

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

Therefore, it is safe to convert the query to an inner join:

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

Now the optimizer can use table t2 before table t1 if doing so would result in a better query plan. To provide a hint about the table join order, use STRAIGHT_JOIN; see Section 13.2.9, “SELECT Statement”. However, STRAIGHT_JOIN may prevent indexes from being used because it disables semijoin transformations; see Section 8.2.2.1, “Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations”.

One Tool Example

Join has a One Tool Example. Visit Sample Workflows to learn how to access this and many other examples directly in Alteryx Designer.

Use Join to combine 2 inputs based on common fields between the 2 tables. You can also Join 2 data streams based on record position.

Configure the Tool

1. Select how to perform the Join. The 2 options are: by record position or by a specific field (column).
   • Join by Record Position: Select this option when the two tables to be joined have the same field structure, and the data will be joined by its position within the two tables.
   • Join by Specific Field: Select this option when the two tables have one or more fields in common (like an ID) and the data will be joined together. You can choose to Join based on multiple fields. Each Join should be a separate row in the grid.

   Potential Error Message

   The Join tool restricts what field types can be joined together. The error messages might result stating Joins on Double or Float are not recommended due to a rounding error that might occur.

   • String fields can only be joined to other string fields.
   • Numeric fields can only be joined to other numeric fields.
   • Boolean fields can only be joined to other boolean fields.
   • DateTime field types can only be joined to their exact type.
   • Spatial fields cannot be joined, use the Spatial Match tool instead.
   • Blob fields cannot be joined to any other type. 

2. Each Input (Left and Right) has a dropdown list where you can select fields (columns). Select the join field for each input. Alteryx Designer automatically selects a join field from an input if the same field name was already selected from a different input. If you need multiple join fields, you can configure an additional row of join fields.
   • Select the dropdown to choose an additional join field, per input.
   • To delete a join field, select a number on the left-hand side and select the Delete button.
3. Use the table to modify the incoming data stream. Each row in the table represents a column in the data (see below for more instructions).

Select, Deselect, and Reorder Columns

• To include a column in data, select the check box. Deselect the check box to exclude the column.
• To reorder the columns of data:
  1. Select to highlight a row or right-click and drag to highlight multiple rows.
  2. Use the up arrow or down arrow, or select and drag to move the rows to a new location.

The Unknown column is selected by default. It allows new columns in the data. Move the column to the location where you want a new column to be.

Modify Data Type and Size

To change the supported length (characters for string and numeric fixed decimal types) or measurement (bytes for other numeric types) of data in a column, select Size and enter a number. Size varies by data type and can be edited for fixed decimal, numeric types, and all string types.

Use the [data type]: Forced option to ensure a column always contains the expected data type. This is helpful when creating macros.

Rename a Column or Add a Description

• To change the name of a column, select the Rename field associated with the column and enter the new name.
• To add a description, select Description and enter a description.

View More Options

After you select or highlight rows (columns of data) in the table, select Options to view more configuration options:

• Save/Load: Save Field Configuration as a .yxft file. The Alteryx Field Type file is a text file that can be used in other workflows using the Load Field Names or Load File Names and Types options.
• Select: Select or deselect all, or highlight columns. Options include Select All and Deselect All.
• Change Field Type of Highlighted Fields: Change the data type of all highlighted columns at once.
• Sort: Sort the column order in ascending or descending order. Options include Sort on Original Field Name, Sort on New Field Name, Sort on Field Type, and Revert to Incoming Field Order.
• Move: Move highlighted columns to the top or bottom of the list.
• Add Prefix to Field Names: Add a prefix to the selected or highlighted column name.
• Add Suffix to Field Names: Add a suffix to the selected or highlighted column name.
• Remove Prefix or Suffix: Remove the prefix or suffix from the selected or highlighted column name.
• Clear All Renames: Remove the new name for all columns.
• Clear Highlighted Renames: Remove the new name for all highlighted columns.
• Revert All to Original Type and Size: Undo all changes to type and size in all columns, and use the original values.
• Revert Highlighted to Original Type and Size: Undo changes to type and size in the selected or highlighted columns and use the original values.
• Forget All Missing Fields: Remove all columns that are no longer included in the data.
• Forget Highlighted Missing Fields: Remove all highlighted columns that are no longer included in the data.
• Deselect Duplicate Fields: Deselect the second column when duplicate column names exist; this option is only available with multiple inputs.

View the Output

The 3 outputs that result from the join are...

Additional Types of Joins

Reference this table to use the Join tool to execute different types of joins.

Inner Join: Contains records that joined from the L input to records in the R input.			The J output of the Join tool contains the result of an Inner Join.
Left Unjoined: Contains records from the L input that didn't join to records from the R input.			The L output of the Join tool contains the result of a Left Unjoined.
Right Unjoined: Contains records from the R input that didn't join to records from the L input.			The R output of the Join tool contains the result of a Right Unjoined.
Left Outer Join: All records from the L input, including the records that joined with the R input.			To do a Left Outer Join, connect the J and L outputs of the Join tool to the Union tool. Connect the J output first to establish the combined table schema.
Right Outer Join: All records from the R input including the records that joined with the L input.			To do a Right Outer Join, connect the J and R outputs of the Join tool to the Union tool. Connect the J output first to establish the combined table schema.
Full Outer Join: All of the records from both L and R inputs.			To do a Full Outer Join, connect the J, L, and R outputs of the Join tool to the Union tool. Connect the J output first to establish the combined table schema.

How do you know when to use left join or right join?

When to use left outer join vs right outer join?

Why would you use a right outer join?

When would you use left join and when left outer join?