Chapter 32: PostgreSQL INNER JOIN
The INNER JOIN.
You’ve already learned basic SELECT, WHERE, LIKE, IN, BETWEEN, AS, and we just did a quick overview of all JOIN types. Today we go deep only into INNER JOIN — because in real applications 80–90% of all JOINs you will ever write are INNER JOINs.
1. What does INNER JOIN actually do? (Very honest teacher explanation)
INNER JOIN returns only the rows where there is a match in both tables according to the join condition.
Think of it like this:
- You have two lists:
- Left table = students
- Right table = enrollments
- INNER JOIN says: “Show me only those students who actually appear in the enrollments table (i.e., who actually enrolled in at least one course). If a student never enrolled → don’t show them. If an enrollment row points to a non-existing student → don’t show it either.”
In short: intersection — only the overlapping part of the two sets.
Official PostgreSQL wording (from docs):
INNER JOIN returns all rows from both tables where the join condition is satisfied.
2. Basic syntax (the two most common ways you will write it)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- Style 1: Explicit INNER JOIN (most readable & recommended) SELECT … FROM table1 INNER JOIN table2 ON table1.foreign_key = table2.primary_key; -- Style 2: USING clause (when column names are identical) SELECT … FROM table1 JOIN table2 USING (common_column_name); -- Style 3: Old comma style (still works, but avoid in new code) SELECT … FROM table1, table2 WHERE table1.id = table2.student_id; |
Best practice in 2026: always use explicit INNER JOIN … ON … — it’s clearest.
3. Real, practical example – college schema (our ongoing playground)
Tables:
students
| id | first_name | last_name | gpa | |
|---|---|---|---|---|
| 1 | Aarav | Patel | aarav@hyd.edu | 8.95 |
| 2 | Priya | Reddy | priya@gmail.com | 9.60 |
| 3 | Rahul | Sharma | rahul@yahoo.com | 7.85 |
| 4 | Sneha | Kumar | sneha@gmail.com | 9.20 |
| 5 | Vikram | Naik | vikram@hyd.edu | 8.40 |
courses
| id | code | name | credits |
|---|---|---|---|
| 1 | CS101 | Introduction to Programming | 4 |
| 2 | DBMS01 | Database Systems | 4 |
| 3 | ML101 | Machine Learning Basics | 3 |
| 4 | AI101 | Artificial Intelligence | 3 |
enrollments (junction table)
| id | student_id | course_id | grade | enrolled_at |
|---|---|---|---|---|
| 1 | 1 | 1 | A | 2025-08-01 |
| 2 | 1 | 2 | A+ | 2025-08-02 |
| 3 | 2 | 1 | B+ | 2025-08-01 |
| 4 | 4 | 3 | A | 2025-08-05 |
| 5 | 5 | 2 | B | 2025-08-10 |
Notice:
- Rahul (id=3) has no enrollments
- Course AI101 (id=4) has no enrollments
Example 1: Basic INNER JOIN – students + their courses
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT s.first_name || ' ' || s.last_name AS student_name, c.name AS course_name, e.grade, e.enrolled_at FROM students s INNER JOIN enrollments e ON s.id = e.student_id INNER JOIN courses c ON c.id = e.course_id ORDER BY student_name, course_name; |
Result:
text
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
student_name | course_name | grade | enrolled_at ---------------|------------------------------------|-------|--------------------- Aarav Patel | Database Systems | A+ | 2025-08-02 Aarav Patel | Introduction to Programming | A | 2025-08-01 Priya Reddy | Introduction to Programming | B+ | 2025-08-01 Sneha Kumar | Machine Learning Basics | A | 2025-08-05 Vikram Naik | Database Systems | B | 2025-08-10 |
→ Rahul is missing (no enrollments) → Course “Artificial Intelligence” is missing (no enrollments)
That’s exactly what INNER JOIN does — only matching rows.
Example 2: Count enrollments per student (with INNER JOIN + GROUP BY)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT s.first_name || ' ' || s.last_name AS student, COUNT(e.id) AS courses_enrolled, STRING_AGG(c.name, ', ') AS courses_list FROM students s INNER JOIN enrollments e ON s.id = e.student_id INNER JOIN courses c ON c.id = e.course_id GROUP BY s.id, s.first_name, s.last_name ORDER BY courses_enrolled DESC; |
Result:
text
|
0 1 2 3 4 5 6 7 8 9 10 11 |
student | courses_enrolled | courses_list -------------|------------------|-------------------------------------- Aarav Patel | 2 | Database Systems, Introduction to Programming Priya Reddy | 1 | Introduction to Programming Sneha Kumar | 1 | Machine Learning Basics Vikram Naik | 1 | Database Systems |
→ Again — Rahul disappears because COUNT(e.id) would be 0
Example 3: Using USING() shorthand (when column names match)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT s.first_name, c.name FROM students s JOIN enrollments e USING (student_id) -- same column name JOIN courses c ON c.id = e.course_id; |
→ Cleaner when foreign key column has same name as primary key (common pattern)
4. When to use INNER JOIN (real decision checklist)
Use INNER JOIN when you want:
- Only records that have matching data in both tables
- “Students who actually enrolled”
- “Orders that have customer information”
- “Products that were actually sold”
- Any “exists in both” question
Do not use INNER JOIN when:
- You want all records from one side even if no match → use LEFT JOIN
- You want all records from both sides → FULL OUTER (rare)
5. Common mistakes & fixes (from real student queries)
| Mistake | Result / Error | Fix |
|---|---|---|
| Forget ON clause | Syntax error | Always write ON or USING |
| Wrong column in ON | No rows returned or wrong matches | Double-check foreign_key = primary_key |
| Use INNER when you need all rows | Missing rows | Switch to LEFT JOIN |
| Join on wrong tables | Cartesian explosion or nonsense | Draw the relationship first |
| No table aliases | Very long & unreadable query | Use s, e, c, o, etc. |
Your mini homework right now
- Write INNER JOIN query: all enrollments with student name + course name + grade
- Add GROUP BY: count how many courses each student is enrolled in
- Try to write it without table aliases → see how ugly it gets
- Add WHERE clause: only show enrollments where grade is ‘A’ or ‘A+’
Next class?
Tell me:
- Want to compare INNER JOIN vs LEFT JOIN with same data (see missing rows)?
- Multiple INNER JOINs (3–4 tables chain)?
- INNER JOIN + aggregate functions (SUM, AVG, COUNT)?
- Performance of INNER JOIN vs subquery vs EXISTS?
Your guru is ready — what’s the next topic? 🚀
