[thelist] ASP, SQL, Recordsets, Best Practices
rudy
r937 at interlog.com
Thu Mar 1 17:18:49 CST 2001
>Wouldn't this be quicker:
>
>select 1 from [table] where [uniqueness]
>
>No functions... just a literal.
>
>If I recall, I learned this from you...
hi matt
you honour me and it is appreciated
there is a subtle difference in the two queries
select count(*) will always return exactly one row with one value, zero or
positive
select 1 will only return a 1 if a row exists, otherwise it will return a
null, and let's just not go there, okay?
and don't forget that without the uniqueness test, select 1 will return as
many 1s as rows ;o)
the situation that select 1 usually comes up in, therefore, is in a
coordinated subselect where you want to know existence only
for example
select username, userdata
from usertable
where not exists
(select 1
from nosoupforyou
where userid = usertable.userid)
this query gets all the users that don't have an entry in the nosoupforyou
table
in the case of the subquery, you don't really need to return anything,
because it's a true/false condition
even if the subquery potentially might return more than one row (e.g. you
designed the nosoupforyou table to record multiple transgressions by the
same individual), the database would still probably resolve the subquery
after finding the first row and not bother getting them all
you could also do the subselect with count(*) --
select username, userdata
from usertable
where 0 =
(select count(*)
from nosoupforyou
where userid = usertable.userid)
in this case the database might -- you never know how clever the path
optimizers are these days -- actually go out and count them all before
evaluating the 0= test
see the difference?
since michele wants also to use the single record's data fields, she won't
be using a subselect, rather, she wants the data fields returned in the
select
she can therefore either test for a null in the primary key of the returned
record, or else go through all that programming rigamarole that asp
developers are so fond of... <dons flame-retardant underwear again />
>if objRS.eof and objRS.bof then
> bSuccess = Dexter.stopUsingFrontPage()
>else
> bSuccess = Rudeman.learnRealSQL()
>end if
>
>And, of course, bSuccess will always be false in both cases...
good one ;o)
unfortunately "real" sql is a standard (sql-99, i think) with which i am
not terribly familiar
standard sql suffers from even more bastardizations by proprietary vendor
extensions than html
i only know specific manifestations of sql, as delivered unto us by the
database vendors in their endless benevolence...
rudy
p.s. i know this wasn't asked, but one final comment on the count(*)
transgressions query
notice that this is obviously the one you would need if it mattered how
many transgressions... for example, to get all users that have fewer than
three strikes against them --
where 3 >
(select count(*)
from nosoupforyou
where userid = usertable.userid)
More information about the thelist
mailing list