Is the table order in the join used when LEFT (external) joins are used?

Question

I would like to confirm that the SQL query

SELECT .... FROM apples, oranges LEFT JOIN kiwis ON kiwis.orange_id = oranges.id, bananas WHERE .... 

exactly equivalent to other permutations in a subclass of FROM, for example

 SELECT .... FROM oranges LEFT JOIN kiwis ON kiwis.orange_id = oranges.id, bananas, apples WHERE .... 

or

 SELECT .... FROM bananas, apples, oranges LEFT JOIN kiwis ON kiwis.orange_id = oranges.id WHERE .... 

while the apparent LEFT MEETING between the oranges and kiwi remains intact. From what I read in different documents, the returned set should be exactly the same.

I'm really only interested in the results of the query, not its performance in a real database. (I use PostgreSQL 8.3, which AFAIK does not support optimizer hints about the connection order and will try to automatically create the optimal query plan).

Answer 1

It's the same thing, but it is ambiguous, like the hell with implicit CROSS JOINs. Use explicit JOINS.

If you join the WHERE clause, the results may be different because the connections and filters are mixed up.

 SELECT .... FROM apples a JOIN bananas b 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 doesn't usually matter.
• INTERCONNECTED COMPOUNDS no that you have identified
• SQL is declarative: you tell the optimizer what you want, not how to do it. This eliminates JOIN order considerations (given the previous two elements).

Answer 2

The situation is summarized in Scheduler Management using explicit JOIN sections . External connections are not reordered, internal can be. And you can forcefully create a specific optimization order by dropping * join_collapse_limit * before running the query and simply placing things in the order you want them. Here's how you “hint” at a database in this area.

In general, you want to use EXPLAIN to confirm which order you receive, and which can sometimes be used to visually confirm that two requests receive the same plan.

Answer 3

I have been doing SQL for donkeys for years, and in my experience, the order of the tables doesn't matter. The database will consider the query as a whole and create an optimal query plan. This is why database companies use many people with PhDs to optimize their query plan.

The database provider will commit commercial suicide if it is optimized in the order in which you personally specified SQL in your query.