Is this correct?

Are these two statements equivalent? If not, how can I rephrase the 2nd
one?
UPDATE Contact
SET DoNotCallTypeKey = 20
WHERE EXISTS (
SELECT *
FROM SSA
INNER JOIN SSP
ON SSP.SampleSourceArchiveKey = SSA.SampleSourceArchiveKey
WHERE ssp.ContactKey = Contact.ContactKey
AND ssa.SampleSourceKey = @.sampleSourceKey
AND ssa.SurveyFlag = 'DNS'
)
vs
UPDATE Contact
SET DoNotCallTypeKey = 20
FROM Contact
INNER JOIN SSP
ON ssp.ContactKey = ssp.ContactKey
INNER JOIN SSA
ON ssa.SampleSourceArchiveKey = ssp.SampleSourceArchiveKey
WHERE ssa.SampleSourceKey = @.sampleSourceKey
AND ssa.SurveyFlag = 'DNS'
They both look like they return the same results, but when it operates on
1.5 million records, I really don't want to have to scroll trhough both
result sets to prove it. I want to rephrase it because I hate all these
dumb "where exists" things that are upside down with the criteria and joins
all stuffed into sub-selects.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneMike,
They look the same with the exception:
ON ssp.ContactKey = ssp.ContactKey
should be
ON ssp.ContactKey = Contact.ContactKey
HTH
Jerry
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:Ou%23dxAvuFHA.908@.tk2msftngp13.phx.gbl...
> Are these two statements equivalent? If not, how can I rephrase the 2nd
> one?
> UPDATE Contact
> SET DoNotCallTypeKey = 20
> WHERE EXISTS (
> SELECT *
> FROM SSA
> INNER JOIN SSP
> ON SSP.SampleSourceArchiveKey = SSA.SampleSourceArchiveKey
> WHERE ssp.ContactKey = Contact.ContactKey
> AND ssa.SampleSourceKey = @.sampleSourceKey
> AND ssa.SurveyFlag = 'DNS'
> )
> vs
> UPDATE Contact
> SET DoNotCallTypeKey = 20
> FROM Contact
> INNER JOIN SSP
> ON ssp.ContactKey = ssp.ContactKey
> INNER JOIN SSA
> ON ssa.SampleSourceArchiveKey = ssp.SampleSourceArchiveKey
> WHERE ssa.SampleSourceKey = @.sampleSourceKey
> AND ssa.SurveyFlag = 'DNS'
>
> They both look like they return the same results, but when it operates on
> 1.5 million records, I really don't want to have to scroll trhough both
> result sets to prove it. I want to rephrase it because I hate all these
> dumb "where exists" things that are upside down with the criteria and
> joins all stuffed into sub-selects.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> They look the same with the exception:
> ON ssp.ContactKey = ssp.ContactKey
> should be
> ON ssp.ContactKey = Contact.ContactKey
Sorry, that was just a typo.
I'm just looking for confirmation that they're the same. Thanks.
These people around here come from a MS Access background and they don't
know how to use joins right. So they just write all these upside down
things with WHERE [NOT] EXISTS and a long list of sub-selects, and it's
almost indecipherable sometimes.
Thank You!
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OcA08JvuFHA.3864@.TK2MSFTNGP12.phx.gbl...
> Mike,
>
> HTH
> Jerry
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:Ou%23dxAvuFHA.908@.tk2msftngp13.phx.gbl...
>|||The first is correct SQL, while the seconnd is proprietary and
problematic.
It makes no sense in terms of the SQL language model. A FROM clause is
always suppose effectively materialize a working table that disappeare
at the end of the statement. Likewise, an alias is supposed to act as
it materializes a new working table with the data from the original
table expression in it. To be consistent, this syntax says that you
have done nothing to the base table.
Sybase and some other vendors had the same syntax but with different
semantics. Worst of both worlds!
And on top of that, it is unpredictable. This is a simple example from
Adam Machanic
CREATE TABLE Foo
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Foo VALUES ('A', 0);
INSERT INTO Foo VALUES ('B', 0);
INSERT INTO Foo VALUES ('C', 0);
CREATE TABLE Bar
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Bar VALUES ('A', 1);
INSERT INTO Bar VALUES ('A', 2);
INSERT INTO Bar VALUES ('B', 1);
INSERT INTO Bar VALUES ('C', 1);
You run this proprietary UPDATE with a FROM clause:
UPDATE Foo
SET Foo.col_b = Bar.col_b
FROM Foo INNER JOIN Bar
ON Foo.col_a = Bar.col_a;
The result of the update cannot be determined. The value of the column
will depend upon either order of insertion, (if there are no clustered
indexes present), or on order of clustering (but only if the cluster
isn't fragmented).
The join mechanism hides cardinality violations, turns your data into
garbage.|||On Fri, 16 Sep 2005 14:55:46 -0400, Mike Labosh wrote:

>Are these two statements equivalent? If not, how can I rephrase the 2nd
>one?
Hi Mike,
Apart from the typo, they might be. But it's also possible that the
second version sets the DoNotCallTypeKey for a row to 20 hundreds of
times during the execution. I'd have to know the table structure to be
sure.
But there are some important other issues that you should think about.
First: The first statement is ANSI standard SQL, that will eaasily port
to other database platforms. The second is proprietary syntax that runs
fine on SQL Server, but can't be ported to other databases. Even MS' own
"other" database product (Access) won't run this code - it has a similar
non-ANSI syntax for UPDATE and DELETE, but it's not the same as in SQL
Server!
Second: There are definitely situations where I would choose to use the
non-standard UPDATE ... FROM syntax. But this is not one of them. I
would consider using the proprietary syntax if the new value for the
DoNotCallTypeKey had to be taken from one of the tables in the subquery
(as SQL Server isn't very clever about optimizing statements that have
the same subquery twice).
Third: Even on SQL Server, the second syntax will sometimes fail. If
Contact is not a base table, but a view, AND you have an INSTEAD OF
UPDATE trigger defined on Contact, you'll get an error if you try the
second syntax. SQL Server somehow doesn't know how to handle this
situation.
Fourth:
> I want to rephrase it because I hate all these
>dumb "where exists" things that are upside down with the criteria and joins
>all stuffed into sub-selects.
I consider this to be a very bad reason. Personal bias should always
come second to professional impartiality.
Instead of trying to get your Access developers to learn a syntax that
won't work on Acces, you'd be better off getting yourself acquainted
with the syntax that will work on all SQL-92 compliant databases. You'll
also find that this syntax grows on you when you use it more often (as I
found out when I had to rewrite dozens of UPDATE ... FROM statements
because the view they updated had to be equipped with an ISNTEAD OF
trigger).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Is there any difference...

...in speed between
if exists(select top 1 id from table)
or
if exists(select id from table)
Thanks.
No.
But just in case you want proof, then check the query plan. If they are
the same, then you are sure their performance is the same.
Gert-Jan
Frank Rizzo wrote:
> ...in speed between
> if exists(select top 1 id from table)
> or
> if exists(select id from table)
> Thanks.
|||Shouldn't there be a WHERE clause on that SELECT?
Since the EXISTS stops processing once it finds a single record that
matches, I would think adding TOP would just make it do more work.
I've read that you should always use *, as in IF EXISTS (SELECT * FROM TABLE
WHERE ...) because that allows the query optimizer to choose whichever field
it thinks is best.
Greg
"Frank Rizzo" <none@.none.com> wrote in message
news:%23sbGSHl4FHA.1184@.TK2MSFTNGP12.phx.gbl...
> ...in speed between
> if exists(select top 1 id from table)
> or
> if exists(select id from table)
>
> Thanks.
|||Gert-Jan Strik wrote:
> No.
> But just in case you want proof, then check the query plan. If they are
> the same, then you are sure their performance is the same.
Thanks for the tip. The query plan is absolutely identical.
[vbcol=seagreen]
> Gert-Jan
>
> Frank Rizzo wrote:
|||These are all equivalent.
if exists (select * from pubs..authors) print 1
if exists (select top 1 * from pubs..authors) print 1
if exists (select au_id from pubs..authors) print 1
if exists (select 1 from pubs..authors) print 1
The presence of a TOP 1 does not affect the result, because what matters is
whether a row is returned or not by the subquery. For the same reason, the
content of the select list (* vs. 1 vs. one or more columns) does not matter
either. This is still the case when the subquery contains a WHERE clause. I
second Gert-Jan's suggestion that this is easily provable by comparing the
query plans.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg Burns" <bluebunny@.newsgroups.nospam> wrote in message
news:%23jKl2Am4FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Shouldn't there be a WHERE clause on that SELECT?
> Since the EXISTS stops processing once it finds a single record that
> matches, I would think adding TOP would just make it do more work.
> I've read that you should always use *, as in IF EXISTS (SELECT * FROM
> TABLE WHERE ...) because that allows the query optimizer to choose
> whichever field it thinks is best.
> Greg
> "Frank Rizzo" <none@.none.com> wrote in message
> news:%23sbGSHl4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>

Is there any difference...

...in speed between
if exists(select top 1 id from table)
or
if exists(select id from table)
Thanks.No.
But just in case you want proof, then check the query plan. If they are
the same, then you are sure their performance is the same.
Gert-Jan
Frank Rizzo wrote:
> ...in speed between
> if exists(select top 1 id from table)
> or
> if exists(select id from table)
> Thanks.|||Shouldn't there be a WHERE clause on that SELECT?
Since the EXISTS stops processing once it finds a single record that
matches, I would think adding TOP would just make it do more work.
I've read that you should always use *, as in IF EXISTS (SELECT * FROM TABLE
WHERE ...) because that allows the query optimizer to choose whichever field
it thinks is best.
Greg
"Frank Rizzo" <none@.none.com> wrote in message
news:%23sbGSHl4FHA.1184@.TK2MSFTNGP12.phx.gbl...
> ...in speed between
> if exists(select top 1 id from table)
> or
> if exists(select id from table)
>
> Thanks.|||Gert-Jan Strik wrote:
> No.
> But just in case you want proof, then check the query plan. If they are
> the same, then you are sure their performance is the same.
Thanks for the tip. The query plan is absolutely identical.
[vbcol=seagreen]
> Gert-Jan
>
> Frank Rizzo wrote:
>|||These are all equivalent.
if exists (select * from pubs..authors) print 1
if exists (select top 1 * from pubs..authors) print 1
if exists (select au_id from pubs..authors) print 1
if exists (select 1 from pubs..authors) print 1
The presence of a TOP 1 does not affect the result, because what matters is
whether a row is returned or not by the subquery. For the same reason, the
content of the select list (* vs. 1 vs. one or more columns) does not matter
either. This is still the case when the subquery contains a WHERE clause. I
second Gert-Jan's suggestion that this is easily provable by comparing the
query plans.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg Burns" <bluebunny@.newsgroups.nospam> wrote in message
news:%23jKl2Am4FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Shouldn't there be a WHERE clause on that SELECT?
> Since the EXISTS stops processing once it finds a single record that
> matches, I would think adding TOP would just make it do more work.
> I've read that you should always use *, as in IF EXISTS (SELECT * FROM
> TABLE WHERE ...) because that allows the query optimizer to choose
> whichever field it thinks is best.
> Greg
> "Frank Rizzo" <none@.none.com> wrote in message
> news:%23sbGSHl4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>

Is there any difference...

...in speed between
if exists(select top 1 id from table)
or
if exists(select id from table)
Thanks.No.
But just in case you want proof, then check the query plan. If they are
the same, then you are sure their performance is the same.
Gert-Jan
Frank Rizzo wrote:
> ...in speed between
> if exists(select top 1 id from table)
> or
> if exists(select id from table)
> Thanks.|||Shouldn't there be a WHERE clause on that SELECT?
Since the EXISTS stops processing once it finds a single record that
matches, I would think adding TOP would just make it do more work.
I've read that you should always use *, as in IF EXISTS (SELECT * FROM TABLE
WHERE ...) because that allows the query optimizer to choose whichever field
it thinks is best.
Greg
"Frank Rizzo" <none@.none.com> wrote in message
news:%23sbGSHl4FHA.1184@.TK2MSFTNGP12.phx.gbl...
> ...in speed between
> if exists(select top 1 id from table)
> or
> if exists(select id from table)
>
> Thanks.|||Gert-Jan Strik wrote:
> No.
> But just in case you want proof, then check the query plan. If they are
> the same, then you are sure their performance is the same.
Thanks for the tip. The query plan is absolutely identical.
> Gert-Jan
>
> Frank Rizzo wrote:
>>...in speed between
>>if exists(select top 1 id from table)
>>or
>>if exists(select id from table)
>>Thanks.|||These are all equivalent.
if exists (select * from pubs..authors) print 1
if exists (select top 1 * from pubs..authors) print 1
if exists (select au_id from pubs..authors) print 1
if exists (select 1 from pubs..authors) print 1
The presence of a TOP 1 does not affect the result, because what matters is
whether a row is returned or not by the subquery. For the same reason, the
content of the select list (* vs. 1 vs. one or more columns) does not matter
either. This is still the case when the subquery contains a WHERE clause. I
second Gert-Jan's suggestion that this is easily provable by comparing the
query plans.
--
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg Burns" <bluebunny@.newsgroups.nospam> wrote in message
news:%23jKl2Am4FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Shouldn't there be a WHERE clause on that SELECT?
> Since the EXISTS stops processing once it finds a single record that
> matches, I would think adding TOP would just make it do more work.
> I've read that you should always use *, as in IF EXISTS (SELECT * FROM
> TABLE WHERE ...) because that allows the query optimizer to choose
> whichever field it thinks is best.
> Greg
> "Frank Rizzo" <none@.none.com> wrote in message
> news:%23sbGSHl4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>> ...in speed between
>> if exists(select top 1 id from table)
>> or
>> if exists(select id from table)
>>
>> Thanks.
>