Why the left joins in wp-include/query.php?

[Denis-de-Bernardy]

While looking into #9635, I noticed a whole bunch of left joins in the main queries, where a straightforward join would usually seem perfectly valid.

Are there any reasons for them to be used? Asking, because imporperly used left joins can have devastating effects on a query's performance by forcing the join order.

[Denis-de-Bernardy]

Example:

SELECT foo.*
FROM foo
JOIN bar USING ( id )
WHERE bar.col = 'val'

-> uses the index on bar(col) // fast

SELECT foo.*
FROM foo
LEFT JOIN bar USING ( id )
WHERE bar.col = 'val'

-> depending on how the query rewriter optimizes this (it could be rewritten as above), either the above plan or:
hash join on shared key, filter on bar.col = 'val' // amounts to a seq scan in our case, so very slow

[Denis-de-Bernardy]

refreshed against 11255

[ryan]

(In [11259]) JOIN instead of LEFT JOIN. Props Denis-de-Bernardy. fixes #9720

[ryan]

(In [11452]) Restore LEFT JOIN for post_status_join. fixes #9851 see #9720