SQL Query - INNER JOIN - WHERE vs AND

Question

I was practising SQL queries and my solution to a question was:

SELECT C.*
FROM customer C
INNER JOIN salesman S ON C.salesman_id = S.salesman_id
  AND S.commission > 0.12
  AND S.city <> C.city;

And the solution given to this question was:

SELECT C.* 
FROM customer C  
INNER JOIN salesman S  ON C.salesman_id = S.salesman_id 
WHERE S.commission > .12 
  AND C.city <> S.city;

However there is no difference between the two outputs.

So I wanted to understand when should I use WHERE clause and ANDclause with INNER JOIN ?

Will there be any performance differences between the two given queries?

Answer 1

There is no difference at all between the two queries. As a matter of convention, conditions between the two queries are often put in the on clause:

select C.*
from customer C inner join
     salesman S 
     on C.salesman_id = S.salesman_id and S.city <> C.city
where S.commission > 0.12;

Functionally, though, additional conditions can go in either the on clause or the where clause -- the results and performance should be the same. Note: This is not true of an outer join. In that case, conditions should often go in the on clause.