In what order are MySQL JOINs evaluated?

Question

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!

Answer 1

Here is a more detailed answer on JOIN precedence. In your case, the JOINs are all commutative. Let's try one where they aren't.

Build schema:

CREATE TABLE users (
  name text
);

CREATE TABLE orders (
  order_id text,
  user_name text
);

CREATE TABLE shipments (
  order_id text,
  fulfiller text
);

Add data:

INSERT INTO users VALUES ('Bob'), ('Mary');

INSERT INTO orders VALUES ('order1', 'Bob');

INSERT INTO shipments VALUES ('order1', 'Fulfilling Mary');

Run query:

SELECT *
  FROM users
       LEFT OUTER JOIN orders
       ON orders.user_name = users.name
       JOIN shipments
       ON shipments.order_id = orders.order_id

Result:

Only the Bob row is returned

Analysis:

In this query the LEFT OUTER JOIN was evaluated first and the JOIN was evaluated on the composite result of the LEFT OUTER JOIN.

Second query:

SELECT *
  FROM users
       LEFT OUTER JOIN (
         orders
         JOIN shipments
         ON shipments.order_id = orders.order_id)
         ON orders.user_name = users.name

Result:

One row for Bob (with the fulfillment data) and one row for Mary with NULLs for fulfillment data.

Analysis:

The parenthesis changed the evaluation order.

---

Further MySQL documentation is at https://dev.mysql.com/doc/refman/5.5/en/nested-join-optimization.html