When it comes to Alteryx, there are certain types of joins that we need to keep in mind: Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, Left Only (or Left Unjoined), and Right Only (or Right Unjoined). These terms may seem familiar if you’ve used SQL, Tableau, or another tool or language that uses joins, even if the terms may be slightly different, but what do they mean?
In this blog, I will be going through each type of join and what better way to present these joins than through classic Venn diagrams?
Inner Join
An Inner Join pulls through records that matched from the left input (table 1) to the right input (table 2). In this type of join, the values from the left input that do not match to values in the right input as well as all values from the right input that do not match to values in the left output are then left out.
Left Outer Join
The Left Outer Join pulls through records that match from the left input (table 1) to the right input (table 2), the same as an Inner Join. The difference between the Left Outer Join and Inner Join, however, is that it also pulls through all the records from the left input regardless of whether or not it matches to records in the right table.
Right Outer Join
The Right Outer Join is similar to the Left Outer Join in that it pulls through records that match from the left and right tables. However, instead of pulling through all records from the left input, the Right Outer Join pulls through all the records from the right input regardless of whether or not it matches to records in the left table.
Full Outer Join
The Full Outer Join pulls through all records from the left table and right table while also matching records from the left and right tables into a single table. Records without a match will have NULL values in those fields.
Left Only & Right Only Joins
The Left Only and Right Only joins only pull through records that do not match with the other table. Another way to understand them would be to think of them in the context of an Inner Join. In an Inner Join, records that matched between the two tables would be pulled through. However, in this case, the Left Only Join only pulls through records that wouldn’t be in the Inner Join and disregards all records in the right input table. The Right Only join functions the same way, but only pulls through all records from the right input table that do not match with records from the left input table and also disregards all records in the left input table.
Hope that helps clarify the different types of joins and with that let's analyze away!