Course contentsShow
← Computer Science
Lesson 1578 of 2,87236. Database FundamentalsFree lesson

Inner Join with Multiple Conditions

Using compound join conditions with AND/OR operators to match on multiple columns.

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.