StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>Update:</strong></p> <p>This article in my blog summarizes both my answer and my comments to another answers, and shows actual execution plans:</p> <ul> <li><a href="http://explainextended.com/2009/06/16/in-vs-join-vs-exists/" rel="noreferrer"><strong>IN vs. JOIN vs. EXISTS</strong></a></li> </ul> <hr> <pre><code>SELECT * FROM a WHERE a.c IN (SELECT d FROM b) SELECT a.* FROM a JOIN b ON a.c = b.d </code></pre> <p>These queries are not equivalent. They can yield different results if your table <code>b</code> is not key preserved (i. e. the values of <code>b.d</code> are not unique).</p> <p>The equivalent of the first query is the following:</p> <pre><code>SELECT a.* FROM a JOIN ( SELECT DISTINCT d FROM b ) bo ON a.c = bo.d </code></pre> <p>If <code>b.d</code> is <code>UNIQUE</code> and marked as such (with a <code>UNIQUE INDEX</code> or <code>UNIQUE CONSTRAINT</code>), then these queries are identical and most probably will use identical plans, since <code>SQL Server</code> is smart enough to take this into account.</p> <p><code>SQL Server</code> can employ one of the following methods to run this query:</p> <ul> <li><p>If there is an index on <code>a.c</code>, <code>d</code> is <code>UNIQUE</code> and <code>b</code> is relatively small compared to <code>a</code>, then the condition is propagated into the subquery and the plain <code>INNER JOIN</code> is used (with <code>b</code> leading)</p></li> <li><p>If there is an index on <code>b.d</code> and <code>d</code> is not <code>UNIQUE</code>, then the condition is also propagated and <code>LEFT SEMI JOIN</code> is used. It can also be used for the condition above.</p></li> <li><p>If there is an index on both <code>b.d</code> and <code>a.c</code> and they are large, then <code>MERGE SEMI JOIN</code> is used</p></li> <li><p>If there is no index on any table, then a hash table is built on <code>b</code> and <code>HASH SEMI JOIN</code> is used.</p></li> </ul> <p><strong>Neither</strong> of these methods reevaluates the whole subquery each time.</p> <p>See this entry in my blog for more detail on how this works:</p> <ul> <li><a href="http://explainextended.com/2009/04/20/counting-missing-rows-sql-server/" rel="noreferrer"><strong>Counting missing rows: SQL Server</strong></a></li> </ul> <p>There are links for all <code>RDBMS</code>'s of the big four.</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