W3cubDocs

/SQLite

SQL As Understood By SQLite

[Top]

SQLite Keywords

The SQL standard specifies a large number of keywords which may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object. The list of keywords is so long that few people can remember them all. For most SQL code, your safest bet is to never use any English language word as the name of a user-defined object.

If you want to use a keyword as a name, you need to quote it. There are four ways of quoting keywords in SQLite:

   'keyword'
 A keyword in single quotes is a string literal.
  "keyword"
 A keyword in double-quotes is an identifier.
  [keyword]
 A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
  `keyword`
 A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.

For resilience when confronted with historical SQL statements, SQLite will sometimes bend the quoting rules above:

  • If a keyword in single quotes (ex: 'key' or 'glob') is used in a context where an identifier is allowed but where a string literal is not allowed, then the token is understood to be an identifier instead of a string literal.

  • If a keyword in double quotes (ex: "key" or "glob") is used in a context where it cannot be resolved to an identifier but where a string literal is allowed, then the token is understood to be a string literal instead of an identifier.

Programmers are cautioned not to use the two exceptions described in the previous bullets. We emphasize that they exist only so that old and ill-formed SQL statements will run correctly. Future versions of SQLite might raise errors instead of accepting the malformed statements covered by the exceptions above.

SQLite adds new keywords from time to time when it takes on new features. So to prevent your code from being broken by future enhancements, you should normally quote any identifier that is an English language word, even if you do not have to.

The list below shows all possible keywords used by any build of SQLite regardless of compile-time options. Most reasonable configurations use most or all of these keywords, but some keywords may be omitted when SQL language features are disabled. Applications can use the sqlite3_keyword_count(), sqlite3_keyword_name(), and sqlite3_keyword_check() interfaces to determine the keywords recognized by SQLite at run-time. Regardless of the compile-time configuration, any identifier that is not on the following 143 element list is not a keyword to the SQL parser in SQLite:

  1. ABORT
  2. ACTION
  3. ADD
  4. AFTER
  5. ALL
  6. ALTER
  7. ANALYZE
  8. AND
  9. AS
  10. ASC
  11. ATTACH
  12. AUTOINCREMENT
  13. BEFORE
  14. BEGIN
  15. BETWEEN
  16. BY
  17. CASCADE
  18. CASE
  19. CAST
  20. CHECK
  21. COLLATE
  22. COLUMN
  23. COMMIT
  24. CONFLICT
  25. CONSTRAINT
  26. CREATE
  27. CROSS
  28. CURRENT
  29. CURRENT_DATE
  30. CURRENT_TIME
  31. CURRENT_TIMESTAMP
  32. DATABASE
  33. DEFAULT
  34. DEFERRABLE
  35. DEFERRED
  36. DELETE
  37. DESC
  38. DETACH
  39. DISTINCT
  40. DO
  41. DROP
  42. EACH
  43. ELSE
  44. END
  45. ESCAPE
  46. EXCEPT
  47. EXCLUDE
  48. EXCLUSIVE
  49. EXISTS
  50. EXPLAIN
  51. FAIL
  52. FILTER
  53. FIRST
  54. FOLLOWING
  55. FOR
  56. FOREIGN
  57. FROM
  58. FULL
  59. GLOB
  60. GROUP
  61. GROUPS
  62. HAVING
  63. IF
  64. IGNORE
  65. IMMEDIATE
  66. IN
  67. INDEX
  68. INDEXED
  69. INITIALLY
  70. INNER
  71. INSERT
  72. INSTEAD
  73. INTERSECT
  74. INTO
  75. IS
  76. ISNULL
  77. JOIN
  78. KEY
  79. LAST
  80. LEFT
  81. LIKE
  82. LIMIT
  83. MATCH
  84. NATURAL
  85. NO
  86. NOT
  87. NOTHING
  88. NOTNULL
  89. NULL
  90. NULLS
  91. OF
  92. OFFSET
  93. ON
  94. OR
  95. ORDER
  96. OTHERS
  97. OUTER
  98. OVER
  99. PARTITION
  100. PLAN
  101. PRAGMA
  102. PRECEDING
  103. PRIMARY
  104. QUERY
  105. RAISE
  106. RANGE
  107. RECURSIVE
  108. REFERENCES
  109. REGEXP
  110. REINDEX
  111. RELEASE
  112. RENAME
  113. REPLACE
  114. RESTRICT
  115. RIGHT
  116. ROLLBACK
  117. ROW
  118. ROWS
  119. SAVEPOINT
  120. SELECT
  121. SET
  122. TABLE
  123. TEMP
  124. TEMPORARY
  125. THEN
  126. TIES
  127. TO
  128. TRANSACTION
  129. TRIGGER
  130. UNBOUNDED
  131. UNION
  132. UNIQUE
  133. UPDATE
  134. USING
  135. VACUUM
  136. VALUES
  137. VIEW
  138. VIRTUAL
  139. WHEN
  140. WHERE
  141. WINDOW
  142. WITH
  143. WITHOUT

SQLite is in the Public Domain.
https://sqlite.org/lang_keywords.html