SQL JOINs Explained

INNER JOIN

INNER JOIN returns only the rows where the join condition matches in both tables. A person with no student record is excluded; a student record with no matching person would be excluded too. The result is the strict intersection.

It is the most common JOIN by far — and the default in many SQL dialects, where writing JOIN without a qualifier means INNER JOIN. Reach for INNER when you genuinely only care about records that exist on both sides of the relationship. Here it doubles as a filter: ods_person also holds applicants and faculty, so joining to ods_students is what narrows the roster to students.

A subtle pitfall: if the join condition has typos or uses the wrong key, INNER JOIN silently returns zero rows instead of an error. When a query unexpectedly comes back empty, double-check that the columns in your ON clause actually match — ods_students.students_id pairs with ods_person.id, not with a column that merely shares its name.

-- Every student, with the name that lives on ods_person
SELECT
  p.first_name,
  p.last_name,
  s.student_type_desc,
  s.stu_residency_desc
FROM ods_students s
INNER JOIN ods_person p
  ON p.id = s.students_id
ORDER BY p.last_name, p.first_name;

LEFT JOIN

LEFT JOIN returns every row from the left table, and matching rows from the right. When there is no match, the right-side columns come back as NULL. Think of it as "give me everyone, and if they have related data attach it."

This is the JOIN you want for "find the things that don't have X." Combine LEFT JOIN with WHERE right_table.key IS NULL to find course sections nobody registered for, or applicants who never became students. It is one of the most useful patterns in SQL.

When you LEFT JOIN against a one-to-many side (like registrations), you end up with multiple result rows per left-table row. This is correct, but it surprises people. If you only want one row per student, aggregate (GROUP BY + COUNT) — and count the right-hand key, not *, so a student with no registrations counts as zero rather than one.

-- All students, plus their registration count (zero if none)
SELECT
  s.students_id,
  s.student_type_desc,
  COUNT(e.stu_course_sec_id) AS registrations
FROM ods_students s
LEFT JOIN ods_stu_course_sec e
  ON e.scs_student = s.students_id
GROUP BY s.students_id, s.student_type_desc
ORDER BY registrations DESC, s.students_id;

-- The anti-join: course sections nobody registered for
SELECT cs.course_sections_id, cs.sec_name, cs.sec_term
FROM ods_course_sections cs
LEFT JOIN ods_stu_course_sec e
  ON e.scs_course_section = cs.course_sections_id
WHERE e.stu_course_sec_id IS NULL
ORDER BY cs.course_sections_id;

RIGHT JOIN

RIGHT JOIN is the mirror image of LEFT JOIN: every row from the right table, with matching rows from the left, and NULL where the left has no match. Conceptually identical to LEFT JOIN with the table order swapped.

In practice, RIGHT JOIN is uncommon because you can always rewrite it as a LEFT JOIN by flipping the table order — and most engineers find LEFT JOIN easier to read. Some teams ban RIGHT JOIN in style guides for that reason.

You will still see it in legacy code or when refactoring complex multi-table queries where reordering would cascade into a lot of other rewrites. It is worth knowing, but you should rarely write a new RIGHT JOIN from scratch. The example returns exactly the rows of the first LEFT JOIN above, with the tables the other way round.

-- Identical result to the LEFT JOIN above, tables swapped
SELECT
  s.students_id,
  s.student_type_desc,
  COUNT(e.stu_course_sec_id) AS registrations
FROM ods_stu_course_sec e
RIGHT JOIN ods_students s
  ON s.students_id = e.scs_student
GROUP BY s.students_id, s.student_type_desc
ORDER BY registrations DESC, s.students_id;

FULL OUTER JOIN

FULL OUTER JOIN combines LEFT and RIGHT — every row from both tables appears, with NULL filling whichever side has no match. The result is the union, not the intersection.

It is the rarest JOIN type because most business questions only need unmatched rows from one side. But when you are reconciling two sources of truth — say, the admissions office's applications against the registrar's student records — FULL OUTER JOIN is exactly what you want, because mismatches in either direction matter: applicants who never enrolled, and students with no application on file.

Note: MySQL does not support FULL OUTER JOIN in any version, 8.x included; there you emulate it with a UNION of a LEFT JOIN and a RIGHT JOIN. PostgreSQL, SQL Server, and Oracle all support it natively, and QueryU runs on PostgreSQL so it works out of the box.

-- Reconcile applications against student records.
-- NULL on the student side = applied, never enrolled.
-- NULL on the application side = enrolled, no application on file.
SELECT
  a.applications_id,
  a.appl_applicant,
  a.current_status_desc,
  s.students_id
FROM ods_applications a
FULL OUTER JOIN ods_students s
  ON s.students_id = a.appl_applicant
WHERE a.applications_id IS NULL
   OR s.students_id IS NULL
ORDER BY a.applications_id NULLS LAST, s.students_id;

Joining Multiple Tables

Real queries often span three or more tables. You chain JOINs by adding more JOIN ... ON ... clauses; each one connects another table into the result. Read top to bottom: the person joins to the student record, the student record joins to registrations, each registration joins to its section, and the section joins to the catalog course that carries the title.

Each JOIN type can be different. You might INNER JOIN students to registrations (only students who registered) but LEFT JOIN to the meeting pattern (keep rows even when a section has no room booked yet). The choice for each link is independent.

Two practical habits: always alias your tables (ods_students s, ods_stu_course_sec e) and qualify every column with its alias — in a query that touches five tables with overlapping status_desc columns, qualifying is the difference between a query that works and a debugging session. And when a term is involved, join ods_terms and sort on term_start_date: term codes like 2024FA and 2024SP do not sort chronologically.

-- Student name, course title, term and final grade -- six tables in one query
SELECT
  p.last_name,
  p.first_name,
  c.crs_title,
  t.term_desc,
  e.scs_final_grade
FROM ods_person p
INNER JOIN ods_students s
  ON s.students_id = p.id
INNER JOIN ods_stu_course_sec e
  ON e.scs_student = s.students_id
INNER JOIN ods_course_sections cs
  ON cs.course_sections_id = e.scs_course_section
INNER JOIN ods_courses c
  ON c.courses_id = cs.sec_course
INNER JOIN ods_terms t
  ON t.terms_id = e.scs_term
WHERE e.scs_final_grade IS NOT NULL
ORDER BY t.term_start_date, p.last_name, c.crs_title;

Practice these on QueryU

Each challenge below runs one of the JOIN shapes above against the same university dataset and grades it on the result set — an INNER JOIN, a subquery or a CTE that returns the right rows all pass. The first four are in the free tier.