Master Tableau From Scratch

Learn Tableau fundamentals, data connections, data preparation, filtering, data modeling, calculations, LOD expressions, visualizations, dashboards, and storytelling through structured practical lessons.

COURSE SYLLABUS

Tableau Physical Layer Joins

Learn how Tableau combines rows from multiple tables using physical joins, join types, matching fields, and practical examples.

1. What are Physical Layer Joins?

A physical join in Tableau combines data from two or more tables into a single logical table. It works inside Tableau's physical layer of the data model.

When you create a join, Tableau combines columns from the selected tables based on a matching condition. The resulting rows depend on the join type and the matching values in the tables.

Key Characteristics

Physical joins combine rows into one logical table. They use matching fields to determine which records are combined. Depending on the join type, unmatched records may be excluded or retained with null values. A join can also change the number of rows and affect aggregations.

2. Understanding the Physical Layer

Tableau's physical layer is where you define joins and unions. When tables are joined in this layer, Tableau creates a combined table that is treated as one logical table in the data model.

Customers Customer ID Customer Name Orders Order ID Customer ID JOIN Customer ID One Combined Logical Table

Physical joins combine columns and rows from multiple tables.

3. Example: Customers and Orders Tables

Consider two tables in a business database. The Customers table stores customer information, while the Orders table stores customer order details.

Customers Table

Customer ID Customer Name City
101RahulPune
102PriyaMumbai
103AmitNashik
104NehaNagpur

Orders Table

Order ID Customer ID Sales
50011015000
50021013000
50031027000
50041052000

Join Condition

Customers.Customer ID = Orders.Customer ID

Tableau compares Customer ID values in both tables to determine which rows should be combined. Customer 101 has two orders, Customer 102 has one order, Customer 103 and 104 have no matching orders, and Order 5004 has no matching customer.

4. Types of Joins in Tableau

Tableau supports four common join types: Inner Join, Left Join, Right Join, and Full Outer Join. Each type determines how matching and unmatched records are included in the result.

4.1 Inner Join

An Inner Join returns only records that have matching values in both tables. Records without a match are excluded.

A B Match Only matching records

Inner Join keeps the intersection of both tables.

Example: Only customers with matching orders appear.

4.2 Left Join

A Left Join returns all records from the left table and matching records from the right table. If no matching record exists in the right table, its columns contain null values.

A B All left records + matching right records

Left Join retains every record from the left table.

Example: All customers appear, including customers without orders.

4.3 Right Join

A Right Join returns all records from the right table and matching records from the left table. If no matching record exists in the left table, its columns contain null values.

A B All right records + matching left records

Right Join retains every record from the right table.

Example: All orders appear, including orders without matching customers.

4.4 Full Outer Join

A Full Outer Join returns all records from both tables. Matching records are combined, while unmatched records from either table are also retained with null values for missing columns.

A B All records from both tables

Full Outer Join retains matching and unmatched records.

Example: All customers and all orders appear, whether or not a matching record exists.

5. Join Types Comparison

Join Type Left Table Right Table Unmatched Records
Inner Join Matching records Matching records Excluded
Left Join All records Matching records Unmatched left records retained
Right Join Matching records All records Unmatched right records retained
Full Outer Join All records All records Both sides retained

6. How to Create a Physical Join in Tableau

Follow these steps to join two tables using Tableau Desktop.

  1. Open Tableau Desktop.
  2. Connect to your database or supported data source.
  3. Open the Data Source page.
  4. Drag the first table onto the logical canvas.
  5. Double-click the logical table to open its physical layer.
  6. Drag the second table into the physical canvas.
  7. Tableau displays a join between the two tables.
  8. Click the join icon to select a join type.
  9. Select the matching field from each table.
  10. Review the preview to check the resulting data.
Customers First table Orders Second table JOIN Combined Table Review rows and matching fields

Drag tables into the physical layer, configure the join, and inspect the result.

7. Understanding Duplicate Rows

Physical joins can increase the number of rows when matching keys are repeated in either table. This commonly happens in one-to-many or many-to-many relationships.

For example, if one customer has three orders, a join returns three rows containing that customer's information. This is expected behavior, but it can affect calculations if the aggregation is not designed for the resulting level of detail.

Example

Customer Order ID Sales
Rahul50015000
Rahul50023000
Rahul50052000

Rahul appears three times because three order records match the same Customer ID. The customer information is repeated across the joined rows.

Important: Repeated dimension values are not automatically errors. However, summing a measure from a table whose values are repeated by the join can produce inflated totals. Always validate aggregations against the original tables.

8. Common Issues and Best Practices

Common Issues

Incorrect join fields, incompatible data types, null values, duplicate keys, and unexpected join types can all produce unexpected results. A join may also increase or reduce the number of rows depending on the data and join condition.

Best Practices

Before creating a join, understand the level of detail in both tables. Verify the matching fields, inspect duplicate and null values, choose the correct join type, and compare the output with the original data. Validate important totals and aggregations before using the joined data in dashboards.

Summary

Physical joins combine rows from multiple tables into one logical table in Tableau. The four main join types are Inner, Left, Right, and Full Outer Join. Each join type controls how matching and unmatched records are handled. Duplicate matching keys can increase the number of rows, while unmatched records may be excluded or retained with null values depending on the join type. Understanding table granularity, join conditions, and aggregation behavior helps you build accurate Tableau data models.