Back to list Standard SQL How to Join on Multiple Columns Database: SQL MySQL MS SQL Server PostgreSQL Oracle SQLite Operators:JOIN, INNER JOIN, ON 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: SQL Basics SQL Basics in SQL Server SQL Practice Set Recommended articles: SQL JOINs for Beginners An Illustrated Guide to Multiple Join Learning JOINs With Real World SQL Examples See also: How to Join Multiple (3+) Tables in One Statement How to Multiply Two Columns in SQL Tags: SQL MySQL MS SQL Server PostgreSQL Oracle SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.