On Day 1 of Week 4 of The Data School, we spent a hefty amount time trying to get to grips with the different ways you can link data that and how each of them work. Here is an overview of the four different types and how they appear in Tableau.
- TYPE: Join
WHAT: two tables from the same database
CRITERIA: the two tables must have matching field (s) in order to join, these are known as the joining conditions
HOW IT WORKS:
- Inner join- only keeps the rows that match from both tables
- Left join- keeps the left table + whatever matches from the right table
- Right join- keeps the right table + whatever matches from the left table
- Full outer- left table + the matches + right table
2. TYPE: Cross Database Join
WHAT: tables from different data sources
HOW IT WORKS: can do any of the four types of join above
PROS: full functionality
CONS: performance limitations as you are loading the data locally onto your laptop
3. TYPE: Blend
WHAT: two tables from different data sources
CRITERIA: there are no set joining conditions
HOW IT WORKS:
- The tables are joined by a left inner join only
- The first field you drop into the view becomes the primary table (left table)
- Primary table will have a blue tick, the secondary will have an orange tick
- It is possible to blend more than two data sources, the primary will always have a blue tick and any additional data sources will have an orange tick
- The secondary table must be aggregated
4. TYPE: Union
WHAT: two tables from different or the same data sources
HOW: the right table is added to the bottom of the left table
CRITERIA: have at least one matching field, if there are different fields then the table without that field will be populated with null
