Difference between Product Join and Cartesian Product Join

General
Enthusiast

Difference between Product Join and Cartesian Product Join

Hi !

I want to know if there any difference between Product Join and Cartesian Product Join ?
If not then why do we have separate Join strategies as Product and Cartesian in Teradata ?

Thanks
Nitin 'Raj' Srivastava
9 REPLIES
Junior Contributor

Re: Difference between Product Join and Cartesian Product Join

There is no difference and there are no separate join strategies.
Why do you think so?

Dieter
Enthusiast

Re: Difference between Product Join and Cartesian Product Join

I read it in some book , separate Join strategies as Product and Cartesian.
Anyways Thanks for sharing the info.
Enthusiast

Re: Difference between Product Join and Cartesian Product Join

I believe Cartesian product join is unconstrained product join , i.e with out join and WHERE clause.
Enthusiast

Re: Difference between Product Join and Cartesian Product Join

The main difference between Product join and Cartesian Product Join is:

Product join - Where clause we will use
Cartesian Product Join - No where clause
Enthusiast

Re: Difference between Product Join and Cartesian Product Join

What is the basic difference in between them in terms of resultant row after the join operation...

Enthusiast

Re: Difference between Product Join and Cartesian Product Join

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?

Enthusiast

Re: Difference between Product Join and Cartesian Product Join

Cartesian Product Join is a TYPE of Join

where as

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..

Thanks, Manish

Re: Difference between Product Join and Cartesian Product Join

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.

Enthusiast

Re: Difference between Product Join and Cartesian Product Join

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. 

Khurram