Connect any database. Ask in plain English.

Try free
← Blog

Pandas Merge: Every Join Type, Merging on Multiple Columns and the Mistakes That Multiply Rows (2026)

pandas merge run on pandas 3.1.1 against 4,734 invented orders: the seven join types with their SQL, merging on several columns, join, concat and merge_asof, and five mistakes with the output they print, from a merge that turns 12 rows into 36 to missing keys that match each other. Each SQL join was rerun in DuckDB 2.0 and returned the same rows.

Fastero Dev TeamFastero Dev Team2026-11-20

merge puts two tables side by side on a shared key: orders next to their product's category, this year's revenue next to last year's. It works like a SQL join, down to the way repeated keys multiply rows, with one difference SQL users don't expect: missing keys match each other.

The short answer: orders.merge(products, on="product", how="left") keeps every order and adds the product's columns, like SQL's LEFT JOIN. how can be "inner" (the default), "left", "right", "outer" or "cross", and since pandas 3.0 "left_anti" and "right_anti". Pass a list to on for several key columns, left_on and right_on when the names differ, suffixes for clashing columns, indicator=True to see where each row came from, and validate="many_to_one" to get an error instead of multiplied rows. join does the same against the other table's index; concat stacks tables instead of matching them. Every example ran on pandas 3.1.1 and Python 3.14.3 on 20 November 2026, with the output it printed.

Fastero

Connect your database. Ask questions. Get dashboards.

Postgres, BigQuery, Snowflake, and 10+ sources — live-connected, AI-powered, no dashboard builder learning curve.

Try free →

The data

The sample CSV every new Fastero workspace starts with: 4,734 invented orders from November 2024 to October 2026, split into orders and a product catalog whose list price is each product's median price, rounded:

import pandas as pd
 
sales = pd.read_csv("sample-sales.csv", parse_dates=["order_date"])
orders = sales.drop(columns="category")
products = (sales.groupby("product", as_index=False)
                 .agg(category=("category", "first"), unit_price=("unit_price", "median"))
                 .round({"unit_price": 0}))
print(orders.shape, products.shape)
print(products.head(3))
(4734, 9) (13, 3)
       product  category  unit_price
0    Cable kit  Supplies        26.0
1    Desk lamp  Hardware        48.0
2  Label rolls  Supplies        12.0

The join types, on five orders

Five orders and four catalog rows: one order (Training day) has no catalog row, and one catalog row (Standing desk) has no order.

orders_5 = orders.loc[:4, ["order_id", "product", "revenue"]]
products_4 = (products[products["product"].isin(["Team plan", "Starter plan", "Notebook set", "Standing desk"])]
              [["product", "category"]].reset_index(drop=True))
print(orders_5.merge(products_4, on="product", how="left"))
   order_id       product  revenue  category
0     10001     Team plan   680.28  Software
1     10002  Training day   903.73       NaN
2     10003  Starter plan   190.68  Software
3     10004  Notebook set    95.60  Supplies
4     10005  Notebook set    72.32  Supplies

Two small input tables, five orders and four catalog rows, above the results of pandas merge with how set to inner, left, right and outer, each row shaded by whether its key was in both tables, only the left or only the right, with NaN where pandas found no match

The same five orders and four catalog rows under each join type, as pandas 3.1.1 printed them. Invented sample data.

how= Keeps SQL Rows here
inner (default) Keys found in both tables JOIN 4
left Every left row LEFT JOIN 5
right Every right row RIGHT JOIN 5
outer Every row of both, keys sorted FULL OUTER JOIN 6
cross Every pair, no key CROSS JOIN 20
left_anti (3.0+) Left rows with no match ANTI JOIN (DuckDB) 1
right_anti (3.0+) Right rows with no match ANTI JOIN, tables swapped 1

Two things are easy to miss. In the right and outer joins order_id comes back as 10004.0: the Standing desk row has no order, and the gap turns the integers into floats. And left_anti keeps the right table's columns, all NaN, where SQL's anti join returns only the left table's.

The same joins in SQL

DuckDB (2.0.0 here) queries a pandas DataFrame by its variable name, so each join can be checked against SQL:

import duckdb
 
print(duckdb.sql("""
    SELECT order_id, product, revenue, category
    FROM orders_5 LEFT JOIN products_4 USING (product)
    ORDER BY order_id
""").df())

It printed the same five rows. A small helper then ran each join both ways and compared them:

inner        pandas rows:     4   DuckDB rows:     4   same rows: True
left         pandas rows:     5   DuckDB rows:     5   same rows: True
right        pandas rows:     5   DuckDB rows:     5   same rows: True
outer        pandas rows:     6   DuckDB rows:     6   same rows: True
cross        pandas rows:    20   DuckDB rows:    20   same rows: True
left_anti    pandas rows:     1   DuckDB rows:     1   same rows: True

At full size, 4,734 orders left-joined to the catalog, both gave the same rows too. pandas' SQL comparison page adds that MySQL has no FULL JOIN.

Merge on multiple columns

January to October 2025 against the same months of 2026, per region and segment:

def jan_oct(year):
    d = sales[(sales["order_date"].dt.year == year) & (sales["order_date"].dt.month <= 10)]
    return d.groupby(["region", "segment"], as_index=False)["revenue"].sum().round(0)
 
y25, y26 = jan_oct(2025), jan_oct(2026)
yoy = y25.merge(y26, on=["region", "segment"], suffixes=("_2025", "_2026"), validate="one_to_one")
yoy["change_pct"] = ((yoy["revenue_2026"] / yoy["revenue_2025"] - 1) * 100).round(1)
print(yoy.sort_values("change_pct").head(4))
   region         segment  revenue_2025  revenue_2026  change_pct
9    West      Enterprise       27465.0       23383.0       -14.9
3   North      Enterprise       23082.0       23771.0         3.0
2    East  Small business       75180.0       82361.0         9.6
10   West      Mid-market       57197.0       64520.0        12.8

Mistake 1: one key too few, and the rows multiply

Leave segment out of the keys and nothing fails:

wrong = y25.merge(y26, on="region", suffixes=("_2025", "_2026"))
print(len(y25), len(y26), len(wrong))
12 12 36

Each region appears three times on each side, so pandas pairs every 2025 segment with every 2026 segment: nine rows per region, six comparing the wrong segments. SQL does the same; DuckDB's join on region alone also returned 36 rows.

The East region's three 2025 rows and three 2026 rows merged on region alone give nine rows, six shaded because they pair different segments; merged on region and segment they give the correct three rows

Merging on one key too few: three rows on each side became nine, as pandas 3.1.1 printed them. Invented sample data.

validate turns it into an error, which since pandas 3.0 lists the first duplicate keys:

y25.merge(y26, on="region", suffixes=("_2025", "_2026"), validate="one_to_one")
pandas.errors.MergeError: Merge keys are not unique in either left or right dataset; not a one-to-one merge.
Duplicates in left:
 region
  East
  East
 North
 North
 South ...
Duplicates in right:
 region
  East
  East
 North
 North
 South ...

The options are "one_to_one", "one_to_many", "many_to_one" (or "1:1", "1:m", "m:1") and "many_to_many", which checks nothing. Orders against a catalog is "many_to_one".

Mistake 2: the key columns have different types

A refunds table we made up, with the order number stored as text and one refund (15020) for an order that doesn't exist:

refunds = pd.DataFrame({"order_ref": ["10002", "10417", "12950", "14731", "15020"],
                        "refund": [903.73, 726.64, 82.54, 193.44, 49.00]})
orders.merge(refunds, left_on="order_id", right_on="order_ref")
ValueError: You are trying to merge on int64 and str columns for key 'order_id'. If you wish to proceed you should use pd.concat

The key has a different name on each side, hence left_on and right_on; both columns stay in the result. Ignore the advice to use concat, which matches nothing: make the types agree with astype("int64"). DuckDB cast the text itself and returned the four refunds, and stopped on a value like 'A-10417' with "Could not convert string 'A-10417' to INT64".

Mistake 3: keys that don't match, and what indicator shows

indicator=True adds a _merge column: both, left_only or right_only. On the fixed refunds, an outer merge marked 4 rows both, 4,730 left_only and one right_only, refund 15020. A left merge hides gaps better, because it never drops a row. Here the catalog spells one product with a capital D:

catalog = products[["product", "category"]].copy()
catalog["product"] = catalog["product"].replace({"Standing desk": "Standing Desk"})
m = orders.merge(catalog, on="product", how="left", indicator=True)
print(m["_merge"].value_counts())
print(orders.merge(catalog, on="product", how="left_anti")["product"].value_counts())
_merge
both          4394
left_only      340
right_only       0
Name: count, dtype: int64
product
Standing desk    340
Name: count, dtype: int64

No error, just 340 orders with a blank category, which a later groupby("category") drops by default.

Connect any database. Ask in plain English.

Start free →

Mistake 4: duplicate column names, and leaving out on=

Both tables have a unit_price: the price paid and the list price.

print(list(orders.merge(products, on="product").columns))
print(orders.merge(products).shape)
['order_id', 'order_date', 'region', 'channel', 'segment', 'product', 'quantity', 'unit_price_x', 'revenue', 'category', 'unit_price_y']
(11, 10)

With on, the clash becomes unit_price_x and unit_price_y; suffixes=("_paid", "_list") gives better names. Without on, pandas joins on every shared column, product and unit_price, and only 11 orders whose price equalled the rounded list price survive.

Mistake 5: missing keys match each other

The pandas docs warn that null keys "will be matched against each other. This is different from usual SQL join behaviour". Two made-up tables:

a = pd.DataFrame({"product": ["Team plan", None], "orders": [455, 3]})
b = pd.DataFrame({"product": ["Team plan", None], "category": ["Software", "Unknown"]})
print(a.merge(b, on="product"))
print(duckdb.sql("SELECT * FROM a JOIN b USING (product)").df())
     product  orders  category
0  Team plan     455  Software
1        NaN       3   Unknown
     product  orders  category
0  Team plan     455  Software

pandas paired the two blanks; SQL didn't. Drop rows with a missing key first if you want SQL's result.

merge vs join vs concat

merge join concat
Matches on Columns, or an index with left_index / right_index The other table's index (a column of yours with on=) Nothing: it stacks
Default how="inner" how="left" join="outer" on the other axis
Best for Most joins Lookups keyed by index; several tables at once, as a list Appending files with the same columns

join merges against an index:

by_name = products.set_index("product")
print(orders.join(by_name, on="product", suffixes=("_paid", "_list"))
      [["order_id", "product", "unit_price_paid", "category", "unit_price_list"]].head(3))
   order_id       product  unit_price_paid  category  unit_price_list
0     10001     Team plan           170.07  Software            179.0
1     10002  Training day           903.73  Services            906.0
2     10003  Starter plan            63.56  Software             60.0

pandas 3.1 deprecated join's lsuffix and rsuffix for suffixes; passing them printed a Pandas4Warning, and passing nothing raised "columns overlap but no suffix specified".

concat stacks, like SQL's UNION ALL: the 2,187 orders of 2025 and 2,253 of 2026 became 4,440 rows, as DuckDB's UNION ALL counted too.

merge_asof: the price that applied on the day

A price list, here each product's median price in each 12-month half of the file, has two rows per product, so a plain merge doubles every order:

period = pd.to_datetime((sales["order_date"] >= "2025-11-01").map({False: "2024-11-01", True: "2025-11-01"}))
prices = (sales.assign(valid_from=period)
               .groupby(["product", "valid_from"], as_index=False)["unit_price"].median().round({"unit_price": 2})
               .rename(columns={"unit_price": "list_price"}))
print(len(prices), len(orders), len(orders.merge(prices, on="product")))
26 4734 9468

validate="many_to_one" catches it. merge_asof instead matches each order to the last price on or before its date, within its product; both tables must be sorted by date:

asof = pd.merge_asof(orders.sort_values(["order_date", "order_id"]), prices.sort_values("valid_from"),
                     left_on="order_date", right_on="valid_from", by="product")
cols = ["order_id", "order_date", "product", "unit_price", "valid_from", "list_price"]
print(len(asof), "\n", asof[(asof["product"] == "Team plan")
                             & asof["order_date"].between("2025-10-30", "2025-11-02")][cols])
4734 
       order_id order_date    product  unit_price valid_from  list_price
2096     12097 2025-10-30  Team plan      166.68 2024-11-01      178.89
2100     12101 2025-10-30  Team plan      167.16 2024-11-01      178.89
2104     12105 2025-10-31  Team plan      179.26 2024-11-01      178.89
2106     12107 2025-11-01  Team plan      170.26 2025-11-01      179.98
2110     12111 2025-11-02  Team plan      178.90 2025-11-01      179.98

One row per order, and the price switches on 1 November. DuckDB's ASOF JOIN returned the same 4,734 rows.

When the join belongs in the database

When both tables live in one database, join there and bring back only the result. Fastero is an AI and data platform where people and AI agents work together on a team's data, and in its notebooks that is a SQL cell:

  • SQL cells run on one of the workspace's connections with its stored credentials, and the result lands in Python as a pandas DataFrame named after the cell. The join runs in the database, and no password sits in the notebook.
  • Then pandas: merge that DataFrame with a CSV or Excel file uploaded to the project; notebooks read the project's files by path.
  • Schedules: run a cell, Run All, or a cron schedule, on the Free plan.
  • Pin to Dashboard: a SQL cell's rows, or the DataFrame a Python cell shows, becomes a dashboard widget that refreshes when the notebook runs on its schedule.
  • Sharing: colleagues sign in; viewers are free and can read a notebook but not run or save it.

A SQL cell in a Fastero notebook reading the sample sales database through a workspace connection into a variable named monthly, with its result table below

A Fastero notebook whose SQL cell reads a database connection; the rows arrive in Python as a DataFrame. Sample data.

More on SQL in a Jupyter notebook and joining CSV files with database tables in DuckDB.

FAQ

What is the difference between merge and join in pandas?

merge matches on columns and defaults to an inner join; join matches the other table's index and defaults to a left join. Use join when the lookup table is indexed by its key, merge otherwise.

How do I merge on multiple columns in pandas?

Pass a list: df1.merge(df2, on=["region", "segment"]). If the names differ, pass lists of equal length to left_on and right_on.

Why does my pandas merge have more rows than before?

The key repeats on both sides, so pandas returns every matching pair. Add the missing key column, or pass validate="many_to_one" so the merge raises an error instead.

How do I find the rows that didn't match?

Merge with how="outer", indicator=True and filter _merge for left_only or right_only. On pandas 3.0 or later, how="left_anti" returns the unmatched left rows.

How do I fix "You are trying to merge on int64 and str columns"?

The key is a number in one table and text in the other. Convert one side with astype("int64") and merge again; pd.concat, which the message suggests, won't match anything.


Related reading:

Sources

Read on 20 November 2026:


Try Fastero free: notebooks next to your team's databases, with SQL cells that hand pandas a DataFrame, and scheduled runs on the Free plan. No credit card required.

Ready to try it yourself?

Connect your database, ask questions in plain English, and get live dashboards — in under 2 minutes. No credit card required.

Follow Fastero on LinkedIn for new articles and examples.