Union and join basics in Tableau - Deep Dive

Overview - Union and join basics

What is it?

Union and join are ways to combine data from two or more tables in Tableau. A union stacks tables on top of each other, adding rows. A join connects tables side-by-side, adding columns based on matching values. These methods help create a complete dataset for analysis.

Why it matters

Without union and join, data from different sources or tables would stay separate, making it hard to analyze together. They solve the problem of combining related data to get a full picture, like merging sales and customer info. Without them, reports would be incomplete or require manual work.

Where it fits

Before learning union and join, you should understand basic data tables and fields. After mastering these, you can explore advanced data blending, relationships, and calculated fields in Tableau.

Mental Model

Core Idea

Union stacks data tables vertically by adding rows, while join connects tables horizontally by matching columns.

Think of it like...

Imagine stacking two decks of cards (union) versus lining up two decks side-by-side by matching card suits (join).

┌─────────────┐   Union   ┌─────────────┐
│ Table A     │──────────▶│ Combined    │
│ Row 1       │          │ Table (Rows)│
│ Row 2       │          │             │
└─────────────┘          │ Row 1 (A)   │
┌─────────────┐          │ Row 2 (A)   │
│ Table B     │──────────▶│ Row 1 (B)   │
│ Row 1       │          │ Row 2 (B)   │
│ Row 2       │          └─────────────┘
└─────────────┘

┌─────────────┐   Join    ┌─────────────┐
│ Table A     │──────────▶│ Combined    │
│ ID | Name   │          │ ID | Name   │
└─────────────┘          │ Age | City  │
┌─────────────┐          └─────────────┘
│ Table B     │──────────▶
│ ID | Age    │
│ ID | City   │
└─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Tables and Rows

Concept: Learn what tables and rows are in data and how they store information.

A table is like a spreadsheet with rows and columns. Each row is one record, like one customer or sale. Columns are fields, like name or date. Understanding this helps you see how data is organized before combining it.

Result

You can identify rows and columns in any dataset and understand their role.

Knowing the basic structure of tables is essential before combining data, so you know what you are stacking or matching.

2

Foundation

What is a Union in Tableau

Concept: Union stacks tables vertically by adding rows from each table.

When you union tables, Tableau puts one table's rows below another's. The columns must be similar or compatible. For example, two sales tables from different months can be unioned to get all sales together.

Result

A new table with all rows from both tables combined vertically.

Understanding union helps you combine similar datasets to analyze them as one continuous list.

3

Intermediate

What is a Join in Tableau

Concept: Join connects tables horizontally by matching rows based on common fields.

A join links tables side-by-side using a key column like Customer ID. Tableau matches rows where the key is the same and combines their columns. Types include inner join (only matches), left join (all from left), right join, and full outer join.

Result

A wider table with columns from both tables combined based on matching keys.

Knowing joins lets you enrich data by bringing related information together from different tables.

4

Intermediate

Differences Between Union and Join

🤔Before reading on: Do you think union adds columns or rows? Does join add rows or columns? Commit to your answer.

Concept: Union adds rows stacking tables; join adds columns matching rows.

Union stacks tables vertically, so columns stay the same but rows increase. Join combines tables horizontally, so rows stay the same or change based on matching, but columns increase. They serve different purposes.

Result

Clear understanding of when to use union vs join.

Distinguishing union and join prevents mixing them up and applying the wrong method for your data needs.

5

Advanced

Handling Schema Differences in Union

🤔Before reading on: What happens if tables have different columns when unioned? Will Tableau keep all columns or only matching ones?

Concept: Tableau includes all columns from both tables in a union, filling missing values with nulls.

If tables have different columns, Tableau creates a combined table with all columns. Rows from a table missing a column get null in that column. This lets you union tables with similar but not identical schemas.

Result

A unioned table with all columns present, some rows having nulls where data is missing.

Knowing how Tableau handles schema differences helps avoid surprises and plan data cleaning.

6

Advanced

Join Types and Their Effects

🤔Before reading on: Does an inner join keep unmatched rows or discard them? What about left join?

Concept: Different join types control which rows appear based on matching keys.

Inner join keeps only rows with matching keys in both tables. Left join keeps all rows from the left table, adding matching data or nulls if no match. Right join and full outer join behave similarly but differ in which rows they keep.

Result

Understanding join types helps you control data completeness and avoid losing important rows.

Choosing the right join type is critical to get accurate and meaningful combined data.

7

Expert

Performance and Data Quality Considerations

🤔Before reading on: Do you think large joins or unions always perform well in Tableau? Why or why not?

Concept: Large unions and joins can slow down Tableau and cause data quality issues if keys or schemas are inconsistent.

Unions with many rows increase data size and can slow dashboards. Joins on non-unique keys can cause duplicate rows or incorrect aggregations. Cleaning data and indexing keys before combining improves performance and accuracy.

Result

Better performing Tableau workbooks with reliable combined data.

Understanding performance and data quality helps you design efficient and trustworthy data models.

Under the Hood

Tableau processes unions by appending rows from each table into a single data source, aligning columns by name. For joins, Tableau matches rows based on join keys using SQL-like operations, creating a combined table with columns from both sources. It pushes these operations to the data engine or source database for efficiency.

Why designed this way?

Unions and joins mimic SQL operations familiar to analysts, enabling flexible data combination. Tableau's design pushes these operations to the fastest layer available, balancing ease of use with performance. Alternatives like data blending exist but are less integrated.

┌─────────────┐       ┌─────────────┐
│ Table A     │       │ Table B     │
│ Columns:    │       │ Columns:    │
│ ID, Name    │       │ ID, Age     │
└─────┬───────┘       └─────┬───────┘
      │                     │
      │ Join on ID          │
      ▼                     ▼
┌───────────────────────────────┐
│ Joined Table                  │
│ Columns: ID, Name, Age        │
│ Rows: matched by ID           │
└───────────────────────────────┘


┌─────────────┐       ┌─────────────┐
│ Table A     │       │ Table B     │
│ Columns:    │       │ Columns:    │
│ ID, Name    │       │ ID, Name    │
└─────┬───────┘       └─────┬───────┘
      │                     │
      │ Union stacks rows    │
      ▼                     ▼
┌───────────────────────────────┐
│ Unioned Table                 │
│ Columns: ID, Name             │
│ Rows: all rows from A and B   │
└───────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does union combine tables by matching columns or stacking rows? Commit to your answer.

Common Belief: Union matches columns side-by-side like a join.

Reality: Union stacks tables vertically, adding rows, not columns.

Why it matters: Confusing union with join leads to wrong data structure and analysis errors.

Quick: Does a join always keep all rows from both tables? Commit to yes or no.

Common Belief: Joins always keep all rows from both tables.

Reality: Only full outer joins keep all rows; inner joins keep only matching rows.

Why it matters: Using the wrong join type can cause missing data or duplicates in reports.

Quick: If tables have different columns, does union drop unmatched columns? Commit to yes or no.

Common Belief: Union only keeps columns present in both tables.

Reality: Union keeps all columns from both tables, filling missing values with nulls.

Why it matters: Not knowing this can cause confusion about nulls appearing after union.

Quick: Can joining on non-unique keys cause duplicate rows? Commit to yes or no.

Common Belief: Joins always produce one row per key.

Reality: Joining on non-unique keys can multiply rows, causing duplicates.

Why it matters: This can inflate data counts and mislead analysis if not handled carefully.

Expert Zone

1

Tableau's union operation is case-insensitive on column names, which can cause unexpected merges if columns differ only by case.

2

Joins in Tableau can be pushed down to the data source or processed in Tableau's engine depending on connection type, affecting performance.

3

Using wildcard unions allows combining many files or tables automatically, but requires consistent schema to avoid null proliferation.

When NOT to use

Avoid unions when tables have very different schemas or when you need to combine data with complex relationships better handled by joins or data blending. Avoid joins on non-unique keys without aggregation to prevent row multiplication. Use data blending or relationships for loosely related data.

Production Patterns

In production, unions are often used to combine monthly or regional data files into one dataset. Joins enrich fact tables with dimension tables like customers or products. Experts optimize joins by indexing keys and minimizing data before joining to improve dashboard speed.

Connections

SQL Data Manipulation

Union and join in Tableau directly correspond to SQL UNION and JOIN operations.

Understanding SQL helps grasp how Tableau combines data under the hood and write custom queries when needed.

Relational Database Theory

Joins are based on relational algebra concepts from database theory.

Knowing relational theory clarifies why join types behave as they do and how to design efficient data models.

Spreadsheet Data Consolidation

Union is like copying and pasting rows from one sheet below another; join is like using VLOOKUP to add columns.

Relating Tableau operations to spreadsheet tasks helps beginners transition smoothly to BI tools.

Common Pitfalls

#1 Combining tables with different column names using union without checking schema.

Wrong approach:

Drag two tables with different column names into union without renaming or aligning columns.

Correct approach:

Rename columns to match or accept nulls where columns differ, then union.

Root cause:

Assuming union requires identical columns without nulls leads to unexpected missing data.

#2 Joining tables on a non-unique key causing duplicate rows.

Wrong approach:

Join sales and customers on customer name without ensuring uniqueness.

Correct approach:

Join on unique customer ID or aggregate data before joining.

Root cause:

Not verifying key uniqueness causes row multiplication and inflated results.

#3 Using inner join when all rows from one table are needed.

Wrong approach:

Inner join sales and customers, losing sales with no customer record.

Correct approach:

Use left join to keep all sales even if customer info is missing.

Root cause:

Misunderstanding join types leads to data loss.

Key Takeaways

Union stacks tables vertically by adding rows, while join connects tables horizontally by matching columns.

Choosing the right join type controls which rows appear and prevents data loss or duplication.

Tableau unions keep all columns from both tables, filling missing data with nulls.

Joining on non-unique keys can multiply rows and distort analysis.

Understanding these basics is essential for combining data accurately and building reliable Tableau dashboards.

Practice

1. Which of the following best describes a Union in Tableau?

easy

A. It filters data based on a condition.

B. It combines columns from two tables based on matching keys.

C. It creates a new calculated field.

D. It stacks rows from two or more tables with similar columns.

Solution

1. Step 1: Understand Union purpose

   A Union stacks rows from tables that have the same columns, combining data vertically.

2. Step 2: Compare with Join

   Join combines columns horizontally based on matching keys, not stacking rows.

3. Final Answer:

   It stacks rows from two or more tables with similar columns. -> Option D

4. Quick Check:

   Union = stacking rows [OK]

Hint: Union stacks rows; Join combines columns [OK]

Common Mistakes:

• Confusing Union with Join
• Thinking Union combines columns
• Assuming Union filters data

2. Which syntax correctly represents a Join in Tableau's data source page?

easy

A. Drag one table onto another and select the join type.

B. Use the UNION keyword in the data pane.

C. Create a calculated field with JOIN() function.

D. Right-click a table and select 'Append'.

Solution

1. Step 1: Identify Join method in Tableau

   In Tableau, you join tables by dragging one table onto another and choosing the join type (Inner, Left, Right, Full).

2. Step 2: Eliminate incorrect options

   Union keyword is for unions, JOIN() function does not exist, and 'Append' is not a Tableau option.

3. Final Answer:

   Drag one table onto another and select the join type. -> Option A

4. Quick Check:

   Join = drag tables + select join [OK]

Hint: Join by dragging tables together in Tableau [OK]

Common Mistakes:

• Confusing Union keyword with Join
• Looking for JOIN() function which doesn't exist
• Using 'Append' instead of Union or Join

3. Given two tables:
Table A: ID, Name
Table B: ID, Sales
What will be the result of an Inner Join on ID?

medium

A. A table with only IDs from Table A and no Sales column.

B. A table stacking all rows from both tables vertically.

C. A table with columns ID, Name, Sales containing only matching IDs.

D. A table with all IDs from both tables, including unmatched rows.

Solution

1. Step 1: Understand Inner Join behavior

   Inner Join returns rows where the join key (ID) exists in both tables, combining columns.

2. Step 2: Analyze columns and rows

   The result will have columns ID, Name, Sales and only rows with matching IDs in both tables.

3. Final Answer:

   A table with columns ID, Name, Sales containing only matching IDs. -> Option C

4. Quick Check:

   Inner Join = matching rows + combined columns [OK]

Hint: Inner Join keeps only matching keys with all columns [OK]

Common Mistakes:

• Thinking Inner Join stacks rows
• Expecting unmatched rows included
• Confusing columns included in result

4. You tried to Union two tables but got an error. What is the most likely cause?

medium

A. You forgot to select a join type.

B. The tables have different column names or data types.

C. You used a calculated field instead of a table.

D. You dragged tables instead of using the Union option.

Solution

1. Step 1: Identify Union requirements

   Union requires tables to have matching column names and compatible data types to stack rows.

2. Step 2: Understand error cause

   If columns differ in name or type, Tableau cannot stack rows, causing an error.

3. Final Answer:

   The tables have different column names or data types. -> Option B

4. Quick Check:

   Union error = mismatched columns/types [OK]

Hint: Union needs matching columns and types [OK]

Common Mistakes:

• Confusing Union errors with Join errors
• Not checking column names and types
• Trying to drag tables instead of using Union

5. You have monthly sales data split into two tables: Sales_Jan and Sales_Feb with identical columns. You also have a Customers table with customer details. How do you create a combined dataset showing all sales with customer info?

hard

A. Union Sales_Jan and Sales_Feb, then Join the result with Customers on CustomerID.

B. Join Sales_Jan with Customers, then Union with Sales_Feb.

C. Union Customers with Sales_Jan and Sales_Feb directly.

D. Join Sales_Jan and Sales_Feb on CustomerID, then Union with Customers.

Solution

1. Step 1: Combine monthly sales data vertically

   Since Sales_Jan and Sales_Feb have identical columns, use Union to stack their rows into one sales table.

2. Step 2: Enrich sales data with customer info

   Join the combined sales table with Customers on CustomerID to add customer details horizontally.

3. Final Answer:

   Union Sales_Jan and Sales_Feb, then Join the result with Customers on CustomerID. -> Option A

4. Quick Check:

   Union sales first, then Join customers [OK]

Hint: Union similar tables first, then Join related tables [OK]

Common Mistakes:

• Joining before unioning sales tables
• Trying to union unrelated tables
• Joining sales tables instead of unioning