By: Eric Parker
Eric Parker lives in Seattle and has been teaching Tableau and Alteryx since 2014. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email.
*May 2023 Update: When I wrote the below post, I was still learning about Relationships. Please note that Relationships can mimic all types of joins, Inner, Left, Right and Outer. Which join type they reflect is determined by which data sources you use a measure from.
No measures in view = Inner Join
Table A Measure Only with Dimension from Table B = Left Join
Table B Measure Only with Dimension from Table A = Right Join
Measures from Both Table A and Table B = Outer Join
For a deep dive into Relationships vs. Joins, check out this webinar.
The below examples mimics an Outer Join because measures from both tables are used.*
Original Post:
The new Tableau Relationships feature will likely replace joins in a lot of circumstances. I was recently asked, “What kind of join does the Tableau Relationships feature most closely resemble?” I didn’t know with certainty so I did some digging.
I turned to a trusted data source I use for testing scenarios like this. It’s an Excel file which consists of two worksheets.
The first worksheet is named “Chocolate Shop Sales” and that made up chocolate shop is closed on Sundays.
The second worksheet is called “Pub Sales” and the imaginary pub is closed on Mondays.
If we used Chocolate Sales first and joined the Pub Sales table second, here are the results of the four join types Tableau Desktop offers:
● Left join: Lose Sundays (since they don’t exist in the left table).
● Right join: Lose Mondays (since they don’t exist in the right table).
● Inner join: Lose both Mondays and Sundays since neither can find a match in the opposite table.
● Outer join: Keep all weekdays including Mondays and Sundays.
To perform this test, I’m going to create a relationship between the chocolate and pub tables using the Date field.
Here’s the initial view of sales by day only using fields from the Chocolate Sales table.
Notice how the situation changes when Pub Sales are added.
There are $16,000 in sales with a date value of “Null”.
If I view the underlying data I can see that the $16,000 comes from 3 rows of data from the Pub Sales table. They don’t have valid dates in the “Date” column (column that came from Chocolate Sales) but they do have valid dates in the “Date (Pub Sales)” column!
This demonstrates that Tableau is performing a sort of Outer Join with the relationships feature because it is keeping all data whether it matches the primary table or not!
Here’s a quick solution to get every value mapped back to a valid date. I’m going to create a field that combines both date values into a single column.
By using that date field in the worksheet instead, every pub and chocolate sale now maps back to a valid date.
If you’re looking to learn more about when to utilize the new Tableau Relationships feature, check out our recent webinar (recording), Tableau Relationships: What are they good for?.