Tableau Joins

What are Tableau Joins?

When there is a need to view the features of different datasets in a consolidated database, be it in different types, matched, or mismatched, we use Tableau Joins. Tableau joins are used to join two data sources to view their features similarly and also perform visualizations if needed. There are several types of joins you can use, each serving different purposes and resulting in different data sets.

Here is an example depicting how to make Tableau database joins.

Here, we create the Tableau joins in database using inner join with the same database, but different worksheets. Here, we acquire all these tables from the Bookshop database.

Types And Applications

There are four main types of Tableau Database joins possible. They are explained in detail.

#1 – Inner Join

This connects both databases on 1 common feature and includes only the values that are common or equal in both databases. The other values are ignored.

Example: If Table A and Table B have a standard column “ID,” an inner join will include only the rows where “ID” values match in both tables.

#2 – Left Join

The Left Join or the Left Outer join is used to connect the databases where all the values from the left table, that is, the first table included have all the values, but, only the matching values from the right table are included. Values without matches are returned as nulls in the right table.

Example: If Table A is the left table and Table B is the right table, a left join will include all rows from Table A and the matched rows from Table B. Unmatched rows in Table B will have NULLs.

#3 – Right Join

Similar to the left join, in the Right Join or Right Outer Join, all the values from the right table, that is, the table added after the default table in Tableau, are included. Only the matching values are included in the left table. Values with no matches are depicted as nulls in the left table.

Example: If Table A is the left table and Table B is the right table, a right join will include all rows from Table B and the matched rows from Table A. Unmatched rows in Table A will have NULLs.

#4 – Full Outer Join

The functionality is similar to Right Join or Left Join. The only difference is that all values from both tables are included in this join. Any values with no matches from either side of the table are shown as nulls.

Example: If Table A and Table B have a common column “ID”, a full join will include all rows from both tables, with NULLs in places where there are no matches.

In this way, you can see three ways to use the Outer Join Tableau methods.

#5 – Union

It is not part of the Tableau Joins case-sensitive method, but it is also used to combine databases by appending the rows of the datasets in a table. You can create new unions in Tableau in a simple manner by clicking on “New Union” in the “Data Source” tab.

How to create a Join in Tableau?

We create the Tableau Database joins as shown.

Step 1: In the Tableau Public application, click on “File” and then “New” to create a new Tableau workbook.

It will open a new workbook with a default sheet available.

Step 2: Connect to a data source by clicking on “Ctrl+D” in your workbook. Then, it opens your local storage, where you can navigate and select your database.

You can also drag and drop the file you need. The “books.csv” file is used.

Step 3: Go to the “Data Source” tab to view the database. The “books.csv” file is shown.

Step 4: Right-click on the database and select “Open.”

Step 5: Inside the table, drag and drop the database “ratings.csv” from the “Files” section. Tableau shows files similar to the data source you have integrated from your local storage to help make things easier.

The inner join is done by default if dragged and dropped and is the combined database view.

Miscellaneous: Drag and drop the file “users.csv”. But you can see that it isn’t working because Tableau cannot detect any common files on its own. 

In such cases, you can either remove the join or manually define it.

Examples

Learn more about the techniques and the different ways to perform the Tableau Database Joins. The examples below provide a simple demonstration of how it can be used.

Example #1: Edit a Join Type in Tableau

Consider that you have two separate datasets. One contains the books available on Amazon, and the other has ratings for the different books available. You want to see the total number of ratings for each of the books on Amazon in a visualization. It can be done by joining the databases and then performing the necessary visualizations.

Step 1: In a new workbook, drag and drop the “books.csv” dataset.

Step 2: Open the dataset to join the tables from different datasets. This is done by right-clicking on “books.csv” and then selecting “Open.”

Step 3: Drag and drop “ratings.csv” from the “Files” tab in Tableau. Note that both the files should remain in the same directory, or else you may need to add new connections, which doesn’t give you the choice of the type of join that you want.

Step 4: Click on the “Join” icon in between “books.csv” and “ratings.csv” and select “Right Join”.

The common features are declared by default in Tableau.

Step 5: Check in the Data Source view whether the “Right Join” has been done successfully. Since it is a Right Outer Join, all the features from “ratings.csv” will be included. Only the matching values in “ratings.csv” will be shown in “books.csv.” The unmatched values will be shown as “NULL.”

If you open the “ratings.csv” file, you can see that all the features (not only the common features) are included.

Step 6: Go to “Sheet1”, the default sheet in a new workbook, to start with your calculations. You can see the dimensions and measures defined for both databases.

Step 7: Place “Book-Title” from “books.csv” in the “Rows” tab.

Step 8: Place the “Book-Rating” value in the “Columns” tab.

Step 9: Drag and drop the “Book-Author” in the “Filter” tab. This is done to remove all the null values. Since this is a right join, the mismatched values are depicted as NULL values. This may harm the visualization that you’re trying to do. Click on “Null” in the popup and click “Exclude.”

Step 10: Click on this icon in the graph to sort the visualization in descending order.

It is the graph made from joining both databases via right join.

Example #2: Edit Join Fields in Tableau

Suppose you have two databases that you want different common features from the default features that Tableau has pronounced, how do you do that? By editing the Join Fields in Tableau, you can drastically change the result. You can do this by following these steps. Here is the number of reviews from different cities in America for each Airbnb. The cities in question are Albany, New York, and Austin, Texas.

Step 1: Drag and drop the Airbnb dataset of Austin, Texas, in a new workbook.

Step 2: Right-click on the dataset and select “Open.”

Step 3: Place the Airbnb dataset of Albany, New York, by dragging and dropping it next to the Austin dataset.

If you click on the join symbol, you can see the common feature that Tableau has automatically chosen.

You can see that there is no Tableau joins case-sensitive features. The feature heading joins are mostly case-insensitive unless specified.

Step 4: Click on the join fields to select the field of your choice to be joined.

The field chosen is the “Number of Reviews” for both datasets.

Step 5: View the joins in the data view in the “Data Source” tab.

Only the common values from both datasets are chosen.

Step 6: Select the year when the “last review” was made. Place that in the “Columns.” Then, place both the “minimum nights” feature from both datasets.

Step 7: Click on “Show Me” in the top-right corner of the worksheet and select “side-by-side bars.”

Step 8: Right-click on the “Null” field in the X-axis and select “Exclude.”

It is the final graph.

The colors indicate the dataset from which they were taken.

Example #3

In this example, you can learn how to join tables from different worksheets in the same database and gain insights from them. Here, the “Bookshop” dataset is used.

Step 1: Drag and drop the “Bookshop” dataset.

The available worksheets are shown in the “Files” tab.

Step 2: Drag and drop “Book” from the files.

Step 3: Right-click on “Book” and select “Open.”

Step 4: In the tab, drag and drop “Author,” “Award,” and “Edition” from the files. All three worksheets are joined using “inner join.”

Step 5: Go to the first worksheet to perform your visualization. Select the features from the following tables to create the visualization. The black mark is used in columns, and the red ones are used in rows.

Step 6: Place “Title” in the “Columns” and place the “Hrs Writing per Day,” “Pages,” and “Price” in the “Rows.”

Step 7: In the top-right-hand corner, click on “Show Me” and select “side-by-side bars.”

It is the graph we obtain when we join two tables with the “Books” table.

Important Things To Note

• Ensure you understand the structure and relationships within your data before creating joins.
• Inner joins are efficient for filtering data to include only matching records.
• Double-check if the join conditions are correct and that join keys are appropriately indexed.
• Avoid using outer joins in excess, as they can result in large datasets and performance issues.
• Ensure the data types of join keys match to avoid errors and unexpected results.
• Only join tables that are essential for your analysis to keep the data model simple.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Tableau Joins, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Tableau Joins. Here we learn different ways to perform the Tableau Database Joins with examples, types & applications and points to remember. You can learn more from the following articles –

• Tableau Data Source
• Tableau Data Blending
• Tableau Data Visualization