Chapter 36: PostgreSQL CROSS JOIN
PostgreSQL: CROSS JOIN.
You’ve already seen INNER, LEFT, RIGHT, FULL — all of them try to match rows between two tables using some condition (ON or USING).
CROSS JOIN does not match anything.
It says:
“Give me every possible combination of one row from the left table and one row from the right table.”
No ON clause. No condition. Just pure cartesian product.
1. What is a CROSS JOIN? (Teacher drawing on board)
Imagine:
- Left table = 4 students
- Right table = 3 courses
CROSS JOIN = 4 × 3 = 12 rows Every student is paired with every course — whether they enrolled or not.
Mathematically:
If table A has m rows and table B has n rows → CROSS JOIN result has m × n rows
This is why it’s also called Cartesian product (named after René Descartes).
2. Basic syntax (very short — because there’s almost nothing to write)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT … FROM table1 CROSS JOIN table2; -- Exactly the same — old comma syntax (still legal) SELECT … FROM table1, table2; |
No ON, no USING — that’s the point.
PostgreSQL also lets you write:
|
0 1 2 3 4 5 6 |
FROM table1 CROSS JOIN table2 |
3. Real example – using our college schema
Tables:
students (5 rows)
| id | first_name | last_name |
|---|---|---|
| 1 | Aarav | Patel |
| 2 | Priya | Reddy |
| 3 | Rahul | Sharma |
| 4 | Sneha | Kumar |
| 5 | Vikram | Naik |
courses (4 rows)
| id | code | name |
|---|---|---|
| 1 | CS101 | Introduction to Programming |
| 2 | DBMS01 | Database Systems |
| 3 | ML101 | Machine Learning Basics |
| 4 | AI101 | Artificial Intelligence |
Example 1: Simple CROSS JOIN – all possible student–course pairs
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT s.first_name || ' ' || s.last_name AS student, c.code, c.name AS course FROM students s CROSS JOIN courses c ORDER BY student, c.code LIMIT 12; -- just first 12 rows — there are 20 total! |
Result (partial):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
student | code | course_name --------------|--------|--------------------------------- Aarav Patel | AI101 | Artificial Intelligence Aarav Patel | CS101 | Introduction to Programming Aarav Patel | DBMS01 | Database Systems Aarav Patel | ML101 | Machine Learning Basics Priya Reddy | AI101 | Artificial Intelligence Priya Reddy | CS101 | Introduction to Programming ... (and so on — 5 students × 4 courses = 20 rows) |
→ Every student is listed with every course → Even Rahul (who never enrolled) is paired with AI101 → Even AI101 (which nobody took) is paired with everyone
4. When do people actually use CROSS JOIN? (real use-cases)
CROSS JOIN is rare in production — but it has very specific legitimate uses:
- Generate all possible combinations
- All colors × all sizes for product variants
- All time slots × all rooms for scheduling
- All regions × all products for reporting empty cells
- Create test data / dummy rows
- Generate fake combinations quickly
- Cross product in analytical queries
- Compare every user to every campaign
- Matrix-style reports
- With LATERAL (advanced — very powerful)
- CROSS JOIN LATERAL is common when you want a subquery per row
Example – generate all possible student–course recommendation pairs:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT s.first_name || ' ' || s.last_name AS student, c.name AS recommended_course FROM students s CROSS JOIN courses c WHERE NOT EXISTS ( SELECT 1 FROM enrollments e WHERE e.student_id = s.id AND e.course_id = c.id ) LIMIT 10; |
→ “Which courses has each student not taken yet?”
5. The BIG danger – performance disaster
Never run CROSS JOIN on large tables without LIMIT or filter!
| Left rows | Right rows | Result rows | Real danger level |
|---|---|---|---|
| 100 | 100 | 10,000 | Manageable |
| 1,000 | 1,000 | 1,000,000 | Slow + memory |
| 10,000 | 10,000 | 100,000,000 | Can crash server |
| 1 million | 1 million | 1 trillion rows | Instant OOM kill |
PostgreSQL will try to execute it — and if you forget LIMIT or a WHERE → your query can hang or crash the instance.
Rule #1 in production 2026:
If you write CROSS JOIN (or implicit comma join) → you must have a very good reason + LIMIT or strong filter.
6. Quick comparison table – all joins so far
| Join Type | Preserves… | Typical row count | Danger level | Real-world frequency |
|---|---|---|---|---|
| INNER JOIN | Only matches | Small–medium | Low | ★★★★★ (most common) |
| LEFT JOIN | All left + matches | Medium | Low | ★★★★☆ |
| RIGHT JOIN | All right + matches | Medium | Low | ★☆☆☆☆ (rare) |
| FULL OUTER JOIN | All from both | Medium–large | Medium | ★☆☆☆☆ (very rare) |
| CROSS JOIN | All × all (no condition) | Huge very fast | ★★★★★ (deadly) | ★★☆☆☆ (special cases) |
Your mini homework right now
- Run CROSS JOIN between students and courses → see how many rows you get (5 × 4 = 20)
- Add WHERE to show only combinations where student has not enrolled in that course
- Try CROSS JOIN between two tables with 10+ rows each → add LIMIT 100
- Think: what real report in a college system might need all possible student–course pairs?
Next class?
Tell me:
- Want SELF JOIN next (table joined to itself)?
- CROSS JOIN LATERAL (very powerful modern pattern)?
- How to safely generate combinations without killing the server?
- Or move to subqueries, EXISTS, CTE, WINDOW functions?
Your guru is ready — what’s the next topic? 🚀
