Join Your Data

The data we analyze mostly does not reside in on single table. Instead the information that we are looking for may be present in other tables. In situation like these we use the concept of joins to bring together data from multiple tables. Joining is the method of combining tables on the basis of some related field. This operation adds columns to our existing data.

Note:

• When joining tables, the fields that you join on must have the same data type. If you change the data type after you join the tables, the join will break.
• Published data sources cannot be used in joins in Tableau.

Type of Joins :

Join Type	Description
Inner Join	When you use an inner join to combine tables, the result is a table that contains values that have matches in both tables. When you use a left join to combine tables, the result is a table that contains all values from the left table and corresponding matches from the right table.
Left Join	When a value in the left table doesn’t have a corresponding match in the right table, you see a null value in the data grid. When you use a right join to combine tables, the result is a table that contains all values from the right table and corresponding matches from the left table.
Right Join	When you use a right join to combine tables, the result is a table that contains all values from the right table and corresponding matches from the left table. When a value in the right table doesn’t have a corresponding match in the left table, you see a null value in the data grid.
Full Outer Join	When you use a full outer join to combine tables, the result is a table that contains all values from both tables. When a value from either table doesn’t have a match with the other table, you see a null value in the data grid.

Lets try and understand these join types by examples. Consider two tables as under . Table 1 contains the Student ID and his marks in Science , while table 2 contains Student ID and his marks in Mathematics.

Student Name	Science		Student Name	Mathematics
Amit	92		Nikunj	92
Rakesh	87		Amit	86
Arjun	93		Rakesh	88
Nishant	80		Shankha	80

Our goal now id to combine these tables and view the marks of students in one single table. The common column between these two is Student Name and we would use the same in our join condition.

Lets import this excel file in Tableau and drag both the sheets on the canvas area, By default Tableau identifies the common column between them and does a inner join.

Inner Join

Inner join gives the result for those names which are common in both of the database. In our two sheets we see that the common names are Amit and Rakesh, hence when we do a inner join we get the science and mathematics marks for these two names only .

Left Join

Now lets change the join to a left join and see what the result is. To change it from a inner to left join , click on the join icon and you will see a window pop up which allows you to change your join type. Select left in that.

Select the columns Student Name, right click on merge mismatched cell. Your result would be as under :

Here we see that the names of Student that were present in the left table are present in the final join output and since we do not have them in the right table , the values for mathematics for these comes as null. So if a left join , the values of the common field from the left table is retained

Right Join

Next to the join condition again and change it to right join. Your result of the join would be as under:

Here we see that the names which where present in the right table ( Mathematics ) is retained and corresponding values are searched in the left table. The values which are not there are replaced by null. As we do not have the names Nikunj and Shankha in the left table we do not see their science marks in the join result and instead see a null value.

Full Outer Join

Next change the join condition to Full outer form right , and your result would be as under:

A full outer join combines are the elements of the two data sources , in the output of the join we see that under the names columns we gave the six names that is a combination of both the tables. Values for science and mathematics are reflected as they are in the table and for the names they do not exist we have a null value.

I hope the concept of joins would now have been clear to all of you now. In case if you have any issues in understanding you can reach me via email and I will be glad to assist.

Happy Learning!!