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

sql cartesian-product cross-join natural-join
AamKhayega picture AamKhayega · Oct 11, 2014 · Viewed 15k times · Source

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

Answer

Bill Karwin picture Bill Karwin · Oct 11, 2014

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.

Related questions

Is there a good LINQ way to do a cartesian product?

I have a class structure like so: Person Dogs (dog 1, dog 2, etc) Puppies (puppy A, puppy B, etc) There is one person. He has 1..n dogs. Each dog has 1..n puppies. I want a list of all the possible combination …

Read More
Clear explanation of the "theta join" in relational algebra?

I'm looking for a clear, basic explanation of the concept of theta join in relational algebra and perhaps an example (using SQL perhaps) to illustrate its usage. If I understand it correctly, the theta join is a natural join with …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More