Chapter 31: PostgreSQL JOINS
PostgreSQL JOINs ☕🧩
You’ve already learned:
- CREATE TABLE, INSERT, SELECT, WHERE, GROUP BY, AVG, LIKE, IN, BETWEEN, AS…
Now we reach the real power of relational databases: combining data from multiple tables using JOIN.
Without JOINs → you would have to copy-paste data everywhere (bad design). With JOINs → you keep data normalized (clean, no duplication) and still ask powerful questions like:
- “Which students are enrolled in which courses?”
- “Which customers bought which books and how much did they spend?”
- “Show me all orders with customer details even if some customers never ordered”
1. What is a JOIN? (Honest teacher explanation)
A JOIN combines rows from two (or more) tables based on a matching condition (usually a foreign key = primary key).
PostgreSQL supports six main types of JOIN in 2026 (version 18):
| JOIN Type | What it returns | When you use it (most common feeling) | Visual metaphor (teacher drawing on board) |
|---|---|---|---|
| INNER JOIN | Only rows where there is a match in both tables | “Show only students who are enrolled in at least one course” | Only the overlapping part of two circles |
| LEFT JOIN | All rows from left table + matching rows from right (NULLs if no match) | “Show all students — show courses if they have any” | All of left circle + overlap |
| RIGHT JOIN | All rows from right table + matching rows from left (NULLs if no match) | Rarely used — usually you swap tables and use LEFT instead | All of right circle + overlap |
| FULL OUTER JOIN | All rows from both tables (NULLs where no match) | “Show every student and every course — even if not connected” | Union of both circles (almost never used) |
| CROSS JOIN | Cartesian product — every row from left × every row from right | Rare — mostly for generating combinations | Every possible pairing |
| SELF JOIN | Join a table to itself (not a separate type — just using any join type) | “Find students who have the same GPA as another student” | Table talks to itself |
Most important rule in 2026:
- 90%+ of JOINs in real applications are INNER JOIN or LEFT JOIN
- RIGHT JOIN and FULL JOIN are very rare — you can almost always rewrite them as LEFT JOIN by swapping table order
2. Basic syntax skeleton (memorize this shape)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT table1.column1, table2.column2, ... FROM table1 [INNER] JOIN table2 ON table1.foreign_key = table2.primary_key [LEFT|RIGHT|FULL] [OUTER] JOIN table3 ON ... [WHERE ...] [GROUP BY ...] [ORDER BY ...]; |
- ON clause = the matching condition (almost always equality)
- You can use USING(column) if column names are identical
3. Real example setup – let’s use our college schema
Tables (simplified):
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 |
courses
| id | code | name | credits |
|---|---|---|---|
| 1 | CS101 | Introduction to Programming | 4 |
| 2 | DBMS01 | Database Systems | 4 |
| 3 | ML101 | Machine Learning Basics | 3 |
enrollments (junction table)
| id | student_id | course_id | grade |
|---|---|---|---|
| 1 | 1 | 1 | A |
| 2 | 1 | 2 | A+ |
| 3 | 2 | 1 | B+ |
| 4 | 4 | 3 | A |
Rahul (id=3) has no enrollments.
4. All JOIN types — live examples
4.1 INNER JOIN (default — most common)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT s.first_name || ' ' || s.last_name AS student, c.name AS course, e.grade 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, course; |
Result:
text
|
0 1 2 3 4 5 6 7 8 9 10 11 |
student | course | grade ------------|-------------------------------|------- Aarav Patel | Database Systems | A+ Aarav Patel | Introduction to Programming | A Priya Reddy | Introduction to Programming | B+ Sneha Kumar | Machine Learning Basics | A |
→ Rahul is missing because he has no enrollments
4.2 LEFT JOIN (show all students — even without courses)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT s.first_name || ' ' || s.last_name AS student, c.name AS course, COALESCE(e.grade, 'Not enrolled') AS grade FROM students s LEFT JOIN enrollments e ON s.id = e.student_id LEFT JOIN courses c ON c.id = e.course_id ORDER BY student; |
Result:
text
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
student | course | grade ------------|-------------------------------|--------------- Aarav Patel | Database Systems | A+ Aarav Patel | Introduction to Programming | A Priya Reddy | Introduction to Programming | B+ Rahul Sharma| NULL | Not enrolled Sneha Kumar | Machine Learning Basics | A |
→ Rahul appears with NULLs — very common pattern
4.3 RIGHT JOIN (rare — usually rewritten as LEFT)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 |
SELECT s.first_name, c.name FROM students s RIGHT JOIN courses c ON FALSE; -- just demo — normally not useful here |
→ Almost never used — swap tables and use LEFT instead
4.4 FULL OUTER JOIN (rare — both sides + non-matches)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT s.first_name, c.name FROM students s FULL OUTER JOIN enrollments e ON s.id = e.student_id FULL OUTER JOIN courses c ON c.id = e.course_id; |
→ Shows unmatched students and unmatched courses (very rare need)
4.5 CROSS JOIN (cartesian product — careful!)
SQL
|
0 1 2 3 4 5 6 7 8 9 |
SELECT s.first_name, c.name FROM students s CROSS JOIN courses c LIMIT 6; |
→ 4 students × 3 courses = 12 rows (every possible combination)
5. Quick cheat-sheet (keep in your notes)
| You want to see… | Best JOIN type | Typical pattern |
|---|---|---|
| Only matching records | INNER JOIN | INNER JOIN … ON fk = pk |
| All from left table + matches from right | LEFT JOIN | LEFT JOIN … ON … |
| All customers + their orders (even if none) | LEFT JOIN | customers c LEFT JOIN orders o ON … |
| Rare — all from both sides | FULL OUTER JOIN | FULL OUTER JOIN … |
| Every combination (testing, generation) | CROSS JOIN | CROSS JOIN … |
| Compare rows in same table | SELF JOIN | students s1 JOIN students s2 ON s1.id <> s2.id |
Your mini homework right now
- Write INNER JOIN query: students + courses they enrolled in
- Write LEFT JOIN query: all students + their enrollments (use COALESCE for NULL grades)
- Try to find students who have no enrollments (LEFT JOIN + WHERE right_table.id IS NULL)
Next class?
Tell me:
- Want deeper examples with multiple JOINs (3–4 tables)?
- USING() vs ON syntax?
- NATURAL JOIN (dangerous — avoid)?
- JOIN performance tips + indexes?
- Or move to subqueries / EXISTS / CTE with JOINs?
Your teacher is ready — what’s next? 🚀
