Joining Datasets: Inner, Left, Right, and Outer Joins

Your data rarely lives in one table. Orders here, customers there, products somewhere else. A join stitches them together on a shared key — and the join type you pick decides which rows survive.

Note

A join needs a key: a column both tables share (customer_id, order_id). Get the key right and everything else follows.

The Four Join Types

Join	Keeps
Inner	Only rows with a match in both tables
Left	All left rows; matched right values, else blank
Right	All right rows; matched left values, else blank
Outer	All rows from both, matched where possible

Most day-to-day work is a left join: keep every record on your main table, enrich it with details from another.

Cardinality and the Fan-out Trap

If a key repeats on the right table, a one-to-many join multiplies your rows. Join a customer to their 10 orders and one customer becomes 10 rows — quietly inflating any total you compute afterward.

Watch out

Always know whether your key is unique. A duplicated key is the number-one cause of "why did my row count explode?"

Common Mistakes

Using an inner join and silently dropping unmatched rows
Joining on a non-unique key and fanning out the data
Mismatched key types or formats ("007" vs 7) so nothing matches
Duplicate column names colliding after the merge

Merging in the SKARI Data Editor

SKARI's Data Editor merges datasets visually: pick the two tables, choose the key and the join type, and preview the result before committing.

Inner, left, right and outer joins from a simple dialog
Row counts shown so you can catch a fan-out immediately
The merge is recorded in the Pipeline history for reproducibility

Takeaway

You see the resulting row count up front — no surprise explosions downstream.

Frequently Asked Questions

Which join should I default to?

Left join from your main table — you keep every record and add detail, without silently losing rows.

Why did my totals get bigger after a join?

A one-to-many join duplicated rows. Aggregate the many-side first, or confirm the key is unique before joining.

Nothing matched — what went wrong?

Usually a key type or format mismatch. Make both keys the same type and clean whitespace or leading zeros first.

Conclusion

Joining is simple once you know your keys and cardinality. Choose the join type on purpose, watch the row count, and your combined dataset stays trustworthy.

Takeaway

Know the key, pick the join, check the count — and merging never surprises you.

Feature Engineering

Build new features from your merged table

Handling Missing Data

Outer joins create gaps — handle them well

Get Started with SKARI

Load, merge, and analyze in one place