Does the order of tables in a join matter, when LEFT (outer) joins are used?

It is the same but it is ambiguous as hell with the implicit CROSS JOINs. Use explicit JOINS If you are joining in the WHERE clause then the results may differ because joins and filters are mixed up SELECT .... FROM apples a JOIN bananas be ON ... JOIN oranges o ON ... LEFT JOIN kiwis k ON k. Orange_id = o.Id WHERE (filters only) Notes: INNER JOINS and CROSS JOINS are commutative and associative: order does not matter usually OUTER JOINS are not, which you identified SQL is declarative: you tell the optimiser what you want, not how to do it.

This removes JOIN order considerations (subject to the previous 2 items).

It is the same but it is ambiguous as hell with the implicit CROSS JOINs. Use explicit JOINS. If you are joining in the WHERE clause then the results may differ because joins and filters are mixed up.

SELECT .... FROM apples a JOIN bananas be ON ... JOIN oranges o ON ... LEFT JOIN kiwis k ON k. Orange_id = o. Id WHERE (filters only) Notes: INNER JOINS and CROSS JOINS are commutative and associative: order does not matter usually.

OUTER JOINS are not, which you identified SQL is declarative: you tell the optimiser what you want, not how to do it. This removes JOIN order considerations (subject to the previous 2 items).

A very good and thorough answer, thank you. For the record, yes, the join conditions are in the WHERE clause. This is a legacy application, and I'm in the process of refactoring some of the queries.

– Radio Yerevan Feb 17 at 16:32.

I've done SQL for donkeys years, and in all my experience, the table order does not matter. The database will look at the query as a whole and create the optimal query plan. That is why database companies employ many people with PhD in query plan optimisations.

The database vendor would commit commercial suicide if it optimised by the order in which you personally listed the SQL in your query.

Older versions of DBMS actually did it that way (e.g. Oracle 7 & 8). This was usually called a "rule based optimizer" as it statically looked at the query and applied some rules based on the structure of the query to determine the execution plan. With a rule based optimizer the first table was usually the driving table and so the order did have a major influence on the performance – a_horse_with_no_name Feb 15 at 22:12.

The situation is summarized at Controlling the Planner with Explicit JOIN Clauses. Outer joins don't get reordered, inner ones can be. And you can force a particular optimizer order by dropping *join_collapse_limit* before running the query, and just putting things in the order you want them it.

That's how you "hint" at the database in this area. In general, you want to use EXPLAIN to confirm what order you're getting, and that can sometimes be used to visually confirm that two queries are getting the same plan.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.