Relationships, Joins, Blends, and Unions
Saturday, May 16th, 2020
When you update to Tableau 2020.2, you’ll see this on the data source view:
If you didn’t read the announcement from Tableau, you’ll think, “relate? what ever happened to join?”
Joins are still available in Tableau 2020.2, you just double-click to get back to the view you’re used to where you can create joins and unions:
Tableau says that relationships have mostly superseded blends. Relationships allow you to combine also avoid data duplication but are more flexible.
| Combines columns or rows? | Match on key field duplicates data? | Limitations | Tableau Documentation | |
|---|---|---|---|---|
| Relationship | Combines columns | No | Relationships cannot be formed between tables from data sources published to Tableau Server or Tableau Online. | Relationships |
| Join | Combines columns | Yes | Cross database joins will not work with all data sources | Joins |
| Blend | Combines columns | No | Fields from the secondary data source cannot be brought in to the view unless there is a match on the key field. This makes a blend somewhat comparable to a left join, since data from the primary data source is always brought into the view even if there is no match to the secondary source. Blends are only able to combine two tables, a primary and secondary data source. More information on limitations of blending here here | Blends |
| Union | Combines rows | No, since unions do not use key fields. | Data sources used in the union must come from the same connection | Unions |
Join Vs Union
Joins combine columns from one table with columns from another table. Here’s an example of how a join looks in Tableau:
And here’s what the data looks like after the two tables have been combined using the join. Notice that the first three columns come from the Book table, while the next three columns come from the Award table.
Unions combine rows from one table with rows from another table. Here’s an example of how a union looks in Tableau:
And here is what the data looks like when you combine Sales Q1 and Sales Q2 with a union. Notice the three of the rows come from Sales Q1 and three of the rows come from Sales Q2
Join Vs Relationship
When tables are joined, the result table has a row each time the join criteria is met. For example, if we have two tables, one with transaction data showing purchases:
and a second table with Monthly Sales targets, with one row per month:
Let’s see how these are combined in Tableau, first using a relationship. I use datetrunc so that 1/14/2022 becomes 1/1/2022 so that it matches to the value in the Monthly Sales target table.
Here’s my view in Tableau:
Now try the same but with a join. Notice that I have the option of Inner, Left, Right, and Full Outer, which I didn’t have when I did the relationship.
Also notice the data preview area. The values of Sales Amt Target are replicated. The values from the sales targets are replicated for each row of the purchase data.
If I don’t do anything to handle this duplication, and simply apply a sum to the Sales Amt Target field, I end up with inflated values for Sales Target:
So this illustrates a couple of the key differences between joins and relationships:
- Joins give the option of different join types – inner, outer, left, and right – which handle nonmatches differently
- Joins produce a row each time the join criteria is met, which can cause duplication. Relationships do not produce duplication.
You can download the Join vs Relationship workbook here: https://public.tableau.com/app/profile/lukas.halim/viz/JoinVsRelationship/Join