Chapter 35: PostgreSQL FULL JOIN
PostgreSQL FULL JOIN
INNER JOIN → only matching rows from both sides- LEFT JOIN → all from left + matching from right
- RIGHT JOIN → all from right + matching from left
Now we reach the fourth and rarest of the standard SQL join types: FULL JOIN (also called FULL OUTER JOIN).
This is the “please show me everything from both tables — no matter what” join.
1. What does FULL JOIN actually do? (Very honest teacher explanation)
FULL OUTER JOIN returns:
- All rows from the left table
- All rows from the right table
- Matching rows are combined where the join condition is true
- Where there is no match → the missing side gets NULL values
In set terms: it is the union of LEFT JOIN and RIGHT JOIN (with duplicates removed where both sides match).
Visual metaphor (imagine two circles again):
- Left circle = students
- Right circle = courses
- FULL JOIN → both entire circles (union) → You see every student and every course — even students with no courses and courses with no students
Official PostgreSQL documentation wording:
FULL OUTER JOIN returns all rows from both tables, with NULLs in places where the join condition is not satisfied.
2. Basic syntax (very straightforward)
SQL
|
0 1 2 3 4 5 6 7 8 9 |
SELECT … FROM left_table FULL [OUTER] JOIN right_table ON left_table.foreign_key = right_table.primary_key; |
- OUTER is optional — almost everyone writes just FULL JOIN
- The order of tables matters less than with LEFT/RIGHT because both sides are fully preserved
3. Real example – using our familiar college schema
Tables (same as before):
students
| 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
| 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 — we’ll join through it)
| student_id | course_id | grade |
|---|---|---|
| 1 | 1 | A |
| 1 | 2 | A+ |
| 2 | 1 | B+ |
| 4 | 3 | A |
| 5 | 2 | B |
→ Rahul (3) has no enrollments → Course AI101 (4) has no enrollments
Example 1: FULL JOIN – show every student and every course (via enrollments)
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, COALESCE(e.grade, '—') AS grade, COALESCE(e.enrolled_at::text, '—') AS enrolled_at FROM students s FULL JOIN enrollments e ON s.id = e.student_id FULL JOIN courses c ON c.id = e.course_id ORDER BY student_name NULLS LAST, course_name NULLS LAST; |
Result (simplified & sorted for clarity):
text
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 Rahul Sharma | — | — | — Sneha Kumar | Machine Learning Basics | A | 2025-08-05 Vikram Naik | Database Systems | B | 2025-08-10 — | Artificial Intelligence | — | — |
→ Rahul appears (no courses) → Artificial Intelligence appears (no students) → All enrolled combinations are shown normally
4. When do people actually use FULL OUTER JOIN? (very rare cases)
FULL JOIN is not common in production code (usually < 1–2% of all JOINs).
You see it when:
- You need a complete reconciliation report (“show me everything — what’s only in A, only in B, and in both”)
- Comparing two similar tables (data migration, audit, diff reports)
- Generating a full matrix / calendar / roster including empty slots
- Some financial / inventory reports (“all accounts + all transactions”)
- Academic examples / certification exams that test all join types
In normal CRUD applications (web apps, e-commerce, college management) → you almost never need FULL JOIN.
Most of the time people want:
- INNER → “only active relationships”
- LEFT → “all main entities + optional details”
5. FULL JOIN vs LEFT JOIN vs RIGHT JOIN – same data comparison
| Join Type | Students shown | Courses shown | Rows (approx) | Typical business question |
|---|---|---|---|---|
| INNER JOIN | Only enrolled | Only enrolled courses | 5 | “Which students enrolled in which courses?” |
| LEFT JOIN | All students | Only enrolled courses | 6 | “Every student — show courses if they have any” |
| RIGHT JOIN | Only enrolled | All courses | 6 | “Every course — show enrolled students if any” |
| FULL OUTER JOIN | All students | All courses | 7 | “Every student and every course — show connections” |
6. Common mistakes & real tips (2026 production wisdom)
| Mistake / Pattern | Problem / Surprise | Fix / Recommendation |
|---|---|---|
| Use FULL when LEFT would do | Unnecessary rows + harder to read | Prefer LEFT JOIN — rewrite by changing table order |
| Forget COALESCE / NULL handling | Ugly NULLs in reports | Always use COALESCE(col, ‘N/A’) or — in production |
| FULL JOIN on large tables without indexes | Very slow (can be quadratic in worst case) | Index both join columns (especially foreign keys) |
| WHERE on right table columns | Turns FULL → LEFT (filters out non-matches) | Move conditions to ON clause or use subquery |
| Expect FULL JOIN to be symmetric | Order of tables still affects NULL placement | Test both orders if presentation matters |
Your mini homework right now
- Write a FULL JOIN query showing all students and all courses (through enrollments)
- Use COALESCE to replace NULL student_name with ‘No student’ and NULL course_name with ‘No course’
- Modify it to show only unmatched rows (WHERE s.id IS NULL OR c.id IS NULL)
- Rewrite the same result using UNION of LEFT + RIGHT JOIN (alternative way to simulate FULL JOIN)
Next class?
Tell me:
- Want to see CROSS JOIN (cartesian product) next?
- SELF JOIN examples (same table to itself)?
- How to simulate FULL JOIN when database doesn’t support it (some old systems)?
- JOIN performance deep dive + EXPLAIN ANALYZE examples?
- Or move to subqueries, EXISTS, CTE that often replace complex joins?
Your teacher is ready — what’s the next topic? 🚀
