How to INNER JOIN on Multiple Columns in MySQL

by

mysql inner join multiple columns, sometimes one column isn’t enough to identify the relationship between two tables.

Imagine a basketball database where the same team has a Guard, Forward, and Center. If you join two tables using only the team name, MySQL may find multiple possible matches. The result can contain combinations that don’t actually belong together.

The solution is to use multiple columns in the JOIN condition.

In MySQL, you can combine two or more conditions in an INNER JOIN using the AND operator.

Basic Syntax

The general pattern looks like this:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column1
AND table1.column2 = table2.column2;

In our example, we want both the team and position to match:

SELECT team, position, points, assists
FROM athletes1
INNER JOIN athletes2
ON athletes1.team = athletes2.team_name
AND athletes1.position = athletes2.position_name;

The important part is this:

ON athletes1.team = athletes2.team_name
AND athletes1.position = athletes2.position_name

A row must satisfy both conditions to be included in the result.

Why Would You Need Multiple JOIN Conditions?

Consider these two pieces of information:

Team       Position
Mavs       Guard
Mavs       Forward
Mavs       Center

If you joined another table using only:

athletes1.team = athletes2.team_name

MySQL knows that both records belong to the Mavs, but it doesn’t know which position belongs to which record.

For example, a Mavs Guard could potentially match a Mavs Forward, Center, or Guard in the other table.

That’s not what we want.

By adding the second condition:

AND athletes1.position = athletes2.position_name

we tell MySQL:

Match the records only when both the team AND the position are the same.

This makes the relationship much more precise.

Creating the First Table

Let’s build a simple example using basketball data.

First, create a table called athletes1:

CREATE TABLE athletes1 (
  team TEXT NOT NULL,
  position TEXT NOT NULL,
  points INT NOT NULL
);

Now insert some 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);

We can inspect the table with:

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 team’s name, player’s position, and points.

Creating the Second Table

Now let’s create a second table containing assist information.

CREATE TABLE athletes2 (
  team_name TEXT NOT NULL,
  position_name TEXT NOT NULL,
  assists INT NOT NULL
);

Insert the data:

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 contains:

+-----------+---------------+---------+
| 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 are different between the tables:

  • athletes1.team corresponds to athletes2.team_name
  • athletes1.position corresponds to athletes2.position_name

That’s completely fine. JOIN conditions don’t require the columns to have the same names.

They simply need to contain corresponding values.

Joining on Two Columns

Now we can connect 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 result 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 |
+-------+----------+--------+---------+

Every row has a matching team-position combination in both tables.

Think of It as a Composite Key

A useful way to understand this type of JOIN is to think about the combination of columns as a single identifier.

For example:

Mavs + Guard
Mavs + Forward
Mavs + Center
Spurs + Guard
Spurs + Forward
Spurs + Center

Instead of identifying a record using only team, we’re identifying it using:

(team, position)

This is often called a composite key or composite relationship.

The JOIN effectively asks:

“Can I find the same team-position combination in the other table?”

If the answer is yes, the rows are joined.

What Happens If You JOIN on Only One Column?

This is where things get interesting.

Suppose we write:

SELECT athletes1.team,
       athletes1.position,
       athletes1.points,
       athletes2.position_name,
       athletes2.assists
FROM athletes1
INNER JOIN athletes2
ON athletes1.team = athletes2.team_name;

This looks reasonable at first.

But there are three Mavs records in each table.

That means each Mavs row in the first table can match three Mavs rows in the second table.

The same thing happens for the Spurs.

Instead of six meaningful matches, the query can produce many more combinations.

For example, a Mavs Guard could match:

Mavs Guard
Mavs Forward
Mavs Center

The JOIN has no way of knowing that only Mavs + Guard is the correct combination.

Adding the position condition fixes the problem:

ON athletes1.team = athletes2.team_name
AND athletes1.position = athletes2.position_name

Now the Guard can only match the Guard.

The AND Operator Is Important

When joining on multiple columns, the conditions are normally connected with AND.

ON condition1
AND condition2

This means both conditions must be true.

For our example:

ON athletes1.team = athletes2.team_name
AND athletes1.position = athletes2.position_name

A row such as:

Mavs | Guard

will match:

Mavs | Guard

but not:

Mavs | Forward

and not:

Spurs | Guard

The more conditions you add, the more specific the matching becomes.

Joining on Three Columns

The same technique works with three or more columns.

Suppose the database also contained a season column.

You could write:

SELECT *
FROM athletes1
INNER JOIN athletes2
ON athletes1.team = athletes2.team_name
AND athletes1.position = athletes2.position_name
AND athletes1.season = athletes2.season;

Now the records must match on:

  1. Team
  2. Position
  3. Season

This is particularly useful when data is organized by multiple dimensions.

Using Table Aliases

Long table names can make multi-condition JOINs difficult to read.

Table aliases make the query cleaner:

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 aliases a1 and a2 simply provide shorter names for the tables.

For larger SQL queries, this approach makes the JOIN conditions much easier to follow.

Adding a WHERE Clause

You can also combine multiple-column JOINs 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;

Now the JOIN determines which records belong together, while the WHERE clause filters the final result.

This distinction is important:

ON → determines how rows are matched

WHERE → determines which matched rows are returned

A Real-World Example

This technique is not limited to sports data.

Imagine an employee database where salary information is stored separately from employee information.

Suppose the relationship is identified by:

employee_id + department_id

You could join the tables using:

SELECT e.employee_id,
       e.department_id,
       e.name,
       s.salary
FROM employees AS e
INNER JOIN salaries AS s
ON e.employee_id = s.employee_id
AND e.department_id = s.department_id;

This can prevent incorrect matches when an employee identifier alone isn’t sufficient to uniquely identify a record.

The same idea appears in:

  • Sales databases
  • Financial systems
  • Healthcare datasets
  • Inventory systems
  • Customer databases
  • HR systems
  • Marketing analytics
  • Sports statistics
  • Data warehouses

Common Mistake: Using OR Instead of AND

A frequent mistake is accidentally writing:

ON athletes1.team = athletes2.team_name
OR athletes1.position = athletes2.position_name

This means a row can match if either condition is true.

That’s very different from:

ON athletes1.team = athletes2.team_name
AND athletes1.position = athletes2.position_name

For a composite relationship where both columns identify the correct match, AND is generally what you need.

A Simple Mental Model

When you’re unsure how to write a multiple-column JOIN, ask yourself:

What makes these two records the same?

If the answer is:

“They have the same team and the same position.”

Then your JOIN should reflect exactly that:

ON a1.team = a2.team_name
AND a1.position = a2.position_name

If three pieces of information are required, use three conditions.

ON a1.team = a2.team_name
AND a1.position = a2.position_name
AND a1.season = a2.season

SQL is essentially being told how to recognize the corresponding record.

Key Takeaways

An INNER JOIN on multiple columns is useful when one column isn’t enough to establish a precise relationship between two tables.

The general syntax 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 most important lesson is simple:

When a single column doesn’t uniquely identify a matching record, combine multiple columns in the JOIN condition.

Once you understand this concept, you’ll be able to work with much more realistic relational datasets, where relationships are often defined by combinations of fields rather than a single column.

Tags:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

19 − 3 =