How to Use INNER JOIN on Multiple Columns in MySQL
mysql inner join multiple columns, A SQL JOIN can look simple when two tables have one obvious column connecting them. But real-world databases are often more complicated.
Sometimes a single column doesn’t provide enough information to identify the correct record. A team name might identify a basketball team, for example, but it doesn’t tell us whether we’re looking at a Guard, Forward, or Center.
In situations like this, MySQL allows you to join tables using multiple columns.
This technique is especially useful when a combination of columns is needed to establish an accurate relationship between two datasets.
The Basic Syntax
To perform an INNER JOIN using multiple columns, place multiple matching conditions in the ON clause and connect them with AND:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column1
AND table1.column2 = table2.column2;
For example:
SELECT team, position, points, assists
FROM athletes1
INNER JOIN athletes2
ON athletes1.team = athletes2.team_name
AND athletes1.position = athletes2.position_name;
This query requires both conditions to be true before two rows are joined.
In our example:
teammust matchteam_namepositionmust matchposition_name
Think of the JOIN as asking:
“Do these two rows belong to the same team and represent the same position?”
If either answer is no, the rows aren’t joined.
Why One Column Isn’t Always Enough
Consider the following data:
| Team | Position |
|---|---|
| Mavs | Guard |
| Mavs | Forward |
| Mavs | Center |
If we joined another table using only the team:
ON athletes1.team = athletes2.team_name
MySQL would know that both records belong to the Mavs, but it wouldn’t know which position should be paired with which.
A Mavs Guard could potentially match a Mavs Forward or a Mavs Center.
That’s not the relationship we want.
By adding the second condition:
AND athletes1.position = athletes2.position_name
we make the match much more precise.
Now:
Mavs + Guard
can only match:
Mavs + Guard
and not:
Mavs + Forward
Mavs + Center
This is the key idea behind joining on multiple columns.
Creating the Example Tables
Let’s use a basketball dataset to see how this works.
First, create a table called athletes1:
CREATE TABLE athletes1 (
team TEXT NOT NULL,
position TEXT NOT NULL,
points INT NOT NULL
);
Insert some sample data:
INSERT INTO athletes1 VALUES ('Mavs', 'Guard', 13);
INSERT INTO athletes1 VALUES ('Mavs', 'Forward', 25);
INSERT INTO athletes1 VALUES ('Mavs', 'Center', 10);
INSERT INTO athletes1 VALUES ('Spurs', 'Guard', 28);
INSERT INTO athletes1 VALUES ('Spurs', 'Forward', 16);
INSERT INTO athletes1 VALUES ('Spurs', 'Center', 20);
View the table:
SELECT * FROM athletes1;
The result is:
+-------+----------+--------+
| team | position | points |
+-------+----------+--------+
| Mavs | Guard | 13 |
| Mavs | Forward | 25 |
| Mavs | Center | 10 |
| Spurs | Guard | 28 |
| Spurs | Forward | 16 |
| Spurs | Center | 20 |
+-------+----------+--------+
This table contains the player’s team, position, and points.
Now let’s create a second table containing assist statistics.
Creating the Second Table
CREATE TABLE athletes2 (
team_name TEXT NOT NULL,
position_name TEXT NOT NULL,
assists INT NOT NULL
);
Insert the following records:
INSERT INTO athletes2 VALUES ('Mavs', 'Forward', 4);
INSERT INTO athletes2 VALUES ('Spurs', 'Forward', 2);
INSERT INTO athletes2 VALUES ('Mavs', 'Guard', 10);
INSERT INTO athletes2 VALUES ('Spurs', 'Guard', 9);
INSERT INTO athletes2 VALUES ('Mavs', 'Center', 13);
INSERT INTO athletes2 VALUES ('Spurs', 'Center', 7);
The second table looks like this:
+-----------+---------------+---------+
| team_name | position_name | assists |
+-----------+---------------+---------+
| Mavs | Forward | 4 |
| Spurs | Forward | 2 |
| Mavs | Guard | 10 |
| Spurs | Guard | 9 |
| Mavs | Center | 13 |
| Spurs | Center | 7 |
+-----------+---------------+---------+
Notice that the column names aren’t identical.
The first table uses:
team
position
while the second uses:
team_name
position_name
That’s perfectly valid. The columns don’t need identical names. What matters is that they contain corresponding values.
Joining the Tables on Two Columns
Now we can combine the tables:
SELECT athletes1.team,
athletes1.position,
athletes1.points,
athletes2.assists
FROM athletes1
INNER JOIN athletes2
ON athletes1.team = athletes2.team_name
AND athletes1.position = athletes2.position_name;
The output is:
+-------+----------+--------+---------+
| team | position | points | assists |
+-------+----------+--------+---------+
| Mavs | Forward | 25 | 4 |
| Spurs | Forward | 16 | 2 |
| Mavs | Guard | 13 | 10 |
| Spurs | Guard | 28 | 9 |
| Mavs | Center | 10 | 13 |
| Spurs | Center | 20 | 7 |
+-------+----------+--------+---------+
Each record is matched using a team-position combination.
For example, the first table contains:
Mavs | Forward | 25
The second table contains:
Mavs | Forward | 4
Both the team and position match, so MySQL combines them:
Mavs | Forward | 25 | 4
What If We Join Using Only the Team?
This is an important experiment because it shows why multiple JOIN conditions matter.
Try:
SELECT a1.team,
a1.position,
a1.points,
a2.position_name,
a2.assists
FROM athletes1 AS a1
INNER JOIN athletes2 AS a2
ON a1.team = a2.team_name;
For every Mavs row in the first table, MySQL can match every Mavs row in the second table.
That means the Mavs Guard could be paired with:
Mavs Guard
Mavs Forward
Mavs Center
The same problem occurs for the Spurs.
This can create many more rows than expected.
The JOIN isn’t technically broken. It’s simply following the instructions we gave it.
We told MySQL:
“Match rows whenever the team is the same.”
We did not tell it that the position must also match.
Adding the second condition fixes the relationship:
ON a1.team = a2.team_name
AND a1.position = a2.position_name
Multiple Columns Act Like a Combined Identifier
A useful way to think about this is that team and position together identify a particular record.
Instead of looking at:
Team = Mavs
we look at:
Team = Mavs
Position = Guard
Together, these values form a more specific identifier:
(Mavs, Guard)
Similarly:
(Mavs, Forward)
(Mavs, Center)
(Spurs, Guard)
(Spurs, Forward)
(Spurs, Center)
This type of combination is commonly associated with a composite key.
You don’t necessarily need to create an actual composite primary key to use this technique. The important point is that multiple columns can collectively define the relationship between records.
Using Table Aliases
When working with multiple JOIN conditions, aliases can make the SQL easier to understand.
Instead of repeatedly writing athletes1 and athletes2, use shorter aliases:
SELECT a1.team,
a1.position,
a1.points,
a2.assists
FROM athletes1 AS a1
INNER JOIN athletes2 AS a2
ON a1.team = a2.team_name
AND a1.position = a2.position_name;
Here:
a1 = athletes1
a2 = athletes2
The result is identical, but the query is easier to maintain.
This becomes particularly helpful when you join three, four, or even more tables.
Adding a WHERE Clause
Multiple-column JOINs can also be combined with filtering.
Suppose we only want players who scored more than 20 points:
SELECT a1.team,
a1.position,
a1.points,
a2.assists
FROM athletes1 AS a1
INNER JOIN athletes2 AS a2
ON a1.team = a2.team_name
AND a1.position = a2.position_name
WHERE a1.points > 20;
The JOIN first establishes the correct team-position matches.
The WHERE clause then filters those matches.
This gives us an important distinction:
ON → How should the records be matched?
WHERE → Which matched records should be returned?
What About Three Columns?
The same idea works with three or more columns.
Imagine that the tables also contained a season column.
You could write:
SELECT *
FROM athletes1 AS a1
INNER JOIN athletes2 AS a2
ON a1.team = a2.team_name
AND a1.position = a2.position_name
AND a1.season = a2.season;
Now all three values must match:
Team
+
Position
+
Season
This is useful in databases where the same team-position combination can occur across different seasons.
For example:
Mavs | Guard | 2024
Mavs | Guard | 2025
Mavs | Guard | 2026
The season becomes necessary to identify the correct record.
A Real-World Example: Sales Data
The same technique appears frequently in business databases.
Imagine two tables containing sales information.
The first table stores sales by:
store_id
product_id
sales
The second table stores:
store_id
product_id
price
Suppose the combination of store_id and product_id identifies the correct record.
You could write:
SELECT s.store_id,
s.product_id,
s.sales,
p.price
FROM sales AS s
INNER JOIN product_prices AS p
ON s.store_id = p.store_id
AND s.product_id = p.product_id;
Using only product_id might not be enough if the same product has different prices at different stores.
The second column makes the relationship more precise.
A Common Mistake: Using OR
When multiple columns must match, you generally want AND, not OR.
Correct:
ON a1.team = a2.team_name
AND a1.position = a2.position_name
Potentially problematic for this use case:
ON a1.team = a2.team_name
OR a1.position = a2.position_name
With OR, a row can match when either condition is true.
That can produce many unintended matches.
If both pieces of information define the relationship, both conditions should normally be connected with AND.
Parentheses: Are They Required?
You may sometimes see the JOIN written like this:
ON ((athletes1.team = athletes2.team_name)
AND (athletes1.position = athletes2.position_name))
This works, but the extra parentheses aren’t necessary here.
This is simpler and equivalent:
ON athletes1.team = athletes2.team_name
AND athletes1.position = athletes2.position_name
Using fewer parentheses can make straightforward JOIN conditions easier to read.
Parentheses become more useful when your conditions contain more complicated combinations of AND and OR.
What Happens When There Is No Match?
Because we’re using an INNER JOIN, a row is returned only when a matching combination exists in both tables.
Suppose athletes1 contains:
Lakers | Guard
but athletes2 doesn’t contain:
Lakers | Guard
That record won’t appear in the result.
This is an important characteristic of INNER JOIN:
No matching combination means no row in the result.
If you wanted to preserve unmatched records from the first table, you would need to consider a LEFT JOIN instead.
A Simple Way to Remember Multiple-Column JOINs
Before writing the SQL, ask one question:
“What information makes these two records represent the same thing?”
If the answer is:
Same team and same position
then use:
ON a1.team = a2.team_name
AND a1.position = a2.position_name
If the answer is:
Same store, same product, and same date
then use:
ON a1.store_id = a2.store_id
AND a1.product_id = a2.product_id
AND a1.sale_date = a2.sale_date
The SQL should reflect the actual relationship in the data.
Final Takeaway
An INNER JOIN on multiple columns is useful when one column alone isn’t enough to identify the correct matching record.
The general pattern is:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column1
AND table1.column2 = table2.column2;
For our basketball example:
SELECT a1.team,
a1.position,
a1.points,
a2.assists
FROM athletes1 AS a1
INNER JOIN athletes2 AS a2
ON a1.team = a2.team_name
AND a1.position = a2.position_name;
The result correctly connects each player record using both team and position.
The biggest lesson isn’t the syntax itself. It’s understanding what uniquely connects the data.
If one column isn’t enough, don’t force the JOIN. Identify the additional columns that define the relationship and include them in the ON clause.
Once you understand that idea, multiple-column JOINs become much easier to write—and much harder to get wrong.
