Coding Notes

Ideas and thoughts from Seth Long

Understanding a SQL Junction Table

My blog is now located at http://www.WhoIsSethLong.com

I was recently asked about a sql junction table and thought I’d share my thoughts about them with the few people who read this.  First off, I am NOT a DBA.  So, if something is not correct or accurate please feel free to correct me.

Junction tables are used when dealing with many-to-many relationships in a SQL database.  If you’re wondering what exactly a many-to-many relationship is, let me try to briefly explain.  Suppose we are working at a school and have a table full of student names and another table full of classrooms.  Each of the students can belong to multiple classrooms or none at all.  Likewise, each classroom can have multiple students or none at all.  This is an example of a many-to-many relationship.

A junction table will allow us to create the many-to-many relationship and most importantly, let us keep from adding duplicate entries as you’ll soon see.

To start, lets create a student table and a classroom table.

CREATE TABLE Students
(
    StudentID int IDENTITY(1,1) PRIMARY KEY,
    StudentName nchar(50) NOT NULL
)

CREATE TABLE Classrooms
(
    ClassroomID int IDENTITY(1,1) PRIMARY KEY,
    RoomNumber int NOT NULL
)

Now that we have our two tables created we need to create the junction table that will link them together.  The junction table is created by using the primary key from the Classrooms and Students tables.

CREATE TABLE StudentClassroom
(
    StudentID int NOT NULL,
    ClassroomID int NOT NULL,
    CONSTRAINT PK_StudentClassroom PRIMARY KEY
    (
        StudentID,
        ClassroomID
    ),
    FOREIGN KEY (StudentID) REFERENCES Students (StudentID),
    FOREIGN KEY (ClassroomID) REFERENCES Classrooms (ClassroomID)
)

We have now created a table with columns for the StudentID and the ClassroomID.  This table also uses a combination of these two columns as the primary key.  This means that each student-classroom pair is unique.  Each student can belong to many classrooms, each classroom can belong to many students but each pair can only occur once.

You should also note that the columns in the junction table are setup as foreign keys to the Students and Classrooms tables.  This is important as it keeps us from adding students to a classroom that doesn't exist or deleting a classroom from the database if there are still students belonging to it.

To see what students belong to what classrooms we can use the junction table and the following query:

SELECT StudentName, RoomNumber
FROM StudentClassroom
JOIN Students ON Students.StudentID = StudentClassroom.StudentID
JOIN Classrooms ON Classrooms.ClassroomID = StudentClassroom.ClassroomID

So, that's a junction table in a nut shell.

- Seth Long

About these ads

January 4, 2008 - Posted by | SQL

26 Comments »

  1. This nice and simple explanation was exactly what I was looking for.. Thanks!

    Comment by David | March 7, 2008 | Reply

  2. It can be difficult to find simple answers in the huge pool of db information on the web. Thanks! This was really helpful for me.

    Comment by Jack | March 13, 2008 | Reply

  3. Thanks.

    Was helpful. :-)

    Comment by Ryno | March 18, 2008 | Reply

  4. Just a quick question.

    How would one populate the junction table in this case?

    Would you mind showing the query for that too please?

    Comment by Ryno | March 19, 2008 | Reply

  5. This is really simplest and to-the-point, and also comprehensive explanation of the Junction Table. It made my concept very clear about this subject.

    Keep it up, it is a great service to the community.

    Comment by Tariq Changgez | June 10, 2008 | Reply

  6. ur explaination is really very easy…
    i m reading junction tables first time from ur aticle nd its very clear to me

    thanks a lot

    Comment by Rakhi Bansal | July 10, 2008 | Reply

  7. Very well written. Thank-you

    Comment by Remi Nkar | July 16, 2008 | Reply

  8. Very useful thank you.
    I am also wondering though, how would you populate this table?

    Comment by Ben | September 1, 2008 | Reply

    • Did you ever find out how to populate the junction table? I am looking for the answer too. Any help would be greatly appreciated!

      Comment by Jeff | March 2, 2010 | Reply

  9. Hi

    Can you add some code on how to update or insert new records?

    Comment by Matt | November 25, 2008 | Reply

  10. Wonderful, was frustratingly trying to create a junction table through the visual editor and more importantly set the relationships to the other two tables which seems impossible but to no avail….i should have been using sql….thanks so much

    Good explanation on the junction table!! You are a star disaster!!

    Comment by Lee | February 20, 2009 | Reply

  11. but SELECT StudentName, RoomNumber
    FROM StudentClassroom in this query

    SELECT StudentName, RoomNumber
    FROM StudentClassroom
    JOIN Students ON Students.StudentID = StudentClassroom.StudentID
    JOIN Classrooms ON Classrooms.ClassroomID = StudentClassroom.ClassroomID

    doesn’t seem to be correct isn’t it ?

    Comment by aarklon | July 4, 2009 | Reply

    • Well, I also ask me if it may be that.
      May be it must be writed as:

      SELECT StudentId, RoomNumber FROM StudentClassroom
      JOIN…

      (allthough is would be nice when it can work with the Name :)

      Comment by Wim | November 11, 2009 | Reply

  12. [...] : How to implement a many-to-many relationship using Linq to Sql ? Database Design – Many-to-many Understanding a SQL Junction Table Coding Notes Oracle – What is a junction table and how is it used in Microsoft Access? junction table ebook [...]

    Pingback by Music Collection relationships? - dBforums | August 16, 2009 | Reply

  13. [...] added additional tables to the database for each of these groupings, with associated join tables (what’s a join table?). This would create multiple routes through the hierarchy and make it less clear where data should [...]

    Pingback by Will bids farewell: how to build a course database « Course Tools | September 16, 2009 | Reply

  14. Very simple explanation was exactly what I was looking for.. Thanks

    Comment by Ramanand Ray | October 12, 2009 | Reply

  15. [...] did a quick research and found the standard way to represent a many-to-many relationship in a database is to introduce a [...]

    Pingback by Using a junction/join table to represent a many-to-many relationship in a relational database « just another late twenties dude | October 20, 2009 | Reply

  16. well explained…. :)

    Comment by Mahreen Ali | July 25, 2010 | Reply

  17. Very Nice and Simple way to explain it.
    I like it.
    thanks

    Comment by Kamlaesh | August 12, 2010 | Reply

  18. thanks…. really great! im using access 2007. i prefer to see examples of this by showing the relationship diagram.

    Comment by bernie | November 30, 2010 | Reply

  19. Very realistic answer. Nice explanation as i felt that someone is teaching directly to me..

    Thank you very much…

    -Prathap

    Comment by Prathap | January 20, 2011 | Reply

  20. how to display clasroomid from StudentClassroom as columns?

    StudentID, class1, class2, class3……
    1 1 2 3
    2 3 4 NULL
    3 …

    Comment by L | July 13, 2011 | Reply

  21. I gave an interview in Siemens in Kolkata on 17th Dec,2011. When I mentioned the interviewer about the Junction table as you said. He argued with me that there is no such concept in Sql server. He was claiming to be the senior consultant there. So funny.

    Comment by Bhaskar | December 17, 2011 | Reply

    • Actually I was asked to fetch almost same thing what you did here. Additionally there was start date and end date in the employeeDepartMent table. I had to fetch employee name, latest department name where the employee is working now. I wrote the same query as you mentioned and added “Order by start_date desc” at the end. He said there is another way to do this job and condemned me. I did not find any other way around at that time. Do anyone know how to write this query in other way?

      Comment by Bhaskar | December 17, 2011 | Reply

  22. very nice and simple explained…thank you

    Comment by Christoph | January 15, 2012 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: