mysql natural join not working

Question

I have two tables in mysql server. I use these tables for studing JOIN multiple tables but something appears to be incorrect:

mysql> select * from category;
+-------------+-----------+
| category_id | name      |
+-------------+-----------+
|           1 | fruit     |
|           2 | vegetable |
+-------------+-----------+
2 rows in set (0.00 sec)

mysql> desc category;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| category_id | int(11)     | NO   | PRI | NULL    | auto_increment |
| name        | varchar(50) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

And:

mysql> select * from goods;
+---------+--------+-------------+------+
| good_id | name   | category_id | cost |
+---------+--------+-------------+------+
|       1 | banan  |           1 | 1.00 |
|       2 | potato |           2 | 1.00 |
|       3 | peach  |           1 | 1.00 |
+---------+--------+-------------+------+
3 rows in set (0.00 sec)

mysql> desc goods;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| good_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(100) | NO   |     | NULL    |                |
| category_id | int(11)      | NO   | MUL | NULL    |                |
| cost        | decimal(6,2) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

The second table has foreign key (category_id) and I can join them using INNER JOIN:

mysql> select c.name category, g.name, g.cost from category as c INNER JOIN goods g ON c.category_id = g.category_id;
+-----------+--------+------+
| category  | name   | cost |
+-----------+--------+------+
| fruit     | banan  | 1.00 |
| vegetable | potato | 1.00 |
| fruit     | peach  | 1.00 |
+-----------+--------+------+
3 rows in set (0.00 sec)

I tried to use NATURAL JOIN but it didnt work and it seems I dont know why(((

mysql> select c.name, g.name, g.cost from category as c NATURAL JOIN goods g;
Empty set (0.00 sec)

Could somebody explain why NATURAL JOIN does not work?

Answer 1

I was having the exact same thing happen to me, and my Googling led me to this question. I eventually figured it out, so I figured I'd post my answer here.

This was the culprit:

Instead of specifying a join condition through ON, USING or a WHERE clause, the NATURAL keyword tells the server to match up any column names between the two tables, and automatically use those columns to resolve the join.

Your fruit and category tables both have a column called "name". When SQL tries to join the two, it tries to join all like columns. So thus, category_id==category_id, but name!=name.

Rename your columns tablename_column instead.