MySQL: Does the order of the ON condition of a LEFT JOIN matter?

Question

I have two tables, employees and unionMembers. The people in unionMembers are a small subset of the people in employees. I want to do a SELECT query using a LEFT JOIN like so:

SELECT    wage, unionDues
FROM      employees 
LEFT JOIN unionMembers ON employees.ID = unionMembers.ID 
WHERE     employees.ID = 'ID001';

Does it matter if I do ON employees.ID = unionMembers.ID or reverse the order and do ON unionMembers.ID = employees.ID?

Let's say I have 100 employees and 5 of them are union members. When doing the LEFT JOIN, ideally it would have to do 5 comparisons instead of a 100. Would the order of the equality test make any difference?

Answer 1

SQL is a declarative language; that is, it does not specify how something should be done, only what the result should be.

The computing languages I have used all respect the properties of equality, so employees.ID = unionMembers.ID is equivalent to unionMembers.ID = employees.ID

In this case, rather than an outer join, which can generate a large data-set, you would probably want to use a sub-query or the EXISTS operator, depending on your data structure. Something like:

SELECT ID, wage, unionDues FROM employees WHERE ID in ( SELECT ID FROM unionMembers WHERE ID = 'ID001' );

There is an in-depth discussion here: https://dev.mysql.com/doc/refman/8.0/en/subquery-optimization-with-exists.html

HTH