Valid query to check if row exists in SQLite3

by

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.

Related Contents:

  1. Sqlite primary key on multiple columns
  2. Insert new column into table in sqlite?
  3. Store boolean value in SQLite
  4. INSERT IF NOT EXISTS ELSE UPDATE?
  5. Create table in SQLite only if it doesn’t exist already
  6. Does it matter what extension is used for SQLite database files?
  7. Escape single quote character for use in an SQLite query
  8. How do you rename a table in SQLite 3.0?
  9. Difference between 2 dates in SQLite
  10. How do DATETIME values work in SQLite?
  11. Efficient paging in SQLite with millions of records
  12. Can I read and write to a SQLite database concurrently from multiple connections?
  13. System.Data.SQLite Close() not releasing database file
  14. Change SQLite default settings
  15. Android – Pulling SQlite database android device
  16. Export from sqlite to csv using shell script
  17. ALTER COLUMN in sqlite
  18. change sqlite file size after “DELETE FROM table”
  19. Using UUIDs in SQLite
  20. How to configure sqlite to display headers by default
  21. Exit a SQLite 3 database
  22. How do I store and retrieve a blob from sqlite?
  23. Update with Join in SQLite
  24. Will SQLite performance degrade if the database size is greater than 2 gigabytes?
  25. What is the difference between SQLite integer data types like int, integer, bigint, etc.?
  26. Export CSV without col.names
  27. How do I DROP a constraint from a sqlite (3.6.21) table?
  28. Modify a Column’s Type in sqlite3
  29. Set start value for AUTOINCREMENT in SQLite
  30. How to create a datetime column with default value in sqlite3?
  31. How to automate a process with the sqlite3.exe command line tool?
  32. Check if a column exists in SQLite
  33. Create a foreign key in SQLite database browser
  34. Case sensitive and insensitive like in SQLite
  35. How to import a tsv file with SQLite3
  36. How to run command-line SQLite query and exit?
  37. How to get a list of column names
  38. DbFunctions.TruncateTime LINQ equivalent in EF CORE
  39. SQLite Modify Column
  40. sqlite get ROWID
  41. Create empty sqlite db from command line
  42. SQLite Syntax for Creating Table with Foreign Key
  43. SQL Server Compact 4.0 vs. SQLite
  44. Sqlite group_concat ordering
  45. Does SQLite lock the database file on reads?
  46. What is the format of Chrome’s timestamps?
  47. Sqlite over a network share [closed]
  48. Is there a command to clear screen in SQLite3?
  49. What is the maximum size limit of varchar data type in sqlite?
  50. SQLite problem selecting two columns as one

Leave a Comment