Merge Several Tables: How to Combine Data

Merging several tables means combining data from two or more tables into one, either by stacking rows (union) or by joining columns based on a common key (join). This article explains the two main approaches, when to use each, and how to avoid common mistakes.

Formula

For a simple union (stacking tables vertically), the total number of rows in the merged result is:

Total rows = (rows in table 1) + (rows in table 2) + … + (rows in table n)

If all tables have the same number of rows r, then Total rows = n × r.

For a join (combining columns), the result depends on the join type and the keys. The formula is not a simple multiplication; it depends on how many rows match between tables.

Worked Example

Using the default values from the calculator above: 3 tables, each with 100 rows.

• Total rows = 3 × 100 = 300

So a union of three tables with 100 rows each gives 300 rows total.

Background: Why Merge Tables?

Merging tables is a fundamental data operation. You might have sales data for different regions in separate files, customer information in one table and orders in another, or survey responses split across multiple exports. Combining them into a single table makes analysis, reporting, and visualization easier.

Two Core Approaches: Union vs. Join

There are two fundamentally different ways to merge tables:

• Union (stacking): Adds rows from one table below another. Used when tables have the same columns (e.g., sales from January, February, March).
• Join (side-by-side): Adds columns from one table to another based on a matching key. Used when tables have different columns but share an identifier (e.g., customer ID).

How to Choose the Right Merge Method

Ask yourself two questions:

1. Do the tables have the same columns? If yes, you likely want a union.
2. Do the tables have different columns but share a common key? If yes, you likely want a join.

If you need both, do the union first, then the join.

Common Problems and How to Avoid Them

• Column mismatch: Ensure column names and data types align before a union. In SQL, use UNION which requires same number of columns.
• Duplicate rows: A union (not UNION ALL) removes duplicates. If you want to keep all rows, use UNION ALL.
• Key collisions: When joining, a single key in one table may match multiple rows in another, causing row multiplication. Check for duplicates in your key columns.
• Case sensitivity: Keys like ‘abc’ and ‘ABC’ may not match depending on your database. Normalize keys before merging.

Merging in Different Tools

Merging is possible in most data tools:

• SQL: Use UNION or JOIN statements.
• Excel: Use Power Query (Get & Transform) to append or merge queries.
• Python (pandas): Use pd.concat() for union and pd.merge() for joins.
• R: Use rbind() for union and merge() for joins.

Frequently Asked Questions

What is the difference between UNION and UNION ALL?

UNION removes duplicate rows from the combined result; UNION ALL keeps all rows, including duplicates. UNION ALL is faster because it doesn’t need to check for duplicates.

Can I merge tables with different numbers of columns?

For a union, no – you need the same number of columns (or explicitly select matching columns). For a join, yes – the join only requires a common key, not the same columns.

What is a full outer join?

A full outer join returns all rows from both tables, filling in NULLs where there is no match. It is useful when you want to keep every record from both sides.

How do I avoid duplicate data when merging?

Use UNION instead of UNION ALL, or remove duplicates before merging. For joins, ensure your key is unique in at least one table.

What if my tables have no common key?

Without a common key, you cannot perform a meaningful join. You can still union them if they have the same columns, or you may need to create a key based on row position or other attributes.

Is merging the same as concatenating?

Concatenating usually refers to stacking rows (union). Merging is a broader term that can include both union and join operations.

Related

• tmux: Organize Multiple Terminal Tasks in One Workspace
• High Mileage Engine Oil Additives: What Works
• United Airlines AirTag Share Item Location: How It Works

in t | Number of tables | - | 3 | 1 | 10
in r | Rows in each table | - | 100 | 1 | 100000
out total | Total rows after union | - | t * r | 0