Valid query to check if row exists in SQLite3

问题:

Is this the best(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, is it false(bool) or 0(int) or NULL?

回答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.

回答2:

Again, quoting from the documentation:

The EXISTS operator always evaluates to one of the integer values 0 and 1. If executing the SELECT statement specified as the right-hand operand of the EXISTS operator would return one or more rows, then the EXISTS operator evaluates to 1. If executing the SELECT would return no rows at all, then the EXISTS operator evaluates to 0.

As to whether or not using EXISTS is more efficient than, say, using count(*), that may depend on the size of the table and whether the table has an index. Try an EXPLAIN on both queries to benchmark (or just time each of them).