As I understand it, CROSS JOIN is essentially a cross product which produces a Cartesian Product. Are INNER JOIN, RIGHT JOIN, LEFT JOIN, OUTER JOIN Cartesian products as well except for fact that they don't produce duplicates and have some condition applied to them?


Note: I don't believe this is a duplicate. The link does not elaborate on the difference to the detail that I was looking for. It's left up to the reader to dig through & infer the differences. The answer I've provided below will hopefully save the reader some time.

2 Answers

Tigran Petrosyan On

A cross join matches all rows in one table to all rows in another table. An inner join matches on a field or fields. If you have one table with 10 rows and another with 10 rows then the two joins will behave differently.

The cross join will have 100 rows returned and they won't be related, just what is called a Cartesian product. The inner join will match records to each other. Assuming one has a primary key and that is a foreign key in the other you would get 10 rows returned.


Note: You can see more information from this link

Jacob L On

The JOIN operation can be specified as a CARTESIAN PRODUCT operation followed by a SELECT operation.


The result of the JOIN is a relation Q with n + m attributes Q(A1, A2, ... , An, B1, B2, ... , Bm) in that order; Q has one tuple for each combination of tuples—one from R and one from S—whenever the combination satisfies the join condition. This is the main difference between CARTESIAN PRODUCT and JOIN. In JOIN, only combinations of tuples satisfying the join condition appear in the result, whereas in the CARTESIAN PRODUCT all combinations of tuples are included in the result. The join condition is specified on attributes from the two relations R and S and is evaluated for each combination of tuples. Each tuple combination for which the join condition evaluates to TRUE is included in the resulting relation Q as a single combined tuple.

Source: Fundamentals of Database Systems (7th edition), Elmasri