Can you join more than one column in a JOIN condition? Give an example.

Difficulty: 4/10

multi-column join, composite keys, index usage

Joining on Multiple Columns in MySQL

Yes, in MySQL you can join tables on more than one column by specifying multiple conditions in the ON clause combined with AND. This is useful when a single column is not sufficient to uniquely match rows between tables.

1. Why Join on Multiple Columns

1
• To ensure a more precise match between rows from different tables.
2
• When a combination of columns forms a composite key or unique identifier.
3
• To avoid incorrect matches when individual columns may have duplicate values.

Example: Joining on Two Columns

In this example, the orders table is joined with the products table using two columns: product_id and warehouse_id. Both conditions must be satisfied for a row to be included in the result set.

2. Key Points

1
• Multiple columns can be joined using AND in the ON clause.
2
• This technique works with INNER, LEFT, RIGHT, or FULL JOINs (simulated in MySQL).
3
• Ensure indexes exist on the join columns to maintain performance in multi-column joins.

In summary: Joining on multiple columns allows more precise matches and helps enforce relational integrity when single columns alone are insufficient.

Scenario Questions

0-2 years experience

2-5 years experience

1Your feature aggregates sales per region, but the sales table stores region_code and store_id as a composite foreign key to the stores table. The query is returning duplicate rows. Walk me through how you'd adjust the JOIN condition.

Follow-up Questions