Join is used to combine rows from two or more tables based on a related column. In a real-world relational database, data is structured in a large number of tables and which is why, there is a constant need to join these multiple tables based on logical relationships between them.
Let's see what are the Alteryx joins and how they work -
In Alteryx Left outer, Right outer and Inner join can be performed using "Join" tool whereas left join, right join and full outer join can be done using "Join" and "Union" tool.
Lets deep dive into the Alteryx Join-
Below are the sample Customer and Customer_Purchase tables which will help us uncover Alteryx joins.
Customer Table
Customer_Purchase Table
We consider customer table as primary table, I am using it as a left table(for best practice) and joining both table by CustomerID.
Left Outer Join-
The above result displays only customer with customerID 2. ID 2 is present only in left table hence we are getting a record with ID 2.
Inner Join-
Customer with CustomerID 1 is present in both tables that is why it is in inner join result set.
Right Outer Join-
CustomerID 5 is only in right table(customer_purchase) that's why appearing in right outer join result set.
Left Join-
This left join result set is joining all records of left table and common records of right table.
Right Join-
Right join is joining all records from right table and matching records from left table.
Full Outer Join-
Full outer join returns all records from both left and right table.
How Alteryx joins compare with SQL joins-
Lets see how Alteryx and SQL joins compare with one another. The below diagram explains the Alteryx joins and its equivalent in SQL joins
As we see above, in SQL we have 4 functions Left join, Right join, Inner Join and full outer join along with Minus Operator to get the equivalent Alteryx join.
Conclusion- Both Alteryx and SQL can perform similar operations on joins but the difference lies in Alteryx where you can do things with no code and just by drag and drop. In contrast for SQL, codes are necessary. Both will perform similar functions, Alteryx enables users with graphical interface where they can do joins among other various operations with no code.