What Are the Types of Joins Supported in Tableau?

Combining data from different spreadsheets or tables is a fundamental step in building insightful reports. In Tableau, this process is handled through joins, a powerful feature that lets you connect related data sets across a common field to create a single unified view. Understanding the different types of joins is essential for preparing your data correctly and answering more complex business questions.

This tutorial will walk you through the four main types of joins available in Tableau. We’ll cover what each one does, when to use it, and what the results look like, using simple, everyday examples to make the ideas stick.

Why are Joins So Important in Tableau?

Imagine you run an online store. You have one spreadsheet with all your sales data - like Order ID, Product Name, and Price. You also have another spreadsheet with your customer information - like Customer ID, Customer Name, and Region. On their own, these tables tell separate stories. But what if you want to answer questions like, "Which region drove the most revenue?" or "Who are our top 100 customers by sales?"

To do that, you'll need to combine the sales data with the customer data. That is exactly where joins come in. Joins let you link these different tables together based on a shared piece of information, like Customer ID, so that you can analyze them together. Think of it as connecting the dots between disparate datasets to uncover more detailed insights.

Understanding Common Fields: The Key to Joins in Data

The secret to a successful join is a common field, also known as a join key. This is a column that exists in both tables that you're trying to connect.

• Product SKUs
• Customer IDs
• Order IDs

The key is that this field should be able to uniquely identify records to connect the tables. Most often, it's a good idea to use a unique identifier as a join key. Also, remember that data types must match. It’s difficult to join a text field (like "Jane Doe") to a number field (like "12345"). Tableau typically warns you about mismatched data types when creating your join.

The Four Main Types of Joins in Tableau

Tableau supports four main types of joins - Inner, Left, Right, and Full Outer. Let's take a closer look at each one:

Inner Join - The Perfect Match

An inner join is the most restrictive type of join. It returns only the records that have matching values in both tables, similar to an exclusive club. Only rows with a match in both tables are returned.

An inner join between Sales and Customers on Customer ID would return the below table:

Notice how orders without matching Customer IDs (e.g., Order ID 789 with Customer ID 3) are excluded. The inner join includes only customers who have made purchases.

When to Use It

An inner join is perfect when you want a clean dataset that only contains data available in both tables. For example, if you want to analyze sales from customers who have full contact information, you'll want to exclude any customers lacking such information. It's ideal for refining data.

Left Join - All from the First, Some from the Second

A left join keeps every record from the first (left) table and adds only matching records from the second (right) table. If there isn’t a match, the fields from the right table will display as 'null'.

If you perform a left join with Sales as the left table and Customers as the right table, you would get:

Notice how Order 789 without a Customer ID is included, but its Customer Name and Region are null.

When to Use It

A left join is useful for finding discrepancies or gaps in data that don’t exist in both sets. This can be student lists, employee records, or system problems.

Right Join: All from the Second, Some from the First

A right join is the exact opposite of a left join. It keeps all rows from the right table and matches them with rows from the left table. Any non-matching rows in the left table will result in null fields.

A right join between Customers as the right table and Sales as the left table would look like this:

Note how Sally Sales and Adam Smith are included even though they don’t have sales entries in the sales table. This is because the right table’s records are prioritized.

When to Use It

Right joins are excellent for verifying missing customer entries. By looking at nulls in the Order ID and Product Name fields, you can quickly identify which customers engage the best for upcoming campaigns.

Full Outer Join - The Whole Shebang

A full outer join is the most all-inclusive join. It returns all rows from both tables and matches up those from either. Any non-matching rows will show null values where appropriate.

With a full outer join, our combined table would look like this:

Notice that Order 789 from Sales and Sally Sales from Customers are both included, even though they don't have corresponding entries in the other table.

When to Use It

A full outer join is useful when you want the comprehensive view of all data. It’s great for data auditing and finding dynamics that exist in both datasets as a whole.

• Check Types: Make sure your join key types match appropriately (e.g., don’t combine a text and numeric field). Using Left Joins or Inner Joins can be essential, especially when analyzing your data and revealing results.
• References and Considerations: In your work with millions of rows of data, join choices can affect the results. Tableau offers support to extract data efficiently. When extracting, keep in mind your local storage and Tableau's proprietary handling of queries.
• Handling Duplicate Data Records: In complex scenarios, joins can unexpectedly multiply rows. For example, if a customer has multiple orders, an Inner Join will multiply sales and customer data and include the customer information for each case. Keep this in mind when analyzing your results.
• Watch Out for Blending: The difference between joining data and blending is significant. While joins combine tables of the same data - such as SQL databases or worksheets - blending is used to combine data from different sources. Blending happens after the worksheet level and is generally more flexible but sometimes slower. Joins ensure everything's in one level before processing.

Final Thoughts

Mastering the use of joins - Inner, Left, Right, and Full Outer - lays a foundational skill for anyone using Tableau. By understanding when and why to use each one, you enhance your ability to unify data from multiple sources and uncover well-rounded insights. Utilizing joins effectively allows Tableau to convert raw data into a coherent presentation.

Of course, refining join setups to exclude unwanted data sources or enhancing datasets works easier in Tableau, making manual work more manageable. While restructuring helps uncover most issues, you can skip this step and go straight to insights instead.

With Graphed, a built-in visual calendar, you create posts when inspiration peaks, schedule them to run at "prime time," and rely on our reliable scheduling to keep your content flowing seamlessly. You can enhance your reach across platforms by utilizing Graphed's tools to build your audience smarter. Try Graphed today to streamline your content scheduling and focus your energy where it matters.