In order to be an effective reporter, you must understand JOIN’s. JOIN is a SQL keyword that let’s you query data from one or more database tables usually by way of a Foreign Key. A foreign key is a field in one table that usually points to the primary key in another table. I say usually because that is not always the case with eduTrac. Instead the foreign key may point to a term or code in another table.

This type of joining of data occurs quite often with relational database systems such as the one eduTrac uses (MySQL). If the database is a well designed one, then there will be several tables in that database containing related data.

INNER JOIN

The first join we will look at is called INNER JOIN or just JOIN, which is the most frequently used clause or key phrase. The INNER JOIN clause will produce records that will match in all three tables. Which means every student will have a person record and every student will have some sort of stu_course_sec record.

The query looks something like this:

SELECT student.stuID AS 'Student ID', CONCAT( person.lname, ', ', person.fname ) AS 'Student Name', stu_course_sec.termCode AS 'Last Term Registered'
FROM student
INNER JOIN person ON student.stuID = person.personID
INNER JOIN stu_course_sec ON student.stuID = stu_course_sec.stuID
LEFT OUTER JOIN stu_course_sec section ON ( stu_course_sec.stuID = section.stuID
AND stu_course_sec.termCode < section.termCode )
WHERE section.stuID IS NULL
GROUP BY stu_course_sec.stuID

The above query gives me the information that I am looking for which is a list of students and the last term each student was registered. The use of LEFT OUTER JOIN will be explained later in the article. The relationship between the three tables is the student ID in both the student and stu_course_sec table which is a foreign key to the primary key in the person table (personID). The query will give me the following results:

LEFT JOIN

If I wanted a list of students with the last term registered regardless of whether or not they have ever registered for a course, then I would used a LEFT JOIN. A LEFT JOIN will return results which matches every entry in the left tables (student and person) regardless of any matching entries in the right table (stu_course_sec).

Our new query will look like this:

SELECT student.stuID AS 'Student ID', CONCAT( person.lname, ', ', person.fname ) AS 'Student Name', stu_course_sec.termCode AS 'Last Term Registered'
FROM student
LEFT JOIN person ON student.stuID = person.personID
LEFT JOIN stu_course_sec ON student.stuID = stu_course_sec.stuID
LEFT OUTER JOIN stu_course_sec section ON ( stu_course_sec.stuID = section.stuID
AND stu_course_sec.termCode < section.termCode )
WHERE section.stuID IS NULL
GROUP BY stu_course_sec.stuID

Now, our new query will give me the following results:

RIGHT JOIN

We can’t use our example query as an example for RIGHT JOIN. So let’s say I wanted a list of students and courses regardless if anyone is enrolled in those courses. I would use a RIGHT JOIN to accomplish this. A RIGHT JOIN returns results which matches every entry on the right table (course_sec) regardless of any matching entries on the left table (student).

This is what our example query looks like:

SELECT student.stuID AS 'Student ID',
CONCAT(person.lname,', ',person.fname) AS 'Student Name',
course_sec.courseSection AS 'Course Section'
FROM student
LEFT JOIN person ON student.stuID = person.personID
LEFT JOIN stu_course_sec ON student.stuID = stu_course_sec.stuID
RIGHT JOIN course_sec on stu_course_sec.courseSection = course_sec.courseSection

Our query will yield the following results:

As a note, RIGHT JOIN’s are rarely used because they are just the opposite of LEFT JOIN’s and basically yield the same kinds of results.

LEFT OUTER JOIN

As a brief explanation, a LEFT OUTER JOIN will return records from the left table even if there no match in the right table.