In SQL, do I use double quotes or single quotes for string literals? What about for column names?

[chris_seymour1]

Knowledge Drop

Last tested: Mar 14, 2019

It depends on the dialect. In some dialects, such as MySQL, you can use single and double quotes interchangeably for string literals or column names. In other dialects, such as BigQuery Standard, you can use them interchangeably for string literals, but must use double quotes for column names. In still other dialects, such as Snowflake and Redshift, you must use single quotes for strings and double quotes for column names.

In general, using single quotes for strings and double quotes for column names is accepted for all SQL dialects, so that approach is the best practice. Remember this mnemonic:

[S]ingle quotes are for [S]trings ; [D]ouble quotes are for [D]atabase identifiers. Source

Examples of errors you may see if you do not adhere to this standard:

SELECT "1"
Snowflake: SQL compilation error: error line 1 at position 7 invalid identifier '"1"'
Redshift: ERROR: column "1" does not exist

SELECT 1 AS '1'
Snowflake: SQL compilation error: syntax error line 1 at position 12 unexpected ''1''
Redshift: ERROR: syntax error at or near "'1'" Position: 128

This content is subject to limited support.