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 136 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. EXCLUSIVE
  48. EXISTS
  49. EXPLAIN
  50. FAIL
  51. FILTER
  52. FOLLOWING
  53. FOR
  54. FOREIGN
  55. FROM
  56. FULL
  57. GLOB
  58. GROUP
  59. HAVING
  60. IF
  61. IGNORE
  62. IMMEDIATE
  63. IN
  64. INDEX
  65. INDEXED
  66. INITIALLY
  67. INNER
  68. INSERT
  69. INSTEAD
  70. INTERSECT
  71. INTO
  72. IS
  73. ISNULL
  74. JOIN
  75. KEY
  76. LEFT
  77. LIKE
  78. LIMIT
  79. MATCH
  80. NATURAL
  81. NO
  82. NOT
  83. NOTHING
  84. NOTNULL
  85. NULL
  86. OF
  87. OFFSET
  88. ON
  89. OR
  90. ORDER
  91. OUTER
  92. OVER
  93. PARTITION
  94. PLAN
  95. PRAGMA
  96. PRECEDING
  97. PRIMARY
  98. QUERY
  99. RAISE
  100. RANGE
  101. RECURSIVE
  102. REFERENCES
  103. REGEXP
  104. REINDEX
  105. RELEASE
  106. RENAME
  107. REPLACE
  108. RESTRICT
  109. RIGHT
  110. ROLLBACK
  111. ROW
  112. ROWS
  113. SAVEPOINT
  114. SELECT
  115. SET
  116. TABLE
  117. TEMP
  118. TEMPORARY
  119. THEN
  120. TO
  121. TRANSACTION
  122. TRIGGER
  123. UNBOUNDED
  124. UNION
  125. UNIQUE
  126. UPDATE
  127. USING
  128. VACUUM
  129. VALUES
  130. VIEW
  131. VIRTUAL
  132. WHEN
  133. WHERE
  134. WINDOW
  135. WITH
  136. WITHOUT

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