Menu
Cartesian Product Join - no WHERE clause? 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.
In case of Cartesian product First A table comprising of A,B,C is created and after that on the basis of what ever condition is given,we Get result. But as you see it's heavy process. On the other hand Inner join only chooses those result which are really fulfilling the given condition .
Natural join does not use any comparison operator. It does not concatenate the way a Cartesian product does. We can perform a Natural Join only if there is at least one common attribute that exists between two relations. In addition, the attributes must have the same name and domain.
I am studying for exams and am failing to find a solid criteria by which I can determine if the Cartesian Product x is to be used or if Natural Join |X| is to be used.
I had come up with a rough guide that:
"If you need to project an attribute that has the same name as an attribute in the table to be joined you must use x and state the table names to be projected: tableA.colname1 = tableB.colname1"
This however doesn't follow some of the solutions in my notes and my lecturer seems to use x with the above convention or |x| interchangeably.
Does anyone have a rule that can be followed to define use of one over the other?
Take for example this schema (only schema related to the question quoted for brevity):
takes(ID, course_id, sec_id, semester, year, grade)
student(ID, name, dept_name, tot_cred)Q) Find the name of all students who took courses that were taught in either Spring 2011 or Autumn 2011.
My answer attempt:
π name(σ semester="Spring" ^ year=2011(takes ⋈ student)) ∪ π name(σ semester="Autumn" ^ year=2011(takes ⋈ student))Actual answer:
π name(σ semester="Spring" ^ year=2011 ^ takes.ID=student.ID(takes x student)) ∪ π name(σ semester="Autumn" ^ year=2011 ^ takes.ID=student.ID(takes x student))Can anyone provide a reason as to why?
In my mind the Natural Join would take care of the takes.ID=student.ID?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With