JOIN on Multiple Conditions
intermediatejoins
2 min read
What is JOIN on Multiple Conditions?
Sometimes a single column is not enough to define the relationship between tables. You can specify multiple conditions in the ON clause using AND.
Syntax
SELECT columns
FROM table1 t1
JOIN table2 t2
ON t1.col1 = t2.col1
AND t1.col2 = t2.col2;When to Use
- Joining on composite foreign keys
- Adding date range or status filters to the join condition
- Preventing duplicate matches when a single column is ambiguous
Key Points
- AND in ON — Multiple conditions are combined with AND.
- Composite Keys — Tables with composite primary keys require multiple join conditions.
- Additional Filters in ON — For outer joins, placing a filter in ON preserves non-matching rows; placing it in WHERE removes them.
- Performance — Multi-column indexes on the join columns improve performance.
- Clarity — Format each condition on its own line for readability.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
JOIN on Multiple Conditions Challenge
Write a query that solve this task: join enrollments with a grades table using both student_id and course_id.
Expected result
Enrollments joined with students where a grade has been assigned.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is JOIN on Multiple Conditions?
Initializing database...Each run starts from fresh sample data.
More Examples
Join with additional filter in ON
Left join orders with products, only matching active products.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Can I use OR in a JOIN ON clause?
Yes, but it is rare and can be slow. Most join conditions use AND. OR in ON may prevent index usage.
What is the difference between ON and USING?
USING (col) is shorthand when both tables have a column with the same name. ON is more flexible and allows different column names and multiple conditions.