-
☆ A M B ☆
- 24,524 Posts
I shocked myself; this code worked the first time.
SELECT name FROM modx_site_htmlsnippets ch LEFT JOIN modx_category ca ON ch.categories = ca.id WHERE ca.category = 'sideBlocks';
The question is, is this the best way to make such a query? I strongly suspect that there is a much better way to do the same thing, i.e. get the names of the chunks with a given category.
There is one little problem in this SQL statement. Instead of LEFT OUTER use INNER.
The query will only return names of snippets, that are in the category in the WHERE clause.
SELECT name
FROM modx_site_htmlsnippets ch
INNER JOIN modx_category ca
ON ch.categories = ca.id
WHERE ca.category = 'sideBlocks';
Gone away and found a better place to stay
-
☆ A M B ☆
- 24,524 Posts
then how come the LEFT JOIN does the same thing? It returns exactly the same list of chunks. Even the "explain" shows exactly the same results.
Hmm. Looks like the WHERE clause did it. I found this interesting tidbit...
In fact, in many cases it is faster and more efficient to use a LEFT JOIN that exludes nulls, rather than an INNER JOIN, due to the way certain SQL engines (notably, MySQL) optimize queries.
[ed. note: sottwell last edited this post 14 years, 6 months ago.]
The LEFT JOIN does the same thing, because you first get all records from modx_site_htmlsnippets. The in the WHERE clause you say, only that from modx_category with the condition of having category named sideBlocks.
The INNER JOIN returns the same result, because of the same conditions, but INNER JOIN is faster than LEFT OUTER JOIN.
In the end it's just less calculation for the database and a very little speed improvement.
Gone away and found a better place to stay
