mysqlmissing-datanatural-join

Natural join works but not with all values

I can't understand whats happening...
I use two sql queries which do not return the same thing...
this one : 

SELECT * FROM table1 t1 JOIN table1 t2 on t1.attribute1 = t2.attribute1

I get 10 rows

this other : 

SELECT * FROM table1 NATURAL JOIN table1

I get 8 rows

With the NATURAL JOIN 2 rows aren't returned... I look for the missing lines and they are the same values ​​for the attribute1 column ...

It's impossible for me. If anyone has an answer I could sleep better ^^

Best regards Max

Solution

  • As was pointed out in the comments, the reason you are getting a different row count is that the natural join is connecting your self join using all columns. All columns are being compared because the same table appears on both sides of the join. To test this hypothesis, just check the column values from both tables, which should all match.

    The moral of the story here is to avoid natural joins. Besides not being clear as to the join condition, the logic of the join could easily change should table structure change, e.g. if a new column gets added.

    Follow the link below for a small demo which tried to reproduce your current results. In a table of 8 records, the natural join returns 8 records, whereas the inner join on one attribute returns 10 records due to some duplicate matching.

    Demo