[thelist] returning values you already have (was: SQL statement construction)

[rudy]

> Select 'evolt is sexy' from myTable;
> 
> That will return 'evolt is sexy' for every 
> row in the table... which isn't necessary, 
> because you already know that value, so you 
> don't need to get it from the database.

matt, that is such a cool example -- nice explanation


for those of you interested in database performance efficiency, ask 
yourself if the database actually has to access the table in order to send 
back the result set for the above query

well, yes -- unless it has an index on the table, it will use the index 
which is presumably faster to access than the actual rows

the key point is, it doesn't actually have to "get" anything from the rows

(all it really needs is the number of rows)

for another example of this, consider the NOT EXISTS subquery

you do a NOT EXISTS when you want to check to see whether there are any 
rows in some second table, as a condition on select rows in some first 
table

for example

    select studid, studname
      from tstudent
     where not exists
           (select 1 
              from tstudtest
             where studid = tstudent.studid
               and testscore < 50)

it selects student id and student name only if the student failed no 
tests, which you find out by the subquery 

EXISTS and NOT EXISTS are boolean values

in some versions of sql you can get the same results with an EXCLUSIVE 
join, or with an OUTER join where you test for nulls signifying unmatched 
rows from one of the tables

<tip>
select as little as possible if all you want is to know whether a row 
exists -- in an EXISTS or NOT EXISTS query, select a literal, e.g. 1, so 
that the database can access an index if one exists, and not return 
columns which won't be needed anyway
</tip> 

matt asked me to repeat my previous tip as an actual tip so that it would 
get harvested

<tip>
you can use the SELECT INTO syntax in msaccess and sql/server and be 
selective about 
which columns and even which rows you copy over -- for example,

   select foo, bar
       into activerows
     from historytable
         where active=1

</tip>

rudy
r937.com