Left, Right & Outer Joins
Python · Combining DataFrames (SQL Joins & Unions)
Theory
The how argument picks the join type. how='left' keeps every row of the left table and fills columns from the right with NaN where there is no match - the usual choice for 'all customers, with their orders if any'. how='right' is the mirror image, and how='outer' keeps every key from both sides (SQL FULL OUTER JOIN). Row order: left joins keep the left table's order; outer joins sort the keys.
indicator=True adds a _merge column saying 'both', 'left_only' or 'right_only' for each row, which is the quickest way to audit a join. validate='one_to_one', 'one_to_many' or 'many_to_one' makes pandas raise a MergeError if the keys are not as unique as you expect, catching accidental row explosions before they corrupt totals. Note that an int column that gains NaN from a left join turns into float64.
Why we use it
Business questions often need unmatched rows: customers who never ordered, products with no sales, orders with a missing customer. Left and outer joins keep those rows, and indicator and validate show you exactly what matched.
When to use it
Use left when the left table is the 'master list' you must not lose, outer when reconciling two sources, and right rarely (swap the tables and use left instead). Add validate= whenever one side should have unique keys.
How it works
- left.merge(right, on='key', how=...)
- Match keys between the two tables
- Key only in the left table? Yes: Kept for left/outer, NaN right cols. No: Next check.
- Key only in the right table? Yes: Kept for right/outer, NaN left cols. No: Matched row.
- Optional: _merge column (indicator) and key checks (validate)
- Joined DataFrame
Syntax
left.merge(right, on='key', how='left' | 'right' | 'outer')
left.merge(right, on='key', how='outer', indicator=True)
left.merge(right, on='key', validate='one_to_many')Example
all_cust = customers.merge(orders, on='cust_id', how='left')
audit = customers.merge(orders, on='cust_id', how='outer', indicator=True)Key points
- left keeps all left rows; right keeps all right rows; outer keeps all keys
- Unmatched columns are NaN, so int columns can turn into float64
- indicator=True adds _merge: both / left_only / right_only
- validate='one_to_many' raises MergeError if the 'one' side has duplicate keys
- SQL equivalent: LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN