What is the difference between single quote ' and double quote " in SQL?

Asked

Viewed 5,487 times

31

Context

When I do an SQL and wish for a specific alias I should quote between double quotes ".

SELECT A.ds_nome_pessoa AS "Pessoa.nome" ...

When I want to make a where in field varchar the values should be in a single quote '.

WHERE A.ds_nome_pessoa IN (
    'Jose',
    'Maria',
    'Joao'
)

Question

What’s the difference between single quote ' double quote " in SQL?

Addendum

If I make one SELECT of SELECT - SUBQUERY - of fields that have alias in ", my SELECT more external should also have ".

SELECT "Pessoa.nome" FROM
(
SELECT A.ds_nome_pessoa AS "Pessoa.nome" ...
)
sql characteristic-language encoding-style
  • 1

    See if you can help. More anyway big question.http://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql.

  • At least no postgres a name identify with capital letters need double quotes ... : P in this case just give the alias in low box.

  • Speaking of PHP, if you use double quotes when in the middle of the expression there is a variable, $var. When not have use simple quotes. If you do: $var = 5; echo '$var';, won’t show 5 and yes $var. If you do echo "$var"; will display the variable value.

1 answer

22


In standard SQL only apostrophes (single quotes) are recognized as literal delimiter strings or some other type of data (I believe that for other types it is specific and not standard). Specific vendors admit the use of double quotes for the same function, but this may vary, some require configuration. Examples that admit the use are Mysql, SQL Server and Oracle.

Some databases use one or both to create a column alias, so you don’t need to use the AS. But it is specific and non-standard. Better to use the pattern which is unambiguous. The default determines that the alias is double-quote, or without double-quote. All major banks accept the standard, even if you need to set it up.

All SQL databases mainstream use (double) quotes to delimit the name of an identifier (table name, column, alias, etc.), some allow you to configure how you will use it. Some use the backtick (crase) to do this. Others use the clasps [] (SQL Server). The delimiter may be required when there is space in the identifier or uses a reserved word. It may vary by vendor.

Mysql configuration:

SET GLOBAL SQL_MODE=ANSI_QUOTES

Configuration of SQL Server:

SET QUOTED_IDENTIFIER ON

There are cases, such as Postgresql, where the use of double quotes makes the name of the identifier consider the case sensitivity, which is not normal for SQL. It is good to be sure what you are doing and if that is what you want. The use of double quotes is necessary, along with &U to write Unicode characters, but it is better to avoid its use.

Remember that the use of delimiters are almost never necessary for identifiers. If the person wants they are never necessary. For me delimiters should only be used in literals. And if you need to use the same identifier, let it be an extreme exception.

You must consult the specific documentation of each supplier or ask specific questions here.

I found nothing in documentation that makes the literal elements string of the operator IN be written one way or another, I rely on the documentation and it says that ANSI should use simple quotes.

Option

Another reason to use simple quotes in SQL is that it is normal for the query to be mounted within another language by strings which will probably be delimited by the pairs, so it is best to avoid confusion. If the language used delimits strings with the simple, then it is better to use the double in SQL.

Choose a form of use and keep it, do not change it for no reason. Only do it if you have a good reason to make an exception. Do if there are reasons that disrupt the SQL of the vendor you are using or the host language of the query mount.

Note that the default SQL quotes are simple quotes. And the default of all languages mainstream What I know are the double quotation marks, although I can, and some prefer, use the single ones in those languages. I particularly adopt this pattern because it works well in more than 99% of cases. The opposite pattern works well in far fewer cases, depending on the database, something up to 99%, but there are cases that hardly work. One day you’ll need to adapt the code to another bank...

The conclusion is that it is best to only use double quotes where it really is absolutely obligatory. If I found any situation where I was obliged - I think I never found it - I would try to find a different solution. I don’t know if everyone agrees.

Browser other questions tagged sql characteristic-language encoding-style

You are not signed in. Login or sign up in order to post.