Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

In what order are MySQL JOINs evaluated?

Tags:

sql

join

mysql

I have the following query:

SELECT c.* FROM companies AS c JOIN users AS u USING(companyid) JOIN jobs AS j USING(userid) JOIN useraccounts AS us USING(userid) WHERE j.jobid = 123; 

I have the following questions:

  1. Is the USING syntax synonymous with ON syntax?
  2. Are these joins evaluated left to right? In other words, does this query say: x = companies JOIN users; y = x JOIN jobs; z = y JOIN useraccounts;
  3. If the answer to question 2 is yes, is it safe to assume that the companies table has companyid, userid and jobid columns?
  4. I don't understand how the WHERE clause can be used to pick rows on the companies table when it is referring to the alias "j"

Any help would be appreciated!

like image 462
Kyle Noland Avatar asked Oct 23 '08 03:10

Kyle Noland


People also ask

Does the order of joins matter mysql?

For INNER joins, no, the order doesn't matter. The queries will return same results, as long as you change your selects from SELECT * to SELECT a.

Does the order of joins matter for performance?

JOIN order doesn't matter, the query engine will reorganize their order based on statistics for indexes and other stuff.


1 Answers

  1. USING (fieldname) is a shorthand way of saying ON table1.fieldname = table2.fieldname.

  2. SQL doesn't define the 'order' in which JOINS are done because it is not the nature of the language. Obviously an order has to be specified in the statement, but an INNER JOIN can be considered commutative: you can list them in any order and you will get the same results.

    That said, when constructing a SELECT ... JOIN, particularly one that includes LEFT JOINs, I've found it makes sense to regard the third JOIN as joining the new table to the results of the first JOIN, the fourth JOIN as joining the results of the second JOIN, and so on.

    More rarely, the specified order can influence the behaviour of the query optimizer, due to the way it influences the heuristics.

  3. No. The way the query is assembled, it requires that companies and users both have a companyid, jobs has a userid and a jobid and useraccounts has a userid. However, only one of companies or user needs a userid for the JOIN to work.

  4. The WHERE clause is filtering the whole result -- i.e. all JOINed columns -- using a column provided by the jobs table.

like image 171
staticsan Avatar answered Oct 09 '22 11:10

staticsan