Select exists vs count

Question

I am using Sqlite-net-pcl. I need to which query is better to use. I am searching in a table if there is at least one record.

First query

Select exists(Select 1 from invnentory where itemname='box2')

Second query

Select count(*) from inventory where itemname='box2'

Both queries are working right. But which is the best approach to go for sqlite-net-pcl?

score 0 · Accepted Answer · answered Aug 26 '20 at 12:29

This query:

Select count(*) from inventory where itemname = 'box2'

normally must do a full table scan to return the number of rows that satisfy the condition in the WHERE clause.

But this:

Select exists(Select 1 from invnentory where itemname='box2')

will return as soon as it finds the 1st row that satisfies the condition and it will do a full table scan only if there isn't such a row.

So EXISTS should perform better.

1 Answers1