What is the difference between single and double quotes in SQL

Question

What is the difference between single quotes and double quotes in SQL

User · Answer

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:

Do different databases use different name quote?

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

User · Answer

A simple rule for us to remember what to use in which case     S ingle quotes are for  S trings    D ouble quotes are for  D atabase identifiers     In MySQL and MariaDB  the    backtick  symbol is the same as the   symbol  You can use   when your SQL MODE has ANSI QUOTES enabled

User · Answer

I use this mnemonic    Single quotes are for strings  one thing  Double quotes are for tables names and column names  two things    This is not 100  correct according to the specs  but this mnemonic helps me  human being

User · Answer

The difference lies in their usage  The single quotes are mostly used to refer a string in WHERE  HAVING and also in some built-in SQL functions like CONCAT  STRPOS  POSITION etc   When you want to use an alias that has space in between then you can use double quotes to refer to that alias   For example   select account id count     count of  from orders group by 1 sub    Here is a subquery from an orders table having account id as Foreign key that I am aggregating to know how many orders each account placed  Here I have given one column any random name as  count of  for sake of purpose    Now let s write an outer query to display the rows where  count of  is greater than 20   select  count of  from   select account id count     count of  from orders group by 1 sub where  count of   gt 20    You can apply the same case to Common Table expressions also

User · Answer

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 anyway   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  PRODUCT 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  e g   product id  but it s not recommended practice for a column alias to be more than one word

User · Answer

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

[sql] What is the difference between single and double quotes in SQL?

Examples related to sql

Examples related to database

Examples related to quotes