What is the difference between cartisean product and natural join operation?

Answer 1

A Cartesian join is when you join every row of one table to every row of another table. While on occassion this may be needed I would argue that a properly constructred database would negate this. This is in fact a VERY hard hitting operation on your resources and should be avoided at any cost. A normal join will include the comparison of one or two fields between tables. While you want to limit the number of joins you have in a query a properly constructed join is not a big use on resources. This of course takes into consideration proper indexing and database construct.

Answer 2

In a Cartesian product, every row in one table is combined with every row in the other table, resulting in a new table with all possible combinations. In a natural join, rows from two tables are combined based on common columns, resulting in a table that retains only the rows with matching values in those columns.