Natural join if no common attributes

Question

What will natural join return in relational algebra if tables don't have attributes with same names? Will it be null or the same as cross join (cross product) (Cartesian product)?

Tonsure

Answer 1

If there are no attributes in common between two relations and you perform a natural join, it will return the cartesian product of the two relations.

Propulsion

A natural join is a join where all columns in both tables are compared and columns with the same name in either table are joined. The result is a table with a single column for each of these paired tables. If there are no columns in common, there is nothing to be joined, so everything gets joined with everything - i.e. a cartesian product. – Propulsion

It joins the info on the columns if they have the same name. It then joins all other columns based on this. So if table A has column 1 and 2 and table B has column 2, 3 and 4, a cartesian product will create a single table with column 1, 2, 3, 4. It will determine how to match this based on the joining of the two column "2"s. If there are no common columns, the result set you are left with is everything in table a and everything in table b - hence every tuple in table b gets joined with the first tuple in table a, then every tuple gets joined with the second tuple in table a and so on. – Propulsion

Here's an example of the natural join: en.wikipedia.org/wiki/… And then another example, this time of a cartesian product: en.wikipedia.org/wiki/Cartesian_product#Examples – Propulsion

A follow-up question: A natural join is classified as an "inner" join. My colloquial definition of an inner join is that only values where a match is produced in the join condition are kept. In the case of no matching columns, nothing is matched. Wouldn't that operation violate the "inner" criteria? – Archive

Answer 2

The cartesian product of the two tables will be returned. This is because, when we perform any JOIN operation on two tables, a cartesian product of those tables is formed. Then, based on any select condition in the WHERE clause, the resultant rows are returned. But here, as there are no common columns, the process stops after finding the cartesian product.

Dariodariole

"Then, based on any select condition in the WHERE clause, the resultant rows are returned." False. Natural join does not return a restriction/selection of a Cartesian product when there are common columns. It returns what can roughly be described as a certain projection of a restriction/selection of a Cartesian product--although describing it correctly depends on the algebra's relations & operators. – Overspread

Answer 3

It will return the cartesian product of the tables. If there are any common attributes, then the natural join removes the duplicate attributes.

Iodine