|
|
Posted by Erland Sommarskog on 06/03/05 00:10
DA Morgan (damorgan@psoug.org) writes:
> The other place where I take issue with you is what I read as an
> implicit assumption that a SQL statement is a SQL statement is a SQL
> statement: Which is clearly not true. Look at it from the standpoint of
> someone whose background is VB or C#. Which of the following SQL
> statement is the one to use? And yes they are all syntactically correct
> and all produce the exact same result set (in Oracle).
>
> 1.
> SELECT srvr_id
> FROM servers
> INTERSECT
> SELECT srvr_id
> FROM serv_inst;
>
> 2.
> SELECT srvr_id
> FROM servers
> WHERE srvr_id IN (
> SELECT srvr_id
> FROM serv_inst);
>
> 3.
> SELECT srvr_id
> FROM servers
> WHERE srvr_id IN (
> SELECT i.srvr_id
> FROM serv_inst i, servers s
> WHERE i.srvr_id = s.srvr_id);
>
> 4.
> SELECT DISTINCT s.srvr_id
> FROM servers s, serv_inst i
> WHERE s.srvr_id = i.srvr_id;
>
> 5.
> SELECT DISTINCT srvr_id
> FROM servers
> WHERE srvr_id NOT IN (
> SELECT srvr_id
> FROM servers
> MINUS
> SELECT srvr_id
> FROM serv_inst);
>
> 6.
> SELECT srvr_id
> FROM servers s
> WHERE EXISTS (
> SELECT srvr_id
> FROM serv_inst i
> WHERE s.srvr_id = i.srvr_id);
>
> My bet is you went straight for #4. And it is not the best
> by a very substantial margin.
My experience from reading posts on the SQL Server newsgroups, is
that most inexperienced users go for #2. And in the days of 6.5
it was a good idea to rewrite that this into #6 for improved
performance. And while I may still give people this advice, it may
only be for esthetic reasons only, because I believe that the
optimizer now is smart enough to essentially rewrite #2 into #6
internally.
Which goes to show that what is the best is not always obvious. Of
course, #4 is likely to perform less effecient because of the distinct.
#3 and #5 are too convluted to make sense (and #5 won't rnn on
SQL Server.) #1 is potentially bad, because it could return a different
result. (You didn't include any DDL, so I don't know how the tables
are related.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|