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.

  1. 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