1

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?

3
  • 1
    I don't believe the order should matter but I don't have a MySQL database handy to prove that. What I think you could do, however, is run EXPLAIN SELECT for both options and compare the resulting query plans. If the plans end up being completely identical, the answer should be obvious. Feel free to post your own answer if you manage to reach a conclusion.
    – Andriy M
    Feb 19 '20 at 15:14
  • 1
    Does it matter No. Only the result makes sense.
    – Akina
    Feb 19 '20 at 15:36
  • When writing a JOIN, always qualify the column names. In this example, if unionDues is in unionMembers, then the IN (SELECT ...) approach won't work. If it is in employees, then why have the LEFT JOIN at all??
    – Rick James
    Feb 25 '20 at 5:47
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.