There is another advantage to the use of joins. By using joins instead of multiple queries, you maximize the placement of the calculation burden on the database. This means you can make better use of the database's abilities to search through, filter, sort, etc.
Here is an example: Let us say you wish to perform a search for data that is stored in several different tables. You can do this with one query using table joins, or perhaps in several queries, and then package the data for display afterwards with something like PHP. But let us say you wish to "paginate". That is, let us say you wish to not extract all the possible rows that match your select criteria, but only perhaps 25 rows at a time. And let us say you wish to extract the "third page" of this selection, in other words, rows 51 to 75. If you set this up to use only one query using table joins, you can easilly do this select using the limit clause "limit 51,25". This way you extract only the 25 rows that you want, no more.
If instead, you did this with multiple queries, you would have to in effect extract all the matching rows for all the queries, do whatever manipulations are needed in PHP arrays afterwards, and then throw everything away except for the 25 rows you wish to show. You cannot just use the "limit 51,25" clause on the individual queries, because the row locations of theindividual query results will in general not be the same.
In general, it is far best to put as much of the selection related computations to the database as possbile. However, should the resulting query become overly complicated resulting in excessive database computational effort (and adding a few indices will not help) then it is better to break it down and do the task in multiple queries.
296
