StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTABLE1 T1, TABLE2 T2 WHERE T1.Blah = T2.Blah - VS - INNER JOIN
text
Body
copied!<p>Provided that the tables could essentially be inner joined, since the where clause excludes all records that don't match, just exactly how bad is it to use the first of the following 2 query statement syntax styles:</p> <pre><code>SELECT {COLUMN LIST} FROM TABLE1 t1, TABLE2 t2, TABLE3 t3, TABLE4 t4 (etc) WHERE t1.uid = t2.foreignid AND t2.uid = t3.foreignid AND t3.uid = t4.foreignid etc </code></pre> <p>instead of </p> <pre><code>SELECT {COLUMN LIST} FROM TABLE1 t1 INNER JOIN TABLE2 t2 ON t1.uid = t2.foreignid INNER JOIN TABLE3 t3 ON t2.uid = t3.foreignid INNER JOIN TABLE4 t4 ON t3.uid = t4.foreignid </code></pre> <p>I'm not sure if this is limited to microsoft SQL, or even a particular version, but my understanding is that the first scenario does a <em>full outer join</em> to make all possible correlations accessible.</p> <p>I've used the first approach in the past to optimise queries that access two significantly large stores of data that each have peripheral table joined to them, with the product of those joins coming together late in the query. By allowing each of the "larger" table to join to their respective lookup tables, and only combining a specific subset of each of the larger tables, I found that there were notable speed improvements over introducing the large tables to each other prior to specific filtering.</p> <p><strong>Under normal (simple joins) circumstance, would it not be far better to use the second scenario</strong>? I find it to be more easily readable and it seems like it'll be much faster.</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