What is the basic difference in between them in terms of resultant row after the join operation...
A bit curious on this.. I know the join strategy for both are same
I also think that Cartesian Product join is a type of Product join
but what is really their difference?
Product Join - there is WHERE clause
Cartesian Product Join - no WHERE clause?
Cartesian Product Join is a TYPE of Join
Product Join is a Join Strategy chosen by the optimizer.
If you don't specify a join condition between two tables, it is a Cartesian Product Join type that you are specifying. The join strategy will be a Product join to perform this Cartesian Product Join.
But there are scenarios, where you specify proper join conditions. But optimizer thinks it is less resource consuming if the smaller table is duplicated over all the AMPs and performs a PRODUCT join. [This scenario may arise when the PI of the tables are not matching/covered by the Joining Columns and one table is very small as compared to the other table]
Hope this helps..
Its true that cartesian join is one type of join and product join is a join stratgey (used by the optimizer during compile time).So basically cartesian join is preferable when you do not have any join condition and redistributes rows to all the amps which is very time taking and costly operation,but in product join you can have join condition so that it makes distribution easier(preferable when you have smaller table joining to larger table as it duplicates the smaller table rows to amps then join with larger table in spool) which is less time taking and less expensive also.
Cartesian product is simply a product join without any constraint (WHERE Caluse). Cartesion products are basically used for bench marking system performanc. else cartesien product should be avoided. Products joins should also be avoided, but can be used if no other option is available.