mysql multiple queries

Question

Query:

SELECT c.Name
  FROM [Catagories] c

    SELECT c.Name,
           bc.CategoryName
      FROM [Catagories] c
INNER JOIN [Business_Categories] bc ON c.BusinessCategoryID = bc.BusinessCategoryID

Question: Why don't I have to put this in a WHERE clause?

Answer 1

You can do joins in the where clause, but it's better to use the syntax you have.

Example of Query:

SELECT 
  c.Name,
  bc.CategoryName
FROM [Categories] c
     [Business_Categories] bc
WHERE
  c.BusinessCategoryID = bc.BusinessCategoryID

Answer 2

The ON clause is a bit like a WHERE clause in that it filters the results, but it is a more expressive way to write the query because you are showing explicitly that the two tables are related in a very specific way. You could have written a WHERE clause instead and it would give the same result and the same query plan (assuming the optimizer can figure it out), but using JOINs is recommended for clarity.

In this case it doesn't make much difference, but if you had a more complex query with many tables, with the JOIN syntax you can see which tables are related and on which fields. You can also clearly see at a glance the difference between the JOINs and the WHERE clauses used to filter the results. If you write everything in the WHERE clause it can get more confusing to see what the intention of the query is.