mysqljoinnatural-join

Natural join returning zero rows in mysql

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

  • 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.