Valid query to check if row exists in SQLite3

Question

Is this the most efficient way to check if a row exists in a table?

SELECT EXISTS(SELECT 1 FROM myTbl WHERE u_tag="tag"); 

Table is...

CREATE TABLE myTbl(id INT PRIMARY KEY, u_tag TEXT); 

Also what is the return value for this, if the row doesn't exist? Is it false (bool) or 0 (int) or NULL?

Answer 1

Though the documentation does not imply it, apparently the primary sqlite dev (Richard Hipp) has confirmed in the mailing list that EXISTS short circuits for you.

The query planner in SQLite, while not brilliant, is smart enough to know that it can stop and return true as soon as it sees the first row from the query inside of EXISTS().

So the query you proposed will be the most efficient:

SELECT EXISTS(SELECT 1 FROM myTbl WHERE u_tag="tag"); 

If you were nervous about portability, you could add a limit. I suspect most DBs will offer you the same short circuit however.

SELECT EXISTS(SELECT 1 FROM myTbl WHERE u_tag="tag" LIMIT 1); 

Selecting 1 is the accepted practice if you don't need something from the record, though what you select shouldn't really matter either way.

Put an index on your tag field. If you do not, a query for a non-existent tag will do a full table scan.

EXISTS states that it will return 1 or 0, not null.