Is there any difference with JOIN?

Question

Print the first names, surnames and salaries of employees who receive less than the minimum salary in their specialty + 1000.

Between

SELECT first_name, last_name, salary
FROM employees e
JOIN jobs j ON (e.job_id = j.job_id)
WHERE salary < min_salary + 1000;

And

SELECT first_name, last_name, salary
FROM employees e
JOIN jobs j ON (e.job_id = j.job_id AND salary < min_salary + 1000);

I get the same output, but is there any difference in logic other than syntax?

This is a faq. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Feb 29 '20 at 16:21
Googling 'sql inner join where vs on site:stackoverflow.com before:2011' ... https://stackoverflow.com/q/121631/3404097 https://stackoverflow.com/q/1018822/3404097 https://stackoverflow.com/q/2722795/3404097 etc — philipxy, Feb 29 '20 at 16:44

score 0 · Answer 1 · answered Feb 29 '20 at 10:31

I don't see any difference in the JOIN from your query. One thing when you use AND this will be used for that Join only, if you join other tables in future that has an impact however WHERE is a clause which will be common for the number of Joins you do for the same query or stored procedure.

This may behave same for your work but need not be same for all the scenarios.

score 0 · Answer 2 · answered Feb 29 '20 at 10:38

There is no logical difference between the two queries you showed us, because the join is an inner join. However, if it were a left join, there would be a difference. Consider:

SELECT e.first_name, e.last_name, j.salary
FROM employees e
LEFT JOIN jobs j ON e.job_id = j.job_id
WHERE j.salary < min_salary + 1000;

This query would actually drop any employee records which don't have any jobs matching the salary requirement. However, if we were to move the logic in the WHERE clause to the ON condition, then all employees would be retained:

SELECT e.first_name, e.last_name, j.salary
FROM employees e
LEFT JOIN jobs j
    ON e.job_id = j.job_id AND
       j.salary < min_salary + 1000;

Is there any difference with JOIN?

2 Answers2