Join Predicate

Definition

The join predicate is the condition (after ON) that determines whether two rows are considered a match — the heart of any join, regardless of join type.

Running example — departments(id, name) and employees(id, name, department_id, manager_id, salary), where department_id/manager_id can be NULL:

departments: (1, Engineering), (2, Sales), (3, Marketing)

employees: (1, Asha, dept=1, mgr=NULL, 90000), (2, Rohan, dept=1, mgr=1, 70000), (3, Neha, dept=2, mgr=1, 65000), (4, Vikram, dept=NULL, mgr=1, 50000)

Note: Marketing (dept 3) has no employees; Vikram has no department; Asha has no manager.

sql
... JOIN departments d ON e.department_id = d.id           -- equality predicate (most common)
... JOIN salary_bands s ON e.salary BETWEEN s.min AND s.max  -- range predicate (a Theta Join, 7.8)
... JOIN events a JOIN events b ON a.end_time < b.start_time -- inequality predicate

Most join predicates are equality (matching a foreign key to a primary key), but ANY boolean condition is valid — this is exactly the Theta Join generality from relational algebra (7.8), expressed via SQL's ON clause.

Edge Cases and Pitfalls

• A join predicate can combine multiple conditions with AND/OR, just like a WHERE clause: ON e.department_id = d.id AND e.status = 'active' restricts the JOIN itself, which behaves differently from putting e.status = 'active' in WHERE instead, especially for outer joins (an ON-clause condition on the non-preserved side filters BEFORE outer-NULL-filling; a WHERE-clause condition filters AFTER, potentially discarding preserved NULL rows).
• A join predicate referencing columns from MORE than the two tables directly being joined (referencing a third, not-yet-joined table) is invalid — a predicate can only reference tables already available at that point in the query.
• Choosing a join predicate that ISN'T selective (matches almost every row, like ON 1=1) technically works but effectively produces a Cartesian Product — the predicate exists specifically to narrow matches down meaningfully.

Key Takeaways

• The join predicate (the ON condition) determines what counts as a match — equality is most common, but any boolean condition is valid (a Theta Join).
• Putting an extra condition in ON vs WHERE matters for outer joins — ON filters before NULL-filling, WHERE filters after.
• A non-selective predicate (like 1=1) effectively degenerates a join into a Cartesian Product.