The two queries you gave are not equivalent. In the second,
user_role.user_id is explicitly linked to
user.id, whereas in the first, there's no link between the
user table and the
user_role table. (What database are you using for that, BTW? An inner join with no linkage to the existing query contents doesn't look like valid SQL to me.)
Your friend is right, BTW.
JOIN criteria are supposed to be for specifying the linkage between the tables. It affects the way one entire table relates to another entire table. Filtering (selecting which rows within a table are valid) should go in the
EDIT: In response to the comment:
I understand what you say, but I still think that the first strategy
is more efficient because all the lines are not loaded. Probably
you're right, but I would understand why.
First, if by "lines" you mean "rows" or "records in the table," it's highly unlikely that all the lines will be loaded as part of the
JOIN anyway. That would be highly inefficient, but only a really horrible database engine would do that.
The SQL engine uses a process known as relational algebra to transform the query you write into the optimum method for retrieving the data you're querying for. Just like the algebra you learned in school, it can involve moving terms around from one place to another under specific rules that ensure that everything stays equivalent in the final result.
As DFord mentioned in his comment, the DBMS will probably end up producing the same query plan from both styles. If you want to test it, try running the query plans of both queries (the mechanism to do this varies from one DBMS to another; you'll have to look up how it's done on your system) and comparing them.
That being true, the real reason to write your joins in the second style is not efficiency of execution, but efficiency of maintenance. If someone needs to read your query at some later point, and they're familiar with standard SQL style, it will be a lot easier for them to understand if your query is written in standard SQL style too, which means using the
JOIN criteria to specify linkage and the
WHERE clause to specify filtering.