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.

The best way to practice SQL JOINs is our interactive SQL JOINs course. It contains over 90 exercises that cover different JOIN topics: joining multiple tables, joining by multiple columns, different JOIN types (LEFT JOIN, RIGHT JOIN, FULL JOIN), or joining table with itself. The explanations are based on real-world examples that resemble problems you'll meet daily.

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.

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:

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:

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!