Natural Join

Definition

A Natural Join (symbol: ⋈, no subscript) automatically joins two relations on ALL of their identically-named columns, using equality, and includes each matched column only ONCE in the result (removing the duplication an Equi Join leaves behind). No explicit join condition is written — the column names themselves define the match.

How It Works

sql
SELECT * FROM nstudents NATURAL JOIN nenrollments;

If nstudents(student_id, name) and nenrollments(student_id, course_id) share the column student_id, NATURAL JOIN automatically matches rows where student_id is equal, and the result has exactly ONE student_id column (not two), alongside name and course_id. This is exactly the redundancy-free version of the equivalent Equi Join ... JOIN ... ON s.student_id = e.student_id.

Edge Cases and Pitfalls

• NATURAL JOIN's biggest real-world danger: it matches on EVERY identically-named column automatically, including ones you didn't intend as part of the join — if both tables happen to also have an unrelated column named, say, created_at or notes, NATURAL JOIN will silently also require those to match, producing a wrong (usually much smaller, or entirely empty) result with no warning.
• Because of this danger, many real-world style guides recommend AVOIDING NATURAL JOIN in production code in favor of an explicit JOIN ... ON (Equi Join) — the explicit version is more verbose but immune to silent breakage if a table's columns change later.
• If two tables share NO column names at all, NATURAL JOIN silently degenerates into a full Cartesian Product (7.7) — there's nothing to match on, so every combination "matches."

Key Takeaways

• Natural Join automatically matches on all identically-named shared columns and keeps each only once — no explicit condition needed.
• Its convenience is also its danger: an unexpected shared column name (or a schema change adding one) can silently change what it matches on.
• An explicit Equi Join (JOIN ... ON) is generally the safer, more maintainable choice for production code, despite being more verbose.