naural join is giving Cartesian product of two relations

Question

I have two relations, emp(id,name) and emp2(name,city,salary) with values:

and relation emp2 values :

As you can see, when I run natural join between them I get their Cartesian product. But why, since they have a common attribute (name)?

Answer 1

You have two time the same value in the common column (name) so this retrive the rows more time . Your natural join is based on both the table that have not unique value each row. Then in this case you get always more that a row for each entry (also with inner join) but you could solve using distinct

select dictinct emp.*, emp2.* 
from emp 
inner join emp2 on emp.name = emp2.name 

or

select select dictinct emp.*, emp2.* 
from emp2 natural join emp

Answer 2

You are getting natural join, not Cartesian product.

There is one copy of each common column and one copy of each column unique to an input table. There is a row for every different combination of a row from the left input table and a row from a right input table.

If a subrow value (('kim')) for common columns (name) appears n times in the left table (2 times) and m times in the right table (2 times) then it will appear n times m times in the output (2 times 2 times = 4 times).

An SQL Cartesian product would have columns name, city, salary, id & name and would have a row for every different combination of a row from the left input table and a row from a right input table.

That includes every possible combination of a name value from the left table and the right table. But for this example data there is only one name value over both tables, so every pair of name values has to have that value twice.

Natural join is the same as Cartesian product followed by restriction on equality of common columns followed by projecting away one of each common column followed by keeping distinct rows.

Answer 3

select * from emp inner join emp2 on emp.id = emp2.id