Join tool icon

Join Tool

Last modified: September 15, 2021

Docs are available before the release of Designer Cloud so you can get a sneak peek. This content might change between now and the official release.

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 Cloud.

Use Join to combine 2 inputs based on common columns between the 2 tables. You can also join 2 data streams based on row position.

Configure the Tool

  1. Select how to perform the Join. The 2 options are by row position and by a specific column.
    • Join by Specific Columns: Select this option when the two tables have one or more columns 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.
    • 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.

    Potential Error Message

    The Join tool restricts what field types can be joined together. The error messages might state "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.
  2. Each Input, left (L) and right (R), has a dropdown list where you can select columns. Select the join column for each input. Designer Cloud automatically selects a join column from an input if the same column header has already been 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 for each input.
    • To delete a join field, select a number and then select Delete.
  3. Use the Columns to Keep table to modify the incoming data stream. Each row in the table represents a column in the data (for more details, continue reading).

Select, Deselect, and Reorder Columns

  • To include a column in data, check the check box. Uncheck the check box to exclude the column.
  • To reorder the columns of data...
    1. Select and hold the reorder icon, and drag the row up or down.
    2. Select to highlight a row and use the up arrow or down arrow 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.

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 the Output

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

 L anchor Contains rows from the L input that didn't join to rows from the R input.
Thumbnail
J anchor Contains rows that joined from the L input to the rows in the R input.
Thumbnail
R anchor Contains rows from the R input that didn't join to rows from the L input.
Thumbnail

Additional Types of Joins

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

Inner Join: Contains rows that joined from the L input to rows in the R input.
Thumbnail

The J output of the Join tool contains the result of an inner join.

Left Unjoined: Contains rows from the L input that didn't join to rows from the R input.
Thumbnail

The L output of the Join tool contains the result of a left unjoined.

Right Unjoined: Contains rows from the R input that didn't join to rows from the L input.
Thumbnail
The R output of the Join tool contains the result of a right unjoined.
Left Outer Join: All rows from the L input, including the rows that joined with the R input.
Thumbnail

To perform a left outer join, connect the J and L outputs of the Join tool to the Union tool.

Connect the J output 1st to establish the combined table schema.

Right Outer Join: All rows from the R input including the rows that joined with the L input.
Thumbnail

To perform a right outer join, connect the J and R outputs of the Join tool to the Union tool.

Connect the J output 1st to establish the combined table schema.

Full Outer Join: All of the rows from both L and R inputs.
Thumbnail

To perform a full outer join, connect the J, L, and R outputs of the Join tool to the Union tool.

Connect the J output 1st to establish the combined table schema.

Commas as Decimal Separators 

Check the Use Commas as Decimal Separators (String/Numeric Conversions only) to use a comma (,) as a decimal separator.

Was This Page Helpful?

Running into problems or issues with your Alteryx product? Visit the Alteryx Community or contact support. Can't submit this form? Email us.