StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POInstead of independently joining multiple tables, use separate queries?
text
Body
copied!<p>I often find myself performing a couple of <em>independent</em> joins off a table. For instance, say we have the table <code>collections</code>, which has independent one-to-N relationships with both <code>photos</code> and <code>songs</code>, where N is from zero to many.</p> <p>Now, say we want to get a collection, and <em>both</em> its (independently) associated photos and songs.</p> <p>I would typically use something like this:</p> <pre><code>SELECT collections.collectionid as collectionid, photos.name as photo_name, songs.name as song_name FROM collections LEFT JOIN photos ON collections.collectionid = photos.collectionid LEFT JOIN songs ON collections.collectionid = songs.collectionid WHERE collections.collectionid = 14 </code></pre> <p>Of course, left-joining one table to two other tables, if the first join results in <code>M</code> rows and the second in <code>N</code> rows, gives <code>M * N</code> rows. This would seem suboptimal in terms of database-traffic and performance.</p> <pre><code>+--------------+------------+-----------+ | collectionid | photo_name | song_name | +--------------+------------+-----------+ | 14 | 'x' | 'a' | \ | 14 | 'x' | 'b' | - Each photo is returned 3 times, | 14 | 'x' | 'c' | / because 3 songs are returned. | 14 | 'y' | 'a' | \ | 14 | 'y' | 'b' | | 14 | 'y' | 'c' | / +--------------+------------+-----------+ </code></pre> <p>Alternatively, you can perform two selects: two separate queries, each joining <code>collections</code> to a different table, giving <code>M + N</code> rows:</p> <pre><code>SELECT collections.collectionid as collectionid song.name as song_name FROM collections LEFT JOIN songs on collections.collectionid = songs.collectionid WHERE collections.collectionid = 14 </code></pre> <p>and:</p> <pre><code>SELECT collections.collectionid as collectionid photos.name as photo_name FROM collections LEFT JOIN photos on collections.collectionid = photos.collectionid WHERE collections.collectionid = 14 </code></pre> <p>giving:</p> <pre><code>+--------------+------------+ +--------------+------------+ | collectionid | song_name | | collectionid | photo_name | +--------------+------------+ +--------------+------------+ | 14 | 'a' | | 14 | 'x' | | 14 | 'b' | | 14 | 'y' | | 14 | 'c' | +--------------+------------+ +--------------+------------+ </code></pre> <p>My question: What is the best way to handle this?</p> <p>Neither of the above seems optimal. So, is there another way that results in <code>M + N</code> rows, yet can be done in a single query?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload