Back to articles list Articles Cookbook
5 minutes read

How to Join Two Tables by Multiple Columns in SQL

The SQL JOIN is an important tool for combining information from several tables. Most often, you’ll be joining tables based on a primary key from one table and a foreign key from another table. However, it is also often the case that you need to join tables by two or more columns. In this article, I’ll discuss why you would want to join tables by multiple columns and how to do this in SQL.

Joining tables by just one column does not work in some scenarios. For example, you may encounter cases in which there is no one column in the table that uniquely identifies the rows. Or the tables you want to join may not have just one common column to use for joining. In situations like these, you may need to use multiple columns to join tables – e.g., the first and the last names, or the order number and the year if the order numbering restarts each year.

Let’s see some examples to understand how this works in practice.

Joining Two Tables on Multiple Columns

Let’s imagine we run a network of kindergartens. In our database, we have the following tables:

  • students, where we have information about each student, such as the name, the kindergarten he or she attended, the class, the graduation year, and the teacher.
  • teachers, where we have the name and the education level of each teacher.
  • classes, where we have information about each class, such as the name of the kindergarten, the class, the graduation year, and the name of the classroom.
students
idfirst_namelast_namekindergartengraduation_yearclassteacher_first_nameteacher_last_name
1MaryJonesSunshine2021AIsabellaLopez
2JohnSmithLove & Learn2020BMiaGonzalez
3MarkAndersonButterfly2020BSophiaStevenson
4StevenMooreButterfly2021CMariaStuart
5DianaMillerButterfly2021CMariaStuart
6AnnaDavisSunshine2021AEmmaGrey
7HellenBrownSunshine2020BOliviaTaylor
8GracyLeeLove & Learn2020ACharlotteJohnson
9SaraGarciaLove & Learn2021BJasmineHarris
10KateWilsonSunshine2019BOliviaTaylor
teachers
idfirst_namelast_nameeducation
101IsabellaLopezUndergraduate
102MariaStuartUndergraduate
103EmmaGreyGraduate
104OliviaTaylorGraduate
105CharlotteJohnsonGraduate
106MiaGonzalezUndergraduate
107SophiaStevensonUndergraduate
108JasmineHarrisUndergraduate
classes
idkindergartengraduation_yearclassclassroom
201Sunshine2019BIronman
202Butterfly2020BSun
203Love & Learn2020ALove
204Love & Learn2020BHappiness
205Sunshine2020AHulk
206Sunshine2020BSuperman
207Butterfly2021AGarden
208Butterfly2021BIsland
209Butterfly2021CSea
210Love & Learn2021ADream
211Love & Learn2021BLaugh
212Sunshine2021AIronman

You might notice our database is not perfectly organized. Typically, the students table would include foreign keys like the teacher ID and the class ID instead of detailed information about the corresponding teachers and classes. However, even with the data stored like this, we can join the tables as long as each table has a set of columns that uniquely identifies each record.

It’s time for a couple of SQL queries!

Example 1: SQL JOIN by Two Columns

In our first example, we want to know the education level of the teacher for each student. For this, we need to combine the information from the tables students and teachers. Unfortunately, we don't have the teacher ID column in the students table. However, we do have the teacher's first and last names in both tables. As long as we don't have teachers with identical full names, we can safely join these tables by these two columns. Here’s the query:

SELECT s.first_name, s.last_name, s.teacher_first_name, s.teacher_last_name, t.education AS teacher_education
FROM students s
JOIN teachers t
ON s.teacher_first_name = t.first_name AND s.teacher_last_name = t.last_name;

If you need a refresher on the SQL JOIN syntax, check out this great SQL JOIN Cheat Sheet.

Notice the two conditions in the ON clause as we condition on both (1) the first name from the teachers table to be equal to the teacher's first name in the students table and (2) the last name from the teachers table to be equal to the teacher's last name in the students table. As you see, to specify two conditions, we simply put both of them in the ON clause using the AND keyword in between.

Here's the result:

first_namelast_nameteacher_first_nameteacher_last_nameteacher_education
MaryJonesIsabellaLopezUndergraduate
StevenMooreMariaStuartUndergraduate
DianaMillerMariaStuartUndergraduate
AnnaDavisEmmaGreyGraduate
HellenBrownOliviaTaylorGraduate
KateWilsonOliviaTaylorGraduate
JohnSmithMiaGonzalezUndergraduate
MarkAndersonSophiaStevensonUndergraduate
GracyLeeCharlotteJohnsonGraduate
SaraGarciaJasmineHarrisUndergraduate

Looks good! We now see the corresponding teacher's education level for each student.

To get more practice with joining tables in SQL, check out this interactive SQL JOINs course.

Example 2: SQL JOIN by Three Columns

In the previous example, we saw how to join two tables by two conditions. We can have even more conditions if needed. Let’s see how to join tables in SQL with three conditions.

We now want to find out the name of the classroom where each student played and studied. The classroom information is available in the classes table.

We don’t have the class ID in the students table. However, we have three columns there that uniquely identify a class when combined: kindergarten, graduation_year, class. The same columns are present in the classes table. Thus, we are going to combine students and classes using three columns:

SELECT s.first_name, s.last_name, c.kindergarten, c.graduation_year, c.class, c.classroom
FROM students s
JOIN classes c
ON s.kindergarten = c.kindergarten AND s.graduation_year = c.graduation_year AND s.class = c.class;

As you can see, we join the tables using the three conditions placed in the ON clause with the AND keywords in between. Here’s the output:

first_namelast_namekindergartengraduation_yearclassclassroom
MarkAndersonButterfly2020BSun
JohnSmithLove & Learn2020BHappiness
StevenMooreButterfly2021CSea
MaryJonesSunshine2021AIronman
GracyLeeLove & Learn2020ALove
HellenBrownSunshine2020BSuperman
DianaMillerButterfly2021CSea
AnnaDavisSunshine2021AIronman
SaraGarciaLove & Learn2021BIronman
KateWilsonSunshine2019BLaugh

The JOIN worked as intended! We now have the corresponding classroom for each student.

If you want to see more examples, check out this cookbook on joining tables by multiple columns.

Let’s Practice SQL JOINs by Multiple Columns!

The SQL JOIN is one of the basic tools for data analysts working with SQL. Relational databases are built in a way such that analytical reports usually require combining information from several tables. You’ll be joining tables, sometimes by one column and other times by two or more columns.

As you saw, joining tables by multiple columns is quite straightforward in SQL. But if you want to become confident in using SQL JOINs, practicing with real-world data sets is a key success factor.

I recommend starting with this interactive SQL JOINs course which includes 93 coding challenges. It covers the most common types of joins like JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and self-joins as well as non-equi joins.

To get even more practice with SQL JOINs and other basic SQL tools, consider taking the SQL from A to Z track. It includes 7 interactive courses that cover standard SQL functions, basic SQL reports, window functions, common table expressions, recursive queries, and much more.

Thanks for reading, and happy learning!