What is the difference between single quotes and double quotes in SQL?
+6
A:
Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes generally aren't used in SQL, but that can vary from database to database.
Stick to using single quotes.
That's the primary use anyways. You can use single quotes for a column alias - where you want the column name you reference in your application code to be something other than what the column is actually called in the database. For example: PRODUCTS.id would be more readable as product_id, so you use either of the following:
- SELECT PRODUCT.id
AS product_id - SELECT PRODUCT.id
'product_id'
Either works in Oracle, SQL Server, MySQL... But I know some have said that the TOAD IDE seems to give some grief when using the single quotes approach.
You do have to use single quotes when the column alias includes a space character. IE: "product id", but it's not recommended practice for a column alias to be more than one word.
OMG Ponies
2010-01-02 18:22:01
Double quotes are usually used to object names (e.g. column name "First name"). That is part of SQL-92 standard.
LukLed
2010-01-02 18:27:49
@LukLed: You mean a column *alias*? If you say so - I've only ever seen and/or used the `AS column_name` notation, or where you supply the alias in **single quotes** after the column reference. Have to enclose the alias in single quotes if there is a space character (which I don't recommend) in the column alias.
OMG Ponies
2010-01-02 18:31:40
No. I meant column names, but it concerns aliases too. I would advise to use double quotation marks for aliases and names with unusual characters, because of SQL-92 standard. `SELECT *FROM USERS 'Users'` doesn't work in SQL Server, but `SELECT *FROM USERS "Users"` does.
LukLed
2010-01-02 18:38:18
+4
A:
In ANSI SQL, double quotes quote object names (e.g. tables) which allows them to contain characters not otherwise permitted, or be the same as reserved words (Avoid this, really).
Single quotes are for strings.
However, MySQL is oblivious to the standard (unless its SQL_MODE is changed) and allows them to be used interchangably for strings.
Moreover, Sybase and Microsoft also use square brackets for identifier quoting.
So it's a bit vendor specific.
Other databases such as Postgres and IBM actually adhere to the ansi standard :)
MarkR
2010-01-02 18:40:22
+1
A:
Double quotes do not work in several SQL server products (MySQL, Microsoft SQL Server), unless enabled.
For instance, in SQL Server, you have to SET QUOTED_IDENTIFIER ON in the beginning of your script.
naivists
2010-01-02 18:41:18
+4
A:
Single quotes delimit a string constant or a date/time constant.
Double quotes delimit identifiers for e.g. table names or column names. This is generally only necessary when your identifier doesn't fit the rules for simple identifiers.
See also:
You can make MySQL use double-quotes per the ANSI standard:
SET GLOBAL SQL_MODE=ANSI_QUOTES
You can make Microsoft SQL Server use double-quotes per the ANSI standard:
SET QUOTED_IDENTIFIER ON
Bill Karwin
2010-01-02 18:42:43