Left, Right, and Full Outer Joins are easy!

[Loic]

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:

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.

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.

Fourth - Use "Multiple Join" for Full outer join

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

• https://help.alteryx.com/current/Join.htm
• https://gallery.alteryx.com/#!app/Advanced-Join/547f8df96ac90f0f2ca5e439
• https://help.alteryx.com/current/JoinMultiple.htm

[Hani_Patel]

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]

this should be included in the interactive lessons

[djspring]

Hello,

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

Thank you.

[lepome]

@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]

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?

[okaychill]

I wanted to know how to interpret join from SQL in Alteryx

SELECT				*

	INTO				#NewTable
	FROM				[Sometable]

	WHERE				
  [Col1] IN  ('x','y') 		
  AND	[Col2] 		<> 'I'							
  AND	[Col3] 	= 'KRED'