Wednesday, April 11, 2012

Do we need cartesian join?

I have written so many SQL script and non of them requires cartesian join based on business requirement. I have seen union, left and right join. Cartesian join? No where in sight.

However, the cartesian join was found in mistake where developer has forgotten to put in the necessary join condition between the tables. This has caused the query to join all the rows from each other and resources has been wasted due to this mistake.

My questions for this join in database system as follows:
  1. Why the database system allows cartesian join since it was not used most of the time? 
  2. Why can't cartesian join be required to specify in the SQL script such as union, left or right join? 
  3. Why can't the database system alert  (an error will be sufficient) the developer that one specific table has no relationship or filter defined and therefore, the table is no longer required?
My guess on the 3rd point is that it was too complex to write the logic to catch cartesian join. Perhaps there are tools out there that providing this feature. However,  I do hope in future database system, all cartesian join should be required to specify in the query.