Difference between Tableau relationships and Joins
In my previous blog How to create relationships in Tableau, we learned about relationships. In this blog, we will study the difference between Tableau Relationships and joins. We will also see if joins are still available in Tableau.
To explain this concept, I will use the following three tables,
*In Tableau, Joins work similar to database joins.
Difference between Tableau Relationships and Joins
- In Tableau, relationships are called the Logical layer and Joins are called the physical layer.
- As we understood in the previous blog, Relationships are not Joins. Relationships keep the tables separate and do not merge them into a single table.
- If you connect to MS-Excel source of these three tables, a relationship is created between Emp and Dept based on the common field DeptNo and a relationship is created between Emp and Salary based on JobID, which is shown below. (please comment below if you need these tables for your practice)
Emp data is displayed
You will notice that the rows of Emp table only will be displayed in the preview window, since Emp table is selected. The rows from other two tables will NOT be displayed. This occurs because relationships keep the tables separate.
An analysis of data from these tables can be easily performed. For e.g. I can determine the Employees working in different departments and their respective salaries based on the job titles.
In the earlier versions of Tableau, in the absence of a relationships, this task would have required data blending with multiple sources and would therefore be slow. Using relationships, we can do this in one datasource. Also, data blending is limited to a worksheet, but relationships are available for the entire workbook. Relationships will increase the performance of your dashboard.
Where are Tableau Joins?
Joins are still available and can be created as required.
- A Join between two tables merges the tables and creates a single table.
- Joins between two tables are created based on the common field names. To create a join, you need to double click on the table in the logical/relationship layer and add another table next to it.
- To create a join between Emp and Dept tables, in the relationship, double click on the Emp table and add dept to it. You can choose the type of join you need.
You can close this Join/physical layer by clicking on the cross on the right corner. This will navigate you back to the relationship layer. Notice the emp table will look different as it now contains a join.
Compared to Relationships, Joins have some disadvantages
- Joins are static and once made, will affect the data in the entire workbook.
- Depending on the join type you use, you may lose unmatched data. For example, inner join shows only matching rows between the tables. Although emp table has 7 rows, you will see only 5 rows when inner join is used.
- If you use Left join, with Emp table on the left, you will get all the rows from the Emp table but dept table will show only the matching rows and therefore Deptno 800 will not be displayed.
Data when left join is used 4. If you use a Right join, with Dept table on the right, you will get all the rows from the dept table including DeptNo 800. The employees in Emp table that do not belong to any department, will not be displayed.
Data using Right join
5. This problem can be resolved by using outer join, but here again, if your tables are huge, this may result in slow performance. Due to this reason, Outer joins are not recommended.
All these problems will be resolved by using Relationships. You can still use joins but use them when absolutely necessary.
Tableau offers diverse techniques for merging data, such as, relationships, joins, unions, and data blending. Compare each method and learn which method to use in specific scenarios.
For more such tips and to become an expert in Tableau checkout my new Tableau book – Dashboarding with Tableau
To check your Tableau knowledge visit Tableau Quiz
About the Author
Chandraish Sinha has 20+ years of experience in Information Technology. He is an accomplished author and has published 11 books covering Business Intelligence related topics such as, Tableau,Power BI, and Qlik. Checkout his Amazon Author profile.
His latest book Excel Basics to Advanced covers all the aspects of MS Excel and provides exercises for self-learning.
Similarly, his recent book, Dashboarding with Tableau, covers all the features in Tableau and includes exercises for self-learning.
He has implemented IT solutions in different domains viz. Pharmaceutical, Healthcare, Telecom, Financial and Retail.
He blogs regularly on various IT topics. Check them out in the links given below: