Inner Join with Multiple Conditions
You'll learn how to combine tables using multiple matching criteria in a single JOIN operation.
What Are Multiple Join Conditions?
So far, you've seen basic inner joins that match tables on a single column (like employees.department_id = departments.id). But sometimes you need to match records based on multiple criteria simultaneously. This is where compound join conditions come in.
Think of it like matching puzzle pieces: instead of needing just one edge to align, you need two or three edges to match perfectly before the pieces fit together.
Using AND in Join Conditions
The most common approach uses the AND operator to require that all conditions must be true for rows to match:
SELECT *
FROM orders
INNER JOIN shipments
ON orders.order_id = shipments.order_id
AND orders.warehouse_id = shipments.warehouse_id;
This only returns rows where both the order_id and the warehouse_id match between the two tables. If either condition fails, the row pair is excluded.
Using OR in Join Conditions
Less common but valid, OR allows rows to match if any condition is true:
SELECT *
FROM employees
INNER JOIN contacts
ON employees.email = contacts.email
OR employees.phone = contacts.phone;
This finds matches when either the email or the phone number aligns—useful when you have multiple ways to identify the same entity.
Why Use Multiple Conditions?
Multiple join conditions are essential when:
- Your relationship depends on composite keys (multiple columns together form uniqueness)
- You need to filter within the join logic itself
- You're matching on date ranges or other complex criteria
Key Takeaway: Compound join conditions using AND/OR let you match tables on multiple columns simultaneously, enabling more precise and complex table relationships.