Connecting to Data & Joins

Picking up right after your data is loaded, understand Tableau's Physical Layer and how to build joins (inner, left, right, full outer) between tables, how joins differ from Relationships in the Logical Layer, and how to use Join Calculations to create composite keys when tables lack a shared column.

6 minutesVideo LessonPDF notes

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Ready to continue?

Mark this lesson as complete when you're ready to proceed.

Key moments

Introduction & Physical Layer — The instructor introduces data joins and demonstrates how to enter the physical layer by double-clicking a logical table.
Joining Tables on Common Fields — The Award table is dragged into the physical canvas and joined to Book using Title as the common key.
Join Types & Venn Diagrams — The instructor explains Inner, Left, Right, and Full Outer joins using Tableau's Venn diagram interface.
Custom Join Calculations — A join calculation concatenates Book ID1 and Book ID2 to match the Book table's composite primary key.
Closing the Physical Layer — The physical layer is closed to verify the resulting joined table in the logical data model.

PDF notes

Bottom line: You will combine multiple relational tables in Tableau using physical-layer joins instead of logical-layer relationships. You will master configuring Inner, Left, Right, and Full Outer join types, and write custom join calculations to merge tables lacking identical join keys.

This lesson on Connecting to Data & Joins is hands-on and example-driven. You will combine multiple relational tables in Tableau using physical-layer joins instead of logical-layer relationships. You will master configuring Inner, Left, Right, and Full Outer join types, and write custom join calculations to merge tables lacking identical join keys.

What You'll Be Able To Do

Navigate between the logical layer and physical layer of Tableau's data model.
Configure Inner, Left, Right, and Full Outer joins using common key fields.
Interpret Venn diagram visual cues to predict row inclusion and null generation.
Write custom join calculations to concatenate or transform fields for table matching.

Detailed Concept Walkthrough

1. Navigating the Physical Layer

Tableau's data model consists of two distinct tiers: the top logical layer (relationships/noodles) and the underlying physical layer (joins/unions). Joins occur strictly within the physical layer.

Mechanism: Double-clicking any logical table opens its physical container canvas, marked by an orange border and a dedicated close button in the canvas header.
Under the Hood: While logical relationships preserve separate levels of detail dynamically per visualization, entering the physical layer forces a fixed row-level merge before data reaches the worksheet.
Best Practice: Use the physical layer only when you explicitly need a fixed, pre-aggregated, or merged tabular schema across specific tables.

// Navigation Action in Tableau UI:
// 1. Double-click [Book] table in logical canvas.
// 2. Drag [Award] table into the opened container.
// 3. Click the Join icon to edit join conditions.

Key Takeaway: Double-click a logical table to access the physical layer where traditional Venn-diagram joins are configured.

2. Join Types and Venn Diagrams

Join types determine which rows are retained when combining tables on matching join keys, visualised in Tableau via shaded Venn diagram icons.

Mechanism: Click the join icon between two physical tables to select Inner, Left, Right, or Full Outer join clauses based on field equality.
Execution Flow: An Inner join returns only intersecting rows; Left join retains all left-table rows with matching right-table data; Right join retains all right-table rows; Full Outer combines all rows from both sides, producing nulls where keys do not match.
Nuance: Hovering over the Venn diagram icons provides live visual warnings indicating where null values will be introduced across non-matching records.

-- Equivalent SQL executed under the hood for a Left Join:
SELECT *
FROM Book
LEFT JOIN Award
  ON Book.Title = Award.Title;

Key Takeaway: Select join types by referencing the shaded areas of Tableau's Venn diagram interface to control row retention and null insertion.

3. Join Calculations for Mismatched Keys

When two tables lack an identical key field, join calculations dynamically transform or concatenate fields on the fly to establish a match.

Mechanism: Select 'Create Join Calculation' from the field drop-down inside the join configuration dialogue to open the formula editor.
Syntax Rule: Write standard Tableau expressions to align formats, such as concatenating string split fields to match a composite primary key.
Best Practice: Inspect source data closely to detect composite keys before joining, avoiding dropped rows caused by mismatched field schemas.

// Custom Join Calculation inside Tableau Join Dialog
// Left Table Key: [Book ID]
// Right Table Join Clause Expression:
[Book ID1] + [Book ID2]

Key Takeaway: Use custom join calculations to construct composite keys or transform data types directly inside the join configuration dialogue.

Topics Covered in Connecting to Data & Joins

Introduction & Physical Layer (0:00 - 0:45) — The instructor introduces data joins and demonstrates how to enter the physical layer by double-clicking a logical table.
Joining Tables on Common Fields (0:46 - 1:35) — The Award table is dragged into the physical canvas and joined to Book using Title as the common key.
Join Types & Venn Diagrams (1:36 - 2:14) — The instructor explains Inner, Left, Right, and Full Outer joins using Tableau's Venn diagram interface.
Custom Join Calculations (2:15 - 3:05) — A join calculation concatenates Book ID1 and Book ID2 to match the Book table's composite primary key.
Closing the Physical Layer (3:06 - 3:34) — The physical layer is closed to verify the resulting joined table in the logical data model.

Reference Cheat Sheet

Double-Click Logical Table — Opens physical layer container for joins and unions
Inner Join — Retains only records with matching keys in both tables
```
Book.Title = Award.Title
```
Left Join — Retains all left records and matching right records
```
Book.Title = Award.Title
```
Right Join — Retains all right records and matching left records
```
Book.Title = Award.Title
```
Full Outer Join — Retains all records from both tables, filling mismatches with nulls
```
Book.Title = Award.Title
```
Create Join Calculation... — Builds an ad-hoc calculation to match join keys
```
[Book ID1] + [Book ID2]
```

Comparison Table

Join Type	Retained Left Rows	Retained Right Rows
Inner	Only matching rows	Only matching rows
Left	All rows	Only matching rows
Right	Only matching rows	All rows
Full Outer	All rows	All rows

Common Pitfalls

Mistake: Trying to configure traditional joins on the top canvas. Avoid: Double-click the table icon to drill into the physical layer first.
Mistake: Assuming join keys must always match single existing columns. Avoid: Use join calculations to concatenate or manipulate split keys.
Mistake: Choosing Full Outer join without accounting for missing data. Avoid: Inspect the preview pane to handle resulting null attributes appropriately.

FAQs

How do I know whether I am in the logical layer or the physical layer? The logical layer displays flexible relationship noodles between table icons, while the physical layer shows an enclosed border with classic Venn-diagram join symbols.
What happens when a Left Join finds no match in the right table? Tableau retains the left table row and inserts Null values for all incoming right table columns.
Can I join tables on multiple fields or calculated fields? Yes, you can add multiple join clauses and write custom join calculations directly in the join configuration window.

Common pitfalls

Mistake: Trying to configure traditional joins on the top canvas. Avoid: Double-click the table icon to drill into the physical layer first.
Mistake: Assuming join keys must always match single existing columns. Avoid: Use join calculations to concatenate or manipulate split keys.
Mistake: Choosing Full Outer join without accounting for missing data. Avoid: Inspect the preview pane to handle resulting null attributes appropriately.

Hi there, my name's Henry and I'm a member of The Data School's 27th cohort at The Information Lab. This video is the second part of a four-part series on combining data. Er in this video, I'm going to show you how to combine data using joins in Tableau. These are a really useful way of combining data so you can analyze across multiple tables. So, let's jump into Tableau and get started. So, I'm connected again to the Bookshop data set, which we were using in our the first video in this series, which is on relationships. If you haven't checked that out already, I recommend you go and watch that before carrying on here with this video. However, if you want to carry on watching, you can still find some useful information here about how to join data together. Okay, so after we created er these relationships in our first video of this series, now what we want to do is er carry on using this Bookshop data set, but show you how to do some joins, too. So, relationships happen here in the logical layer of Tableau's data model. And we know that we're in the logical layer because we can see the noodles which represent our relationships between our tables. Now, joins happen in the physical layer of Tableau's data model. So, to enter the physical layer, we will double-click on this Book table here. And you can see now this border has opened up, and you can see the Book table looks slightly different there. We're now operating in the physical layer of Tableau's data model. So, what we want to do is we want to join information about the er awards that are available and the awards which each book has won. We want to join those onto the information that we have about our books. So, if we drag in the Award table, and just drop it into the physical layer there, we can see Tableau gives us our join symbol here. It set up the join for us. If we click on that, we can see that er to do this join, we need er just like in relationships, common fields across both tables. We can see here that Title is a common field across the Award table and the Data Source table. Now, there are different types of joins that we can choose from, er Inner, Left, Right, and Full Outer. And if you don't quite know which one to choose, then er it might be helpful to think about er these symbols here, and what they represent. So, you can think of each table as er as a circle, which are represented here, and the blue area colored in each of these Venn diagrams includes the information that you're going to get back as a result of the join. So, in an inner join, we would only have values that match each other from both the Book and Award table. If we chose a left join, we'd get all of the values from the Book table because that whole circle is colored, and we'd get only matching information from the Award table because the middle section of the Venn diagram is colored. If you choose right join, the opposite thing would happen. We'd get all of our information from the Award table, and only those books that match from the Book table. And finally, a full outer join would include all the information from both tables because both circles and the section in the middle are fully colored. And you can see here, if you hover over er that we will get um some nulls showing up, it's just telling you that that will happen, and that's totally fine. So, for this one, we're going to choose a left join, and we're going to press X to close that down. Now, I'm just going to show you one more method for joining which er might not be immediately obvious from looking at those drop-downs that we had earlier. I'm going to drag on the Info table, and I know from er inspecting this data that um the Book ID in the Book table is a combination of Book ID1 and Book ID2. So, even though we don't have Book ID as its complete field in the Info table, we can still join these tables together, and we do that using a join calculation. So, this is just something to be aware of, because we know that Book ID1 add Book ID2 equals Book ID. So, if we click on Apply, and hit OK, we can see here the result of our join. Er I'm just going to choose a left join for that, and close. So, we can see the result of our join. We can see Book ID from the Book table is a combination of two letters and three numbers. We can see here it has joined all of our values. We've got Book ID1 and Book ID2 is just a split of Book ID. So, adding those together concatenates these two strings, which equals this. So, Tableau knows that we've created the join using a correct join calculation. We press this X up here to close the physical layer. We can see our join represented by the Venn diagram there. And that is how you join tables together in Tableau. So, thanks for watching. I hope you feel a bit more confident in using joins now to combine data in Tableau. Please check the description of this video for links to additional materials that you might find useful. Tableau Tim has fantastic videos er which explain the Tableau data model, which I've linked below too. If you enjoyed this video and want to see how to set up a union in Tableau, then click on the thumbnail to the bottom right and watch the next video in this series. Subscribe to the channel to get notified as we release new videos.

Frequently asked questions

How do I know whether I am in the logical layer or the physical layer?

The logical layer displays flexible relationship noodles between table icons, while the physical layer shows an enclosed border with classic Venn-diagram join symbols.

What happens when a Left Join finds no match in the right table?

Tableau retains the left table row and inserts Null values for all incoming right table columns.

Can I join tables on multiple fields or calculated fields?

Yes, you can add multiple join clauses and write custom join calculations directly in the join configuration window.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.