Quick Natural Join Question

[slindstr]

As I understand it, if you do a natural join between two tables, the result will be all of the columns from both tables without duplicates

Example
Table1 has the columns: ID, Cats, Dogs, Birds
Table2 has the columns: ID, Cats, Tigers, Lions

SELECT * FROM Table1 NATURAL JOIN Table2 WHERE Table1.ID = Table2.ID

should (if I understand Natural Join correctly) yield the following columns:

ID, Cats, Digs, Birds, Tigers, Lions

Is this correct?

This is my PHP code:

<?php

require_once("include/mySQLconnect.php");

$row; //array containing result from mysql_fetch_array

$query = "SELECT * FROM Table1 NATURAL JOIN Table2 WHERE Table1.ID = Table2.ID";

$result = mysql_query($query);

if($result)
  $row = mysql_fetch_array($result);
else
  echo mysql_error($result);

if(isset($row))
{
  foreach($row as $item)
    echo $item . "<br>";
}
?>

The problem I'm having is that 15 elements are being placed in $row (in this order):
Table1.ID
Table1.ID
Table1.Cats
Table1.Cats
Table1.Dogs
Table1.Dogs
Table1.Birds
Table1.Birds
Table2.ID
Table2.Cats
Table2.Cats
Table2.Tigers
Table2.Tigers
Table2.Lions
Table2.Lions

how do I fix this?

Thanks
Steve

[Lars_Berg]

You are correct in your understanding of a natural join. This piece of wisdom has not been grasped by the Mysql development team though. You are better of using a qualified join with an explicit column list.

[slindstr]

Thanks for the quick reply.

By qualified join you mean left Join, right join, inner join, or outer join?

[slindstr]

I was just messing with the query again - when I type it in PHPMyAdmin it gets me the correct rows - is there something wrong with my PHP code maybe?

[Lars_Berg]

Yes and with an on clause. The Mysql implementation of the using clause has the same flaw.