SQL Reference ยท Dialects and Concepts
SQL identifier and string quoting across dialects
Which quotes mean what in SQL: single quotes for strings, double quotes for identifiers, backticks and brackets, escaping, case folding and SQLite quirks.
SQL uses quotes for two different jobs. Single quotes wrap a string literal ('Lisbon'). Double quotes wrap an identifier, which is the name of a table, column or alias ("order"). This is the standard, and PostgreSQL, Oracle, SQL Server (with default settings) and SQLite follow it. MySQL adds backticks for identifiers, and SQL Server adds square brackets.
Quoting an identifier lets you use a reserved word or spaces in a name, and makes the name case-sensitive in most systems. The cost is that you must quote it the same way everywhere afterwards.
SELECT 'It''s fine' AS text_value, 1 AS "order";
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
'string literal' -- standard 'it''s' -- a single quote inside a string is doubled "identifier" -- standard delimited identifier "say ""hi""" -- a double quote inside an identifier is doubled `identifier` -- MySQL, MariaDB (and accepted by SQLite) [identifier] -- SQL Server (and accepted by SQLite) N'unicode string' -- SQL Server, Oracle, standard national literal
| Form | Meaning |
|---|---|
'text' |
A string value. Always a value, in every dialect. |
"name" |
An identifier in standard SQL. In MySQL (default mode) a string. |
`name` |
An identifier in MySQL and MariaDB. |
[name] |
An identifier in SQL Server. |
Dialect differences
| Topic | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Identifier quotes | "x", `x`, [x] |
"x" |
`x` ("x" with ANSI_QUOTES) |
[x], "x" |
"x" |
| Double quotes make a string? | only as a fallback | no | yes (default) | no | no |
| Backslash escapes in strings | no | no (unless E'...') |
yes (default) | no | no |
| Unquoted names fold to | keeps case, matches ignoring case | lower case | as typed (table names depend on the OS) | as typed, collation decides | upper case |
| Quoted names | case-insensitive | case-sensitive | case depends on settings | collation decides | case-sensitive |
-- PostgreSQL: these are three different things SELECT * FROM Users; -- the table users SELECT * FROM "Users"; -- the table Users (capital U) SELECT * FROM "users"; -- the table users again -- MySQL: backticks, and a backslash is an escape in strings SELECT `order` FROM `group` WHERE note = 'it\'s fine'; -- SQL Server SELECT [order] FROM [group]; -- Oracle: unquoted names are upper-cased, so these are the same table SELECT * FROM employees; SELECT * FROM EMPLOYEES; SELECT * FROM "employees"; -- a different table, if it exists
SQLite has an unusual rule: if a double-quoted name does not match any column, it silently becomes a string literal. That means WHERE name = "Lisbon" can seem to work, and WHERE nmae = "Lisbon" returns no rows instead of an error. Always use single quotes for strings so a typo fails loudly.
Usage notes
- Strings use single quotes everywhere. Doubling a single quote (
'') is the portable escape. Do not build strings with\'; it only works in MySQL. - Never insert user input into SQL text by quoting it. Escaping rules differ by dialect, and mistakes cause injection holes. Use bind parameters (SQL injection).
- Avoid quoted identifiers in your own schema. Choose plain
snake_casenames that are not reserved words, so you never need quotes. Names such asuser,order,group,indexandkeyare the usual culprits. - Quoting changes case rules. Once a PostgreSQL or Oracle name is created in quotes with capital letters, every query must quote it exactly.
- Aliases may be quoted when they contain spaces (
AS "total sales"). Use that for display only; reports generally want simple aliases. - A column named like a keyword still works when quoted, but tools, ORMs and migrations may mishandle it. Rename it.