315

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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Vineet
  • 5,029
  • 10
  • 29
  • 34

7 Answers7

206

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.

cmaher
  • 5,100
  • 1
  • 22
  • 34
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 43
    Double quotes are usually used to object names (e.g. column name "First name"). That is part of SQL-92 standard. – LukLed Jan 02 '10 at 18:27
  • @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 Jan 02 '10 at 18:31
  • 23
    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 Jan 02 '10 at 18:38
  • 171
    I always get this wrong until I realised a simple rule: [S]ingle quote for [S]trings, [D]ouble quote for things in the [D]atabase – Spacedman Jan 04 '12 at 14:49
  • 4
    You should use double quotes for identifiers. Single quotes goe against the standard. – Erwin Brandstetter Apr 28 '12 at 22:36
  • 8
    `SELECT PRODUCT.id 'product_id'` will ***not*** work in Oracle. Single quotes are **only** for character literals. They can not be used for identifiers in (standard) SQL (although some DBMS just ignore the SQL standard when it comes to quoted identifiers) –  Oct 30 '14 at 18:40
  • 2
    I think it would help to add to this answer that `'` and `"` are seen as the same thing in terms of language, where using one or the other is a matter of convention. I came here to double check that `"` doesn't pre-process the string, because in some languages the single and double quotes are processed differently (like Php or something). The answer wasn't directly clear on that, but more insinuated and I think it would help to directly add that to the answer. +1 anyways – Kellen Stuart May 25 '16 at 18:27
  • In PostgreSQL, double quotes are used for column names. http://stackoverflow.com/a/7651432/3114900 – ceruleus Aug 19 '16 at 08:08
  • In PostgreSQL you have to use double quotes if you want DB identifiers to be case sensitive: https://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql , e.g. `MYTABLE` without quotes is the same as `mytable`, the upper case version if `"MYTABLE"` with quotes. So I just tend to Always use quotes. Also as linked to by ceruleus, you need the double quotes if the name is a reserved identifier, one more reason to always use them. – Ciro Santilli OurBigBook.com Feb 03 '22 at 18:39
176

A simple rule for us to remember what to use in which case:

  • [S]ingle quotes are for [S]trings Literals (date literals are also strings);
  • [D]ouble quotes are for [D]atabase Identifiers;

Examples:

INSERT INTO "USERS" ("LOGIN", "PASSWORD", "DT_BIRTH") VALUES ('EDUARDO', '12345678', '1980-09-06');

In MySQL and MariaDB, the ` (backtick) symbol is the same as the " symbol. And note that you can't use " for literal strings when your SQL_MODE has ANSI_QUOTES enabled.

NaN
  • 8,596
  • 20
  • 79
  • 153
  • 7
    To clarify, backtick (`) can be used to delimit identifiers whether or not ANSI_QUOTES is enabled, but if ANSI_QUOTES is enabled, then "you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier." ([source](https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_ansi_quotes)). (This all assumes you're talking about MySQL, mind.) – Sam Oct 26 '16 at 09:34
  • 3
    But [**S**]ingle quotes are also for [**D**]ate literals. :-/ – Bill Karwin Feb 27 '19 at 20:22
74

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
Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
44

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
  • 62,604
  • 14
  • 116
  • 151
  • 4
    MySql uses backtick ` for identifier quoting. (just for completion) – dar7yl Feb 24 '10 at 06:09
  • 1
    Example: If you want to name a Postgres column "date" (which is reserved), you'll need to double quote it. – fny Aug 04 '14 at 18:23
12

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

guettli
  • 25,042
  • 81
  • 346
  • 663
3

Two Things To Remember :

Single Qutoes(') : String Or Text

select * from employees where room_name = 'RobinCapRed';

where RobinCapRed is a string or a text.

Double Quotes(") : Column Names or Table Names

select "first_name" from "employees";

where first_Name is a column name from employees table

majidshakeelshawl
  • 114
  • 6
1

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.

Ayushh Srivastava
  • 49
  • 4