SQL 102: Joins and Join Tables

In my last blog, we learned about how to create a table, insert data, and query data in that table using SQL. Now let’s take a look at how we can make more complex queries using SQL to get data from two tables using joins and how we can create separate join tables!

In the last blog, we used a students table as our example. Let's go ahead and see how we would create that table again using SQL:

CREATE TABLE students(
 id INTEGER PRIMARY KEY,
 name TEXT,
 grade INTEGER,
 gpa FLOAT,
 tardies INTEGER);

Now let’s make a new table for teachers.

CREATE TABLE teachers(
 id INTEGER PRIMARY KEY,
 name TEXT,
 subject TEXT,
 grade INTEGER);

Let’s say every student has one teacher. How can we link the students to their teacher? Simple, we just need to add a column to the students table that contains the id of their teacher! The id of each teacher will be the same as the teacher’s primary key on the teachers table. The teacher_id on the students table is called a foreign key. Let’s see how we can add it with an ALTER TABLE statement:

ALTER TABLE Orders
  ADD FOREIGN KEY (teacher_id) REFERENCES teachers(id)

Let’s insert some data into our tables now:

INSERT INTO students (name, grade, gpa, tardies, teacher_id)
  VALUES ("Jake", 12, 3.5, 4, 2);
INSERT INTO students (name, grade, gpa, tardies)
  VALUES ("Emily", 10, 3.7, 2, 4);
INSERT INTO students (name, grade, gpa, tardies)
  VALUES ("Sam", 11, 3.5, 3, 1);
INSERT INTO students (name, grade, gpa, tardies)
  VALUES ("Jordan", 10, 3.6, 2, null);
INSERT INTO students (name, grade, gpa, tardies)
  VALUES ("Victoria", 9, 3.2, 3, 3);
INSERT INTO teachers (name, grade, subject)
  VALUES ("Mr. Smith", 12, "Math");
INSERT INTO students (name, grade, gpa, tardies)
  VALUES ("Mrs. Brown", 10, "Science");
INSERT INTO students (name, grade, gpa, tardies)
  VALUES ("Mrs. Reed", 11, "English");
INSERT INTO students (name, grade, gpa, tardies)
  VALUES ("Mr. Jones", 10, "Music");

Using an Inner Join

How could we see the data for all of the names of the students as well as the name of each student’s teacher? We can join the tables on the teacher_id using an INNER JOIN.

SELECT student.name, teacher.name
FROM students
INNER JOIN teachers
ON students.teacher_id = teacher.id

This will return rows of data with columns for student’s names and the name of each student’s teacher. You also may notice that we are using dot notation now when writing column names, this is necessary when there is more than one table being referenced in the SQL statement. When we use an INNER JOIN, only rows that do not have null data on the columns we join will be returned. If we want to include the records of students who do not have a teacher assigned to them we can use a LEFT JOIN.

Thisstudents and teachers relationship is known as a “has many / belongs to” relationship. Each student “belongs to” a teacher and each teacher “has many” students. When you have this kind of relationship, a foreign key will suffice to connect the tables.

Aliasing

If we are selecting a lot of columns, our code is going to get a bit long-winded. Let’s see how we can alias using AS. First, let’s look at an example that selects several columns and then see how we can clean it up by aliasing the names of the tables:

SELECT student.name, student.grade, student.gpa, teacher.name, teacher.subject
FROM students
INNER JOIN teachers
ON students.teacher_id = teacher.id

Now lets alias students and teachers:

SELECT s.name, s.grade, s.gpa, t.name, t.subject
FROM students AS s
INNER JOIN teachers AS t
ON s.teacher_id = t.id

Ah, much better! We can also alias the names of the columns:

SELECT s.name AS student_name, t.name AS teacher_name
FROM students AS s
INNER JOIN teachers AS t
ON s.teacher_id = t.id

Moving right along, we’ve seen how to reference another table with a foreign key, but what would we do if each student had multiple teachers? Only one teacher_id can go on each row, so that won’t work if we have multiple teacher_ids for each student. We need to create a Join Table!

Creating a Join Table

Since each student now has many teachers, and each teacher has many students, we call this a many-to-many relationship. Any time we have a many-to-many relationship a join table is needed. It can be queried to see which teachers each student has and which students each teacher has. So each row in the table will contain a teacher_id and a student_id. Let’s call it class_assignments and create the table now:

CREATE TABLE class_assignments(
 student_id INTEGER,
 teacher_id INTEGER);

Now let’s insert some data:

INSERT INTO class_assignments (student_id, teacher_id) VALUES (3, 2)
INSERT INTO class_assignments (student_id, teacher_id) VALUES (1, 2)
INSERT INTO class_assignments (student_id, teacher_id) VALUES (3, 4)
INSERT INTO class_assignments (student_id, teacher_id) VALUES (1, 3)

How can we create a query to get the names of the teachers that a student has? Let’s use our student “Sam”, who has an id of 3:

SELECT teacher.name 
FROM teacher
INNER JOIN class_assignments 
ON teacher.id = class_assignments.teacher_id WHERE student.id = 3;

By joining the teachers table with our class_assignments join table, we get the names of all of Sam’s teachers. Pretty neat, right!?