Natural join returning zero rows in mysql

Question

Select book_id,title
from   Publisher
natural join Book natural join Category
where Publisher.name='thomas hardy' AND Category.name='computer science';

Answer 1

Just avoid natural join. It looks for columns that are the same, and you might miss one somewhere. For instance, almost all tables I create have a CreatedAt column. And that would cause NATURAL JOIN to fail.

Instead, include a USING clause or explicit ON:

Select book_id,title
from Publisher join
     Book 
     using (Publisher_Id) join
     Category
     using (Category_Id)
where Publisher.name = 'thomas hardy' AND Category.name = 'computer science';

In fact, the mystery is solved. The NATURAL JOIN will attempt to use name as a key. And, I'm guessing no publishers have a name that exactly matches a category name.

Answer 2

If you use a natural join it means that your tables will join on fields that share the same name. One of the fields in your Publisher table and your Category table is Name which means the natural join will join on these fields.

You then filter both fields by different criteria. After the two tables are joined, you can't have different values in the two fields.

It would be similar to writing:

Select foo FROM bar WHERE foo="something" and foo="Something Else"

It makes no sense. Natural joins are nice and all, but you are almost ALWAYS better off being explicit about your JOIN criteria, even if it feels a bit redundant. After all... comments in code are generally redundant, but only an asshat doesn't make them.