Difference between cross product (cross join, Cartesian product) and natural join

Question

While writing in SQL, how would I know if I should use cross product (cross join, Cartesian product) or natural join?

Answer 1

CROSS JOIN creates all possible pairings of rows from two tables, whether they match or not. You don't use any join condition for a cross product, because the condition would always be true for any pairing.

An example of using CROSS JOIN: you have tables of ShoeColors and ShoeSizes, and you want to know how many possible combinations there are. SELECT COUNT(*) FROM ShoeColors CROSS JOIN ShoeSizes;

NATURAL JOIN is just like an INNER JOIN, but it assumes the condition is equality and applies for all columns names that appear in both tables. I never use NATURAL JOIN, because I can't assume that just because columns have the same name, that they should be related. That would require a very strict column naming convention, and practically no real-world project has such discipline.