Dvir Naim Dvir Naim - 1 month ago 8
MySQL Question

SQL - The fastest query for multiple INNERJOIN

Which query is faster - (or it doesn't matter)?

SELECT *
FROM students as s
INNER JOIN hallprefs as hp
ON s.studentid = hp.studentid
INNER JOIN halls as h
ON hp.hallid = h.hallid


or

SELECT *
FROM students as s
INNER JOIN hallprefs as hp
INNER JOIN halls as h
ON hp.hallid = h.hallid
AND s.studentid = hp.studentid


Of course the original question is with way more tables.

Answer

The comments have all alluded to the same points, that it shouldn't matter in terms of performance, and that the second query is not ANSI compliant. The reason MySQL allows it is because

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

extracted from online documentation

So the ANSI equivalent of the second query is:

SELECT *
FROM students as s
CROSS JOIN hallprefs as hp
INNER JOIN halls as h
    ON hp.hallid = h.hallid
    AND s.studentid = hp.studentid;

Again, this rewrite should have no impact on performance, SQL is a declarative language, so you tell the engine what you want it to do, not how you want it to do it, so since the intention of the two queries is exactly the same, one would hope the optmiser arrives at the same plan for both. This is of course not always the case, although I am pretty certain it will be for all DBMS for simple cases like this.

When it comes to SQL, the answer to which is fastest/more efficient etc is almost always, it depends. It will depend on your schema, indexes, data types, data distribution, database vendor/version. So while general guidelines can be give, the real answer is to test.

As to which is better practice, I think it really depends on your intentions, The problem with the former is that you might decide you only want to left join on halls, so adapt your queries:

SELECT *
FROM students as s
CROSS JOIN hallprefs as hp
LEFT JOIN halls as h
    ON hp.hallid = h.hallid
    AND s.studentid = hp.studentid;

You introduce a Cartesian product, whereas the same change with the first query doesn't do this.

SELECT *
FROM students as s
INNER JOIN hallprefs as hp
    ON s.studentid = hp.studentid
LEFT JOIN halls as h
    ON hp.hallid = h.hallid;

Now, the intention could have been to have the Cartesian product, in which case the cross join solution is better for this situation. Once again, it depends, and your mileage may vary.

Comments