Cleanest Way to determine whether a row exists?

Question

Is this the safest/optimal code for a simple "does the record exist" question:

. . . // "oc" is an OracleConnection component
const string sql = "SELECT COUNT(*) FROM CONGRESS WHERE IQ > WEIGHT"; //Most politicians have more pounds than sense
try {
    using (OracleCommand ocmd = new OracleCommand(sql, oc)) {
        return Convert.ToInt32(ocmd.ExecuteScalar()) > 0;
    }
. . . // the rest elided to spare the vertical scroll-bar bearings

?

score 3 · Accepted Answer · answered Jun 14 '12 at 17:42

3

Something like

SELECT 1
  FROM dual
 WHERE EXISTS( SELECT 1
                 FROM congress
                WHERE iq > weight )

is probably the safest option since that allows Oracle to stop executing the query as soon as one matching row is found rather than scanning the entire table to get an accurate count. Something like

SELECT COUNT(*) 
  FROM congress
 WHERE iq > weight
   AND rownum <= 1

should do the same thing as well. In my mind, the first query is more expressive of your intent but it's also a bit more verbose.

answered Jun 14 '12 at 17:42

Justin Cave

227,342
24
367
384

So what is returned if the record doesn't exist? Should that be run under `ExecuteScalar` or `ExecuteReader`? – saluce Jun 14 '12 at 17:46
The first query will return either a 1 or an empty result set. The second will return a result set with a single row-- either a 0 or a 1. You can use either `ExecuteScalar` or `ExecuteReader` with either query though `ExecuteScalar` is probably easier. – Justin Cave Jun 14 '12 at 17:52

Cleanest Way to determine whether a row exists?

1 Answers1