Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 35 ยท Connecting to Data

Tableau Joins, Relationships, Unions and Inconsistent Labels

Combine tables in Tableau: inner, left, right and full joins, relationships in the logical layer, unions, cross-database joins and join calculations for inconsistent labels.

Real data lives in several tables: orders in one, customers in another, returns in a third. Tableau gives you three ways to combine them: relationships (the modern default), joins and unions. A fourth technique, data blending, works across separate data sources and has its own lesson: Data blending and cleaning.

Choosing the right tool

flowchart TD
    Q["I have more than one table"] --> A{"Same columns,<br/>just more rows?"}
    A -->|"Yes"| U["Union"]
    A -->|"No"| B{"Do I need full control of<br/>which rows match?"}
    B -->|"Yes"| J["Join"]
    B -->|"No, tables have<br/>different levels of detail"| R["Relationship"]

Union stacks rows. Joins and relationships add columns, and relationships decide how to combine at query time.

Joins

Drag a second table next to the first on the Data Source canvas and a join appears (a Venn diagram icon). Click it to set the type and the join clause: one or more pairs of fields that must be equal.

Join type Rows kept
Inner Only rows with a match in both tables
Left All rows of the left table plus matching rows of the right (non-matches get Null)
Right All rows of the right table plus matching rows of the left
Full outer All rows from both tables (not offered by every database)

Three consequences are worth memorising:

  1. Inner joins drop data. Orders without a customer row vanish silently.
  2. Joins can multiply rows. If one order matches three rows in the right table, the order appears three times and its Sales is triple-counted. Always check the grain.
  3. Left is the safe default when you want to keep every row of the main table.

Try it: inner and left joins (stand-in)

This SQL stand-in mirrors what the join icon does. The returns table only lists orders that were returned.

SQL Stand-in: inner join versus left join

Press Run (or Ctrl + Enter) to see the result here.

The inner join returns two rows; the left join keeps all four orders and shows NULL for orders 1 and 3. Order 9 exists only in returns, so only a right or full join would show it.

Joining on several fields

A join clause can have more than one pair, for example Order ID = Order ID and Product ID = Product ID. All pairs must match, like AND in SQL. To use a calculation, choose Create Join Calculation... in the drop-down of the join field.

Joining data with inconsistent labels

Often the keys almost match: "East " with a trailing space, "east" versus "EAST", or "United States" versus "USA". A join on raw values loses those rows. Fix this inside the join with a join calculation on each side, such as:

TEXT
UPPER(TRIM([Region]))

For spelling variants, the stronger fix is a small mapping table: one column with the messy value, one with the clean value. Join the messy side to the mapping and use the clean column afterwards. Another option is a Group on the field (Lesson Groups, sets, hierarchies and parameters) for a handful of corrections.

SQL Stand-in: a join calculation that cleans the key

Press Run (or Ctrl + Enter) to see the result here.

Only Central matches the raw join; all four rows match after cleaning. In Tableau you would put UPPER(TRIM([Region])) in the join calculation dialog on both sides.

Relationships

Since Tableau 2020.2, dragging a table to the canvas creates a relationship (a flexible line, sometimes called a noodle) instead of a join. The canvas then has two layers:

  • The logical layer (default): tables stay separate, linked by relationships. Tableau decides at query time, per view, which tables to join and how, using only the tables the view needs.
  • The physical layer (double-click a table): the classic canvas for joins and unions, where tables merge into one.

Advantages of relationships:

  • No row duplication when tables have different levels of detail. Customers and Orders can stay at their own grain, and aggregates stay correct.
  • Context-aware joins: a view that uses only Orders fields queries only Orders.
  • Unmatched values are kept (the default behaves like a full outer join across the used tables), so you do not lose dimension members that have no facts.
  • You can set the performance options (cardinality and referential integrity) in the relationship pane. The defaults are safe. Set them to "One to many" and "All records match" only when you know that is true.

Relationships have limits: you cannot create a relationship using a join calculation of aggregated fields, and some data sources (for example some published or legacy sources) do not support them. Use a join in the physical layer for those cases.

When in doubt: start with relationships. Use joins when you need a specific join type or a calculation on the key, or when the data comes from a published source that only supports joins.

Unions

A union stacks tables with the same columns into one tall table. Typical cases: monthly files with identical structure (sales_jan.csv, sales_feb.csv), or one sheet per region.

  • On the canvas, drag a table onto New Union, then drop others onto it.
  • Use Wildcard (automatic) to include every file or sheet whose name matches a pattern (sales_*.csv).
  • Tableau adds two helper fields, Sheet (or Table Name) and Path, so you can tell which source each row came from.
  • Columns with different names do not line up; merge them with the Merge mismatched fields option in the union settings.
SQL Stand-in: union of two monthly tables

Press Run (or Ctrl + Enter) to see the result here.

The first column plays the role of Tableau's added Table Name field.

Cross-database joins

You can join tables from different connections, for example a CSV with a SQL Server table. Tableau adds the second connection in the left pane, and the join runs inside Tableau, so it is slower and best used on small tables. For big data, load both into one database or use a relationship.

Common mistakes

  • Inner join by habit, which hides unmatched rows.
  • Joining on a non-unique key and doubling totals (check SUM(Sales) against the source).
  • Joining text keys with different case or spaces without cleaning them.
  • Using a union for different structures. Tables with different columns need a join.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which join keeps every row of the left table, even without a match?

    A left join keeps all left-table rows and fills the right-side fields with Null when there is no match.
  2. 2. Why can a join double-count Sales?

    When one row matches several rows in the other table, the join repeats it. Relationships avoid this by keeping each table at its own level of detail.
  3. 3. What is the main benefit of a relationship over a join?

    Relationships live in the logical layer and are resolved at query time, so they avoid duplication and only query needed tables.
  4. 4. You have twelve monthly CSV files with identical columns. What do you use?

    A union stacks files with the same structure, and a wildcard includes new files automatically.
  5. 5. How do you join 'East ' with 'EAST'?

    A join calculation normalizes both keys before comparing them.

Summary

  • Relationships are the modern default: they keep tables separate and combine them per view.
  • Joins (inner, left, right, full outer) merge tables into one; check the grain to avoid duplication.
  • Fix inconsistent labels with join calculations or a mapping table.
  • Unions stack tables with the same columns, and wildcards pick up new files.
  • Cross-database joins work but run in Tableau, so keep them small.

Next: Data blending and cleaning