What does the SQL Standard say about usage of backtick(`)?

by

The SQL standard (current version is ISO/IEC 9075:2011, in multiple parts) says nothing about the ‘back-tick’ or ‘back-quote’ symbol (Unicode U+0060 or GRAVE ACCENT); it doesn’t recognize it as a character with special meaning that can appear in SQL.

The Standard SQL mechanism for quoting identifiers is with delimited identifiers enclosed in double quotes:

SELECT "select" FROM "from" WHERE "where" = "group by";

In MySQL, that might be written:

SELECT `select` FROM `from` WHERE `where` = `group by`;

In MS SQL Server, that might be written:

SELECT [select] FROM [from] WHERE [where] = [group by];

The trouble with the SQL Standard notation is that C programmers are used to enclosing strings in double quotes, so most DBMS use double quotes as an alternative to the single quotes recognized by the standard. But that then leaves you with a problem when you want to enclose identifiers.

Microsoft took one approach; MySQL took another; Informix allows interchangeable use of single and double quotes, but if you want delimited identifiers, you set an environment variable and then you have to follow the standard (single quotes for strings, double quotes for identifiers); DB2 only follows the standard, AFAIK; SQLite appears to follow the standard; Oracle also appears to follow the standard; Sybase appears to allow either double quotes (standard) or square brackets (as with MS SQL Server — which means SQL Server might allow double quotes too). This page (link AWOL since 2013 — now available in The Wayback Machine) documents documented all these servers (and was helpful filling out the gaps in my knowledge) and notes whether the strings inside delimited identifiers are case-sensitive or not.

As to when to use a quoting mechanism around identifiers, my attitude is ‘never’. Well, not quite never, but only when absolutely forced into doing so.

Note that delimited identifiers are case-sensitive; that is, "from" and "FROM" refer to different columns (in most DBMS — see URL above). Most of SQL is not case-sensitive; it is a nuisance to know which case to use. (The SQL Standard has a mainframe orientation — it expects names to be converted to upper-case; most DBMS convert names to lower-case, though.)

In general, you must delimit identifiers which are keywords to the version of SQL you are using. That means most of the keywords in Standard SQL, plus any extras that are part of the particular implementation(s) that you are using.

One continuing source of trouble is when you upgrade the server, where a column name that was not a keyword in release N becomes a keyword in release N+1. Existing SQL that worked before the upgrade stops working afterwards. Then, at least as a short-term measure, you may be forced into quoting the name. But in the ordinary course of events, you should aim to avoid needing to quote identifiers.

Of course, my attitude is coloured by the fact that Informix (which is what I work with mostly) accepts this SQL verbatim, whereas most DBMS would choke on it:

CREATE TABLE TABLE
(
    DATE    INTEGER NOT NULL,
    NULL    FLOAT   NOT NULL,
    FLOAT   INTEGER NOT NULL,
    NOT     DATE    NOT NULL,
    INTEGER FLOAT   NOT NULL
);

Of course, the person who produces such a ridiculous table for anything other than demonstration purposes should be hung, drawn, quartered and then the residue should be made to fix the mess they’ve created. But, within some limits which customers routinely manage to hit, keywords can be used as identifiers in many contexts. That is, of itself, a useful form of future-proofing. If a word becomes a keyword, there’s a moderate chance that the existing code will continue to work unaffected by the change. However, the mechanism is not perfect; you can’t create a table with a column called PRIMARY, but you can alter a table to add such a column. There is a reason for the idiosyncrasy, but it is hard to explain.

More Related Contents:

  1. Selecting more than one value
  2. Is it possible to insert multiple rows at a time in an SQLite database?
  3. SQLite Reset Primary Key Field
  4. How do I dump the data of some SQLite3 tables?
  5. SQLite – How do you join tables from different databases?
  6. Does SQLite3 not support foreign key constraints?
  7. SQLite – ORDER BY RAND()
  8. Address standardization within a database
  9. Query to get records based on Radius in SQLite?
  10. How do I add a foreign key to an existing SQLite table?
  11. How to use SQL Order By statement to sort results case insensitive?
  12. Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine
  13. sqlite alter table add MULTIPLE columns in a single statement
  14. Is there AUTO INCREMENT in SQLite?
  15. Name database design notation you prefer and why?
  16. Get month from DATETIME in sqlite
  17. How do I import .sql files into SQLite 3?
  18. sqlite equivalent of row_number() over ( partition by …?
  19. How to create timestamp column with default value ‘now’?
  20. Android multiple databases open
  21. SQLite3 UNIQUE constraint failed error
  22. Does SQLite3 have prepared statements in Node.js?
  23. SQlite: select into?
  24. SQL error: misuse of aggregate
  25. IF() statement alternative in SQLite
  26. SQLite query, ‘LIKE’
  27. How to use a calculated column by another calculated column
  28. UPSERT *not* INSERT or REPLACE
  29. Drop column from SQLite table
  30. converting int to real in sqlite

Leave a Comment