89

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?

mkrieger1
  • 19,194
  • 5
  • 54
  • 65
sazr
  • 24,984
  • 66
  • 194
  • 362

2 Answers2

140

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.

Tom Kerr
  • 10,444
  • 2
  • 30
  • 46
  • Passing this raw query into the cursor, how can I get 0 or 1 from cursor? Is it cursor.getInt(0), cursor.getCount(), or cursor.getColumnCount()? – Ben Sewards Jul 25 '13 at 21:26
  • 4
    `EXISTS` always returns a result, so only checking the value would work. – Tom Kerr Jul 25 '13 at 21:29
  • 1
    To be certain, checking the value of a cursor for an int would be getInt(0) correct? – Ben Sewards Jul 25 '13 at 21:51
  • 2
    @BenSewards The original question isn't language specific, so I can only guess that on your platform that is correct. I would recommend trying it. – Tom Kerr Jul 26 '13 at 18:03
  • 2
    @BenSewards you might use the alias method such as: SELECT EXISTS(SELECT 1 FROM myTbl WHERE u_tag="tag") AS alias_naming; Then you could use cursor.getInt("alias_naming") to get the value. – Cloud Chen Oct 31 '13 at 03:28
  • The `LIMIT` isn't necessary as `EXISTS` will short-circuit as soon as it has a value. – Anurag May 07 '15 at 04:55
  • 1
    @Anurag: You claim it does, the answer claims it doesn't. One of you should cite a source. – Lynn Apr 17 '16 at 22:33
  • 9
    @Lynn Here's a [comment](http://sqlite.1065341.n5.nabble.com/Re-Fastest-way-to-find-whether-at-least-one-row-has-a-certain-column-value-td87944.html) from the original author of SQLite, Richard Hipp: "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()." – Anurag Apr 18 '16 at 23:43
17

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).