How to Join on Multiple Columns Database: Standard SQL MySQL MS SQL Server PostgreSQL Oracle SQLite Operators: JOIN INNER JOIN ON Table of Contents Problem: Example: Solution: Discussion: Problem: You want to join tables on multiple columns by using a primary compound key in one table and a foreign compound key in another. Example: Our database has three tables named student, enrollment, and payment. The student table has data in the following columns: id (primary key), first_name, and last_name. idfirst_namelast_name 1EllieWillson 2TomBrown 3SandraMiller The enrollment table has data in the following columns: primary key (student_id and course_code), is_active, and start_date. student_idcourse_codeis_activestart_date 1GD03true2020-01-20 1AP01false2020-03-10 2SL01true2020-05-05 3SL01true2020-06-01 The payment table has data in the following columns: foreign key (student_id and course_code, the primary keys of the enrollment table), status, and amount. student_idcourse_codestatusamount 1GD03paid230 1AP01pending100 2SL01pending80 3SL01pending110 Let’s show each student’s name, course code, and payment status and amount. Solution: SELECT s.last_name, s.first_name, p.course_code, p.status, p.amount FROM enrollment e JOIN student s ON s.id=e.student_id JOIN payment p ON p.course_code=e.course_code AND p.student_id=e.student_id; last_namefirst_namecourse_codestatusamount WillsonEllieGD03paid230 WillsonEllieAP01pending100 BrownTomSL01pending80 MillerSandraSL01pending110 Discussion: If you’d like to get data stored in tables joined by a compound key that’s a primary key in one table and a foreign key in another table, simply use a join condition on multiple columns. In one joined table (in our example, enrollment), we have a primary key built from two columns (student_id and course_code). In the second table (payment), we have columns that are a foreign compound key (student_id and course_code). How can we join the tables with these compound keys? Easy! We just need to use a JOIN clause with more than one condition by using the AND operator after the first condition. In our example, we use this condition: p.course_code=e.course_code AND p.student_id=e.student_id In the first part, we use the student_id column from the enrollment table and student_id from the payment table. In the next condition, we get the course_code column from the enrollment table and course_code from the payment table. Note that the student_id and course_code columns form a primary key in the enrollment table. Therefore, they’re used in the payment table as a foreign key. Recommended courses: JOINs SQL Practice Set Recommended articles: SQL JOIN Cheat Sheet SQL Joins: 12 Practice Questions with Detailed Answers How to Practice SQL JOINs An Illustrated Guide to Multiple Join How to Join 3 Tables (or More) in SQL See also: How to Get all Row Combinations From Two Tables in SQL How to Combine the Results of Two Queries in SQL Subscribe to our newsletter Join our monthly newsletter to be notified about the latest posts. Email address How Do You Write a SELECT Statement in SQL? What Is a Foreign Key in SQL? Enumerate and Explain All the Basic Elements of an SQL Query