When I was first introduced to Alteryx I couldn't wrap my head around why the outputs were so different than the ones you can create in SQL. The great news is you can create those same SQL outputs in Alteryx without any need to code. Before continuing, I suggest you take a look at how the Join Tool and Union Tool work in Alteryx.
In this post, I will be going over the most frequent SQL joins, though all SQL joins can be completed within Alteryx. Below find out how to create the following: Left Outer Join, Left Join, Right Outer Join, Right Join, Full Outer Join, and Inner Join.
For the below examples I will be using two tables. Let's take a look at both to see what they contain.
Table 1 has the following fields: customer_id, first_name, last_name, gender, email, and address.
Table 2 has the following fields: order, order_date, customer_id, category, and order_amount.
Since both tables share customer_id, I will be joining on that field.
Left Outer Join
- A Left Outer Join shows the results only from the left table that do not match with results from the right table.
- In Alteryx this join type can be created with the Join Tool by selecting the L output.
Example:
Left Join
- A Left Join shows the results from both the left table along with all matches from the right table.
- In Alteryx this join type can be created by using the Join and Union Tool by inputting the L and J outputs from the Join and running it through a Union.
Example:
Right Outer Join
- A Right Outer Join shows the results only from the right table that do not match with results from the left table.
- In Alteryx this join type can be created with the Join Tool by selecting the R output.
Example:
Right Join
- A Right Join shows the results from both the right table along with all matches from the left table.
- In Alteryx this join type can be created by using the Join and Union Tool by inputting the R and J outputs from the Join and running it through a Union.
Example:
Full Outer Join
- A Full Outer Join shows the results from both tables whether or not they match.
- In Alteryx this join type can be created by using the Join and Union Tool by inputting the L, J, and R outputs from the Join and running it through a Union.
Example:
Inner Join
- An Inner Join shows the results that matched from the left and right tables.
- In Alteryx this join type can be created with the Join Tool by selecting the J output.
Example:
