StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Union will be faster, as it simply passes the first SELECT statement, and then parses the second SELECT statement and adds the results to the end of the output table.</p> <p>The Join will go through each row of both tables, finding matches in the other table therefore needing a lot more processing due to searching for matching rows for each and every row.</p> <h3>EDIT</h3> <p>By Union, I mean Union All as it seemed adequate for what you were trying to achieve. Although a normal Union is generally faster then Join.</p> <h3>EDIT 2 (Reply to @seebiscuit 's comment)</h3> <p>I don't agree with him. Technically speaking no matter how good your join is, a "JOIN" is still more expensive than a pure concatenation. I made a blog post to prove it at my blog <a href="http://www.codeperf.net/sql-union-vs-join-performance/" rel="nofollow noreferrer">codePERF[dot]net</a>. Practically speaking they serve 2 completely different purposes and it is more important to ensure your indexing is right and using the right tool for the job.</p> <p>Technically, I think it can be summed using the following 2 execution plans taken from my blog post:</p> <h1><code>UNION ALL</code> Execution Plan</h1> <p><a href="https://i.stack.imgur.com/JV0P9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JV0P9.png" alt="UNION ALL Execution Plan"></a></p> <h1><code>JOIN</code> Execution Plan</h1> <p><a href="https://i.stack.imgur.com/TzShk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TzShk.png" alt="JOIN Execution Plan"></a></p> <h1>Practical Results</h1> <p>Practically speaking the difference on a clustered index lookup is negligible:</p> <p><a href="https://i.stack.imgur.com/Lj6Dq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Lj6Dq.png" alt="Benchmark Results"></a></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