When one join column isn't enough

Question

Accepted Answer

Some relationships use composite keys - the unique identifier spans multiple columns. For example, a regional_prices table keyed on (product_id, region_id) requires ON o.product_id = rp.product_id AND o.region_id = rp.region_id. Joining on only one column would produce incorrect fan-out (extra rows). Multiple ON conditions are connected with AND. You can also mix equality and inequality conditions in the same ON clause.