Re: Question: select that just detects a matching row

Chris asked:
>I have many places in my application where the user can enter some
>search criteria, a date lets say, and then makes a request against a
>table just to see whether any rows matching the criteria are present. In
>this part of the application, I'm not interested in actually getting the
>data from the table, or even how many rows match the criteria, I just
>want to see if some row in the table matches the criteria. What I'm
>doing now of course is a "select count(*) from table_name where column
>name = search_criteria". I don't need the total count, and I've got a
>performance problem with running this query against the tables. Is
>there any function that's faster that this that will stop and return a
>"true" value when it encounters the first row that matches the where
>clause, instead of scanning through all rows and counting them up, in my
>case unneccesarily? Any ideas would be appreciated...
>
>Chris

Hi Chris,

You could try using EXISTS

e.g.:

SELECT
  1
FROM
  dual
WHERE
  EXISTS
  (SELECT
    1
   FROM
    table_name
   WHERE
    column_name = criteria);

As far as I remember exists will quit as soon as it finds a matching row.

Hope this helps

Regards

John

Parallax Solutions Ltd.   Tel.: 01203 514522
Stonecourt,               Fax.: 01203 514401
Siskin Drive,             Web : http://www.parallax.co.uk/~johne
Coventry CV3 4FJ          Mail: johne_at_parallax.co.uk
________________________________________________________________
       Kaizen - Japanese, Lit: Never ending improvement
________________________________________________________________