JOINs Deep Dive
Master INNER, LEFT, RIGHT, and FULL OUTER JOINs with real examples from a university data model.
- JOIN
- INNER JOIN
- LEFT JOIN
- OUTER JOIN
Want the one-page version first? Read SQL JOINs Explained, then come back here for the full lesson.
Use this when you need to:
- Pull related rows together — orders with customer details, students with their major, employees with their department
- Build a roll-up that keeps zero-rows with LEFT JOIN — "all customers, even ones without orders" so totals don't silently drop
- Find records with no match using an anti-join (LEFT JOIN ... WHERE right IS NULL) — students without enrollments, products that never sold
- Reconcile two systems that should agree — FULL OUTER JOIN to find rows in either but not both
JOINs combine rows from two or more tables based on a related column. Choosing the right JOIN type determines whether unmatched rows are included or excluded.
INNER JOIN — Only Matched Rows
SELECT p.id, p.first_name, p.last_name, scs.scs_course_name FROM ods_person p INNER JOIN ods_stu_course_sec scs ON scs.scs_student = p.id; -- Only returns people with at least one course registration: -- faculty and applicants who never registered drop out
LEFT JOIN — All Left Rows
SELECT p.id, p.first_name, p.last_name, scs.scs_course_name FROM ods_person p LEFT JOIN ods_stu_course_sec scs ON scs.scs_student = p.id; -- Returns ALL 220 people; scs_course_name is NULL for the 70 -- who have never registered for a course
In real-world queries, use LEFT JOIN when you need "all people, even those without X". Use INNER JOIN when you only want the ones who have X. When in doubt, start with LEFT JOIN: an INNER JOIN drops unmatched rows without telling you, and the totals come out quietly wrong.
Joining Multiple Tables
SELECT
stu.first_name || ' ' || stu.last_name AS student,
c.crs_title AS course,
ins.last_name AS instructor,
scs.scs_final_grade AS grade
FROM ods_stu_course_sec scs
JOIN ods_person stu ON stu.id = scs.scs_student
JOIN ods_course_sections cs ON cs.course_sections_id = scs.scs_course_section
JOIN ods_courses c ON c.courses_id = cs.sec_course
-- LEFT JOIN: 3 of the 20 Spring 2026 sections have no instructor of
-- record, and an INNER JOIN would silently drop their 75 registrations
LEFT JOIN ods_faculty f ON f.faculty_id = cs.sec_faculty_info
LEFT JOIN ods_person ins ON ins.id = f.faculty_id
WHERE scs.scs_term = '2026SP';Each JOIN adds another table. The chain above walks registration → section → catalog course, then section → faculty → person for the instructor's name, because every person, student or faculty, keeps their name on ods_person. Notice ods_person appears twice under two aliases (stu and ins): one table, two roles. Always alias your tables with short, descriptive names to keep long queries readable, and always specify an ON condition, or you get a Cartesian product.
Avoiding Duplicate Rows
One-to-many relationships (one student, many course registrations) produce more rows than expected. Use COUNT(DISTINCT p.id) instead of COUNT(*) when counting students across joins.
-- Wrong: counts one row per registration (returns 1980) SELECT COUNT(*) FROM ods_person p JOIN ods_stu_course_sec scs ON scs.scs_student = p.id; -- Correct: counts unique students (returns 150) SELECT COUNT(DISTINCT p.id) FROM ods_person p JOIN ods_stu_course_sec scs ON scs.scs_student = p.id;
Practise this on real university data
Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.