Chapter 34: PostgreSQL RIGHT JOIN
PostgreSQL RIGHT JOIN (also called RIGHT OUTER JOIN).
You’ve already seen:
- INNER JOIN → only matching rows from both sides
- LEFT JOIN → all rows from left table + matching from right
RIGHT JOIN is basically LEFT JOIN but flipped horizontally.
1. What does RIGHT JOIN actually do? (Very honest teacher explanation)
RIGHT JOIN returns:
- All rows from the right table (the table written after the RIGHT JOIN keyword)
- Matching rows from the left table (the table before RIGHT JOIN)
- If there is no match in the left table → the left side columns get NULL
In other words:
“I don’t care if some rows in the right table have no corresponding row on the left — show me every row from the right table anyway.”
Visual metaphor:
- Left circle = students
- Right circle = courses
- RIGHT JOIN → entire right circle + overlap → All courses are shown — even courses that nobody enrolled in
2. Basic syntax (notice the position matters!)
|
0 1 2 3 4 5 6 7 8 9 |
SELECT … FROM left_table RIGHT JOIN right_table ON left_table.foreign_key = right_table.primary_key; |
Important:
- The table written after RIGHT JOIN is the one that is fully preserved
- The table written before is the optional one (can produce NULLs)
You can also write it as:
|
0 1 2 3 4 5 6 |
RIGHT OUTER JOIN … -- OUTER is optional, almost never written |
3. Real example – using our college schema (same data as before)
Tables reminder:
students (left table in this query)
| id | first_name | last_name | |
|---|---|---|---|
| 1 | Aarav | Patel | aarav@hyd.edu |
| 2 | Priya | Reddy | priya@gmail.com |
| 3 | Rahul | Sharma | rahul@yahoo.com |
| 4 | Sneha | Kumar | sneha@gmail.com |
| 5 | Vikram | Naik | vikram@hyd.edu |
courses (right table — we want all 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
| student_id | course_id | grade |
|---|---|---|
| 1 | 1 | A |
| 1 | 2 | A+ |
| 2 | 1 | B+ |
| 4 | 3 | A |
| 5 | 2 | B |
→ Course AI101 (id=4) has no enrollments
Example 1: Basic RIGHT JOIN – all courses + who enrolled (if anyone)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT c.name AS course_name, c.code, s.first_name || ' ' || s.last_name AS student_name, e.grade FROM students s RIGHT JOIN enrollments e ON s.id = e.student_id RIGHT JOIN courses c ON c.id = e.course_id ORDER BY course_name; |
Result:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
course_name | code | student_name | grade -----------------------------------|--------|----------------|------- Artificial Intelligence | AI101 | NULL | NULL Database Systems | DBMS01 | Aarav Patel | A+ Database Systems | DBMS01 | Vikram Naik | B Introduction to Programming | CS101 | Aarav Patel | A Introduction to Programming | CS101 | Priya Reddy | B+ Machine Learning Basics | ML101 | Sneha Kumar | A |
→ Artificial Intelligence appears even though nobody enrolled → Left side (student_name, grade) is NULL for that course
4. Why RIGHT JOIN is rarely used (very important reality check)
In almost every real-world case you can rewrite a RIGHT JOIN as a LEFT JOIN by simply swapping the order of the tables.
Same result as above using LEFT JOIN (more common style):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT c.name AS course_name, c.code, s.first_name || ' ' || s.last_name AS student_name, e.grade FROM courses c LEFT JOIN enrollments e ON c.id = e.course_id LEFT JOIN students s ON s.id = e.student_id ORDER BY course_name; |
→ Exactly same output → Most developers/team standards prefer LEFT JOIN because:
- Reading direction is left-to-right → main table on left
- Easier to chain multiple joins
- Most code style guides recommend LEFT over RIGHT
5. When do people actually use RIGHT JOIN? (rare cases)
You might see RIGHT JOIN in:
- Legacy code that was written before the team standardized on LEFT
- Very specific reporting where the right table is the “anchor” (e.g. all time-slots, even empty ones)
- Some auto-generated SQL from ORMs/tools
- Academic examples / exam questions that want to teach all join types
In production code written in 2024–2026 → RIGHT JOIN is extremely rare (less than 5% of JOINs in most codebases).
6. Quick comparison table – same data, different joins
| Join Type | Rows returned | Courses shown | Students shown | Use-case feeling |
|---|---|---|---|---|
| INNER JOIN | 5 | Only enrolled courses | Only enrolled students | “Only courses that have enrollments” |
| LEFT JOIN | 6 | All courses | All students | “All students + their enrollments if any” |
| RIGHT JOIN | 6 | All courses | Only enrolled students | “All courses + enrolled students if any” |
| FULL OUTER JOIN | 7 | All courses | All students | “Every student and every course” (rare) |
Your mini homework right now
- Write a RIGHT JOIN query showing all courses + enrolled students (use COALESCE for NULL student names)
- Rewrite the same query using LEFT JOIN by swapping table order → compare results
- Add WHERE clause to show only courses with no enrollments (use WHERE s.id IS NULL)
- Try RIGHT JOIN on students RIGHT JOIN enrollments → see how it behaves
Next class?
Tell me:
- Want to see FULL OUTER JOIN next (the rare one)?
- Multiple joins chain with LEFT + RIGHT mixed?
- How to find “missing” records using LEFT/RIGHT + IS NULL?
- JOIN performance differences + when to index join columns?
Your teacher is ready — what’s the next topic? 🚀
