INNER JOIN with Multiple Conditions
You'll learn how to create more precise table joins by combining multiple conditions in the ON clause using AND and OR operators.
Why Multiple Join Conditions Matter
Sometimes matching rows on a single column isn't enough. You might need two (or more) columns to align perfectly to establish the correct relationship between tables. Think of it like matching puzzle pieces—sometimes you need to check both the shape and the color pattern to find the right fit.
Using AND for Stricter Matches
The most common pattern uses AND to require all conditions to be true:
SELECT orders.order_id, customers.name, products.product_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id
AND orders.region = customers.region
INNER JOIN products
ON orders.product_id = products.product_id
AND orders.category = products.category;
Here, rows only match when both the IDs match and the regions match. This prevents incorrect pairings when the same customer ID exists in different regions.
Using OR for Flexible Matches
Less common but useful, OR allows matching when any condition is true:
SELECT employees.name, departments.dept_name
FROM employees
INNER JOIN departments
ON employees.dept_id = departments.dept_id
OR employees.backup_dept_id = departments.dept_id;
This finds matches through either the primary or backup department relationship.
When to Use Multiple Conditions
Use multiple conditions when:
- Composite keys exist (multiple columns form the unique identifier)
- Additional business rules govern relationships
- You need to prevent ambiguous or duplicate matches
Key Takeaway: Multiple conditions in your ON clause using AND/OR let you define more precise and accurate join relationships, ensuring you only combine rows that truly belong together based on your business logic.