What is the difference between merge and concat in pandas?

merge joins tables side by side on key columns. concat stacks tables on top of each other (or side by side by index) without matching keys.

Is a SQL JOIN the same as pandas merge?

Yes, conceptually. pandas merge supports the same inner, left, right and full outer joins, plus a cross join.

What does an anti join do?

It returns rows from the first table that have no match in the second, for example customers who never placed an order.

How to merge data in pandas, R and SQL

By the Analistable team · Updated 2026-10-07 · 3 min read

pandas, R and SQL all do the same two operations. Joining matches rows on a key: df1.merge(df2, on='id', how='left') in pandas, left_join(df1, df2, by = join_by(id)) in R, LEFT JOIN … ON in SQL. Stacking appends rows: pd.concat, bind_rows and UNION ALL. Pick the join type by which unmatched rows you want to keep.

The same join in three languages

Left join of customers and orders on customer_id
Language	Code
pandas	customers.merge(orders, on='customer_id', how='left')
R (base)	merge(customers, orders, by = 'customer_id', all.x = TRUE)
R (dplyr)	left_join(customers, orders, by = join_by(customer_id))
SQL	SELECT * FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id

Join types mapped across languages

Equivalent join arguments
Join	pandas how=	base R merge()	dplyr	SQL
Inner	'inner' (default)	default	inner_join	INNER JOIN
Left	'left'	all.x = TRUE	left_join	LEFT JOIN
Right	'right'	all.y = TRUE	right_join	RIGHT JOIN
Full outer	'outer'	all = TRUE	full_join	FULL OUTER JOIN
Anti	indicator=True, then filter	—	anti_join	LEFT JOIN … WHERE b.key IS NULL

Stacking (appending) rows

pandas: pd.concat([jan, feb, mar], ignore_index=True) aligns columns by name.
R: dplyr::bind_rows(jan, feb, mar) aligns by name and fills missing columns with NA.
SQL: SELECT … FROM jan UNION ALL SELECT … FROM feb aligns by position; UNION also removes duplicates.

Checks that prevent wrong results

Confirm key uniqueness before joining. In pandas, validate='one_to_many' raises an error if the left keys repeat.
Compare row counts before and after. Unexpected growth means duplicate keys.
Match types: an integer id never equals a string 'id'.
Rename clashing columns (pandas adds _x and _y suffixes).

Language guides

Without writing code

If the data starts in spreadsheets, Analistable loads it into an in-browser SQL engine (DuckDB) and writes the join for you from a plain-language question. You can read the SQL it generates, so it doubles as a way to learn the syntax.

Worked example: the same data, the same answer

Customers (C-101 Bakery Lune, C-102 Hart & Co, C-103 Nordic Supply) and Orders (O-9001 C-101 120, O-9002 C-103 75, O-9003 C-101 80, O-9004 C-104 60). A left join from customers, summed per customer, gives the same table in every language:

Total spend per customer (left join + group by)
name	orders	total
Bakery Lune	2	200
Hart & Co	0	0
Nordic Supply	1	75

(customers.merge(orders, on="customer_id", how="left")
          .groupby("name", as_index=False)
          .agg(orders=("order_id", "count"), total=("amount", "sum")))

customers |>
  left_join(orders, by = join_by(customer_id)) |>
  summarise(orders = sum(!is.na(order_id)), total = sum(amount, na.rm = TRUE), .by = name)

SELECT c.name, COUNT(o.order_id) AS orders, COALESCE(SUM(o.amount), 0) AS total
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name;

Order O-9004 (customer C-104) isn't in the result because the join starts from customers. Start from orders, or use a full outer join, to see it.

Which should you use?

SQL when the data already lives in a database, or when the logic should be readable by analysts across teams.
pandas for data pipelines in Python, files of any size that fit in memory, and when the next step is modelling or plotting in Python.
R / dplyr for statistical analysis and reporting, especially with the tidyverse.
None of them when the question is one-off and the data is a few spreadsheets: a tool that writes the SQL for you is faster.

Performance notes

All three handle millions of rows on a laptop, but in different ways: pandas and R hold the data in memory, so very large joins need enough RAM; databases join on disk and use indexes on the key columns. In any language, joining on clean, typed keys (integers or trimmed text) is faster and safer than joining on free text.

Every guide in this topic

How to combine two tables in SQL
Combine two tables in SQL side by side with JOIN or stacked with UNION and UNION ALL: syntax, examples, column rules and how to choose.
How to join multiple tables in SQL
Join three or more tables in SQL: chaining JOIN … ON, mixing inner and left joins safely, aggregating without double counting, and checking row counts.
How to merge data frames in R
Merge data frames in R with base merge() or dplyr joins: inner, left, full and anti joins, different key names, and stacking with rbind or bind_rows.
How to merge DataFrames in pandas
Merge two pandas DataFrames on a key with merge(): inner, left, outer and anti joins, different key names, validate, indicator, and when to use concat.

Frequently asked questions

What is the difference between merge and concat in pandas?: merge joins tables side by side on key columns. concat stacks tables on top of each other (or side by side by index) without matching keys.
Is a SQL JOIN the same as pandas merge?: Yes, conceptually. pandas merge supports the same inner, left, right and full outer joins, plus a cross join.
What does an anti join do?: It returns rows from the first table that have no match in the second, for example customers who never placed an order.