12
edits
Places/Places SQL queries best practices: Difference between revisions
Places/Places SQL queries best practices (view source)
Revision as of 21:10, 20 July 2022
, 20 July 2022→Querying multiple tables: Copy edited (e.g. ref. <https://en.wiktionary.org/wiki/Cartesian_product#Noun>). Expanded.
(→Table aliases: Copy edited (e.g. ref. <https://en.wiktionary.org/wiki/complex#Adjective> and <https://en.wiktionary.org/wiki/conflict#Noun>). Added the missing words, e.g., subjects. Expanded.) |
(→Querying multiple tables: Copy edited (e.g. ref. <https://en.wiktionary.org/wiki/Cartesian_product#Noun>). Expanded.) |
||
| Line 134: | Line 134: | ||
== Querying multiple tables == | == Querying multiple tables == | ||
When querying multiple tables involving foreign keys, please always avoid | When querying multiple tables involving foreign keys, please always avoid Cartesian products like: | ||
<pre> | <pre> | ||
SELECT b.id, h.url | SELECT b.id, h.url | ||
| Line 146: | Line 146: | ||
JOIN moz_bookmarks b ON h.id = b.fk | JOIN moz_bookmarks b ON h.id = b.fk | ||
</pre> | </pre> | ||
That ensures the query is easier to expand, is less error-prone for future developers and more | That ensures the query is easier to expand, is less error-prone for future developers and more performance friendly. | ||
We actually use | We actually use two types of <i>JOIN</i>s: | ||
* <i>LEFT JOIN</i> is used when the right table values could not exist, but we still want a record containing the left table values. | * <i>LEFT JOIN</i> is used when the right table values could not exist, but we still want a record containing the left table values. | ||
* <i>JOIN</i> is used when the right table must obey the <i>JOIN</i> condition, so records without a match in the right table won't be returned. | * <i>JOIN</i> is used when the right table must obey the <i>JOIN</i> condition, so records without a match in the right table won't be returned. | ||