CROSS JOIN
CROSS JOIN produces the Cartesian product - every row from the left table combined with every row from the right table. 3 rows × 4 rows = 12 result rows. It has few practical use cases but is essential to understand.
CROSS JOIN Key Points
- No ON clause - no join condition
- Result count = rows in table A × rows in table B
- 3 users × 3 products = 9 rows
- Use cases: generate combinations, date-range calendars, scheduling grids
- Accidental CROSS JOIN (forgotten ON) can crash databases with huge tables
CROSS JOIN Examples
-- Every user paired with every product (9 combinations)
SELECT
u.name AS user_name,
p.name AS product_name,
p.price
FROM users u
CROSS JOIN products p;
-- 3 users × 3 products = 9 rows
-- Practical use: generate all possible order assignments
-- to check which combinations exist vs don't
-- Generate a date series (cross join with calendar table)
-- Common for reports: show all months even with $0 revenue
-- Cross join as implicit (comma syntax - avoid this!)
-- This is the same as CROSS JOIN (old SQL syntax):
SELECT u.name, p.name FROM users u, products p;
-- AVOID the comma syntax - very confusing and error-proneQuick Quiz
Tip
Tip
Practice CROSS JOIN in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Inner query runs first, results used by outer query. CTEs are often more readable.
Common Mistake
Warning
A common mistake with CROSS JOIN is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of CROSS JOIN from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- CROSS JOIN produces the Cartesian product - every row from the left table combined with every row from the right table.
- No ON clause - no join condition
- Result count = rows in table A × rows in table B
- 3 users × 3 products = 9 rows