Equi Join

Definition

An Equi Join is a Theta Join (7.8) where the condition θ is restricted specifically to equality (=). It's the single most common join type in real-world SQL — matching a foreign key in one table to a primary key in another is almost always an Equi Join.

How It Works

sql
SELECT e.name, d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

The join condition e.department_id = d.id uses only equality — this makes it an Equi Join specifically (as opposed to the more general Theta Join, which could have used <, >, BETWEEN, etc.). An Equi Join's result includes BOTH the matched columns from each side (here, e.department_id AND d.id both appear if you SELECT *) — even though their values are identical for every returned row, since they're what matched. This redundancy is exactly what Natural Join (7.10) removes.

Edge Cases and Pitfalls

• An Equi Join can compare multiple column-pairs with AND, all using equality — e.g. matching on both department_id = department_id AND semester = semester for a composite-key relationship; it's still an Equi Join as long as EVERY comparison in the condition is equality.
• Mixing an equality comparison with a non-equality one in the same join condition (ON a.x = b.x AND a.y > b.y) makes the overall join a Theta Join, not a pure Equi Join — "Equi Join" specifically means ALL comparisons in the condition are =.
• Since Equi Join keeps both matched columns (unlike Natural Join), it's actually the more flexible choice when the matching columns don't happen to share the same NAME but should still be equated — Natural Join specifically requires identical column names to work correctly.

Key Takeaways

• Equi Join = Theta Join restricted to equality conditions only; it's the most common real-world join type.
• Its result includes both sides' matched columns, even though their values are identical — this redundancy is what Natural Join removes.
• Equi Join works even when the matching columns have different names; Natural Join requires them to share a name.