Joins are something that I always struggled with when working with Tableau in the past. I tried lots of tutorials and there were many variations of diagrams aiming to explain them, but it remained something that I struggled with. This week has been a breakthrough for me on the topic, so I am hoping this method of looking at them will also help others who have struggled with joins.

First, I will list the types of joins, and then I will go on to explain what would be included for each type.

The different types of joins:

Left Outer Join
Left Inner Join
Inner Join
Right Inner Join
Right Outer Join
Outer Join

I am going to use tables on clothing data to illustrate what would be included for each type of join.

Below are our base tables, Table 1 (the left table) looks at the colour of each clothing item and Table 2 (the right table) looks at the size of clothing items. The tables will be joined across the specified field ‘Clothing ID’. The joined field will have to be in the same format, for example joining a string to a string.

What is included in each join?

An inner join includes only the common records from the data. For example in this instance both Table 1 and 2 have Clothing IDs 10001, 20001, and 40001 so only these 3 records will be included, along with their corresponding colours and sizes.

A left outer join would ONLY include records from Table 1 (the left table) that do not have a match from Table 2 (the right table). This may be useful to see what records from Table 1 are missing the information from Table 2.

A left inner join would have all the contents of Table 1 (the left table), as well as all matching records from Table 2 (the right table). In this there may be some nulls as not all records from Table 1 may have corresponding data in Table 2. This type of join could be useful to see what records have complete sets of data and which records are missing data from Table 2.

A right outer join would ONLY include records from Table 2 (the right table) that do not have a match from Table 1 (the left table). In this situation this type of join may be used to see which Clothing IDs are missing information on colour.

A right inner join would have all the contents of Table 2 (the right table), as well as all matching records from Table 1 (the left table). In this there may be some nulls as not all records from Table 2 may have corresponding data in Table 1. In this situation this type of join could allow us to see which records have complete data on colour and size, and which records are missing information on colour.

An outer join will include all records from both tables. For this there will be some nulls as not all records from Table 1 may have corresponding data in Table 2 and vice versa.