Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.
We'll be completing standard maintenance for our Single Sign-On system on November 30th 6pm Pacific Time for approximately one hour. During this time My Alteryx and sign-in functionality to all My Alteryx programs (Licensing Portal, Community, Beta Program, Case Portal, and others) may be inaccessible. Thank you for your patience.

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Left, Right, and Full Outer Joins are easy!

Loic
Alteryx
Alteryx
Created

How To: Make Left, Right, and Full outer joins

I noticed that a lot of customers in the Alteryx forums were confused about how to make left, right and full outer joins in Alteryx Designer. This KB entry goal is to make it easy and clear.

This is an aggregation in one place of information spread out in multiple sources: Designer help, various discussions on our forums, and public gallery.

First - what does the Join Tool do?

For now, the join tool does a simple inner join with an equal sign. That's it!

In particular:
• R output anchor is NOT the result of a right outer join. I know the R letter can make you think this but it is not.
• Similarly: L output anchor is NOT a left outer join. I know that got me at first too!

See the table below that shows you what each does for the “Join” tool:

Joins.PNG

Look at the online help to learn more: https://help.alteryx.com/current/Join.htm

Second - Can I do outer joins with the Join Tool then?

Yes! You have to combine it with a Union Tool:

- Join Tool + Union of L&J output anchors = Left outer Join,

- Join Tool + Union of R&J output anchors = Right outer Join,

- Join Tool + Union of R+L+J output anchors = Full Join.

outerjoins.PNG


Look at the online help to learn more: https://help.alteryx.com/current/Join.htm

Third - Download "Advanced Join" tool from Public Gallery


Now, Alteryx is very flexible as you can create your own tools or macros. You can find a lot of these on the public gallery.

There is a tool called "Advanced Join" that Neil Ryan created that works well for more complex joins (including left, right, full/cross etc). You can also apply a filtering condition.

You can find it here: https://gallery.alteryx.com/#!app/Advanced-Join/547f8df96ac90f0f2ca5e439
Download it, follow instructions to install it. It will show up first in the category "Join" in your Designer toolbar.

Advanced Join.PNG


Fourth - Use "Multiple Join" for Full outer join

Multiple Join - Full Outer join.PNG

In designer click on "open example" link that appears once you click on the "Multiple Join" tool for detailed examples.

Note

As Alteryx improves the "Join Tool", we might see an easier way to do outer joins. But for now these are the few options you have.

Additional Resources

No ratings
Comments
Hani_Patel
7 - Meteor

The second one is most important if recreating workflow from STD tools to IN-DB tools. Till now I was in a wrong impression that - Join Tool + Union of L&J output anchors = Left outer Unjoined, while actually it's a Join. 

 

After struggling for a month now this is a big relief to know the actual logic and get the right output from massy, large datasets.   

wdelta
5 - Atom

this should be included in the interactive lessons

djspring
6 - Meteoroid

Hello,

 

I'm having trouble interpreting the join results. What results are displayed first? The LEFT or RIGHT?

 

Thank you.

 

LisaL
Alteryx
Alteryx

@djspring 

That entirely depends on how you have configured the workflow.  If your Union tool specifies the input order, then that will tell you.  If not, then it's more difficult to know.  
The Help section on Join is much improved, and you might also find this article helpful:  How to Combine Join Output Anchors with a Union Tool

jdub_1999
5 - Atom

I can not get an inner join command to work in my workflow. When I type in Inner Join, Join is in blue highlight like it should be, but Inner does not highlight blue. Any thoughts?