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

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

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.

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" >20;

You can apply the same case to Common Table expressions also.

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).

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 :)

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