What is the difference between a single quote and a double quotation mark in SQL?

28

Context

When I do a SQL and I want a specific alias I should enclose double-quotes ".

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

When I want to make a where in of a field varchar the values must be in single quotation mark '.

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

Question

What is the difference between a simple double-quote ' and double-quote ' in SQL?

Addendum

If I make a SELECT of SELECT - SUBQUERY - of fields that have alias in ", my SELECT outermost must also have " .

SELECT "Pessoa.nome" FROM
(
SELECT A.ds_nome_pessoa AS "Pessoa.nome" ...
)
    
asked by Guilherme Lautert 25.01.2016 в 12:21
source

1 answer

21

In standard SQL only apostrophes (single quotation marks) are recognized as literal delimiters strings or some other type of data (I believe other types are specific rather than standard). Specific vendors support the use of double quotes for the same function, but this may vary. Some require configuration. Examples that support use are MySQL, SQL Server, and Oracle.

Some databases use one or both to create a column alias, so you do not need to use AS . But it's specific and not standard. Better to use the pattern that is unambiguous. The pattern determines that the alias is double-quoted. Or without them. All major banks accept the default, even if you need to set this up.

All mainstream SQL banks use the (double) quotation marks 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 [] (SQL Server) brackets. The delimiter may be required when there is space in the identifier or use a reserved word. It may vary by vendor.

MySQL Configuration :

SET GLOBAL SQL_MODE=ANSI_QUOTES

SQL Server Configuration :

SET QUOTED_IDENTIFIER ON

There are cases, such as PostgreSQL, where using the double quotes causes the identifier name to handle box sensitivity, which is not normal for SQL. It's good to be sure what you're doing and if that's what you want. The use of double quotes is required, along with &U to write Unicode characters. But it's best to avoid using it.

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

You have to consult specific vendor-specific documentation or ask specific questions here.

I have not found anything in documentation that makes the literal string elements of the IN operator written in one way or another. I count on the documentation. The documentation says that ANSI should use single quotes.

Option

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

Choose a way to use it and keep it. Do not trade without reason. Only do it if you have a good reason to make an exception. Make sure that there are reasons that will hinder the SQL of the provider you are using or the host language of the query assembly.

Note that the SQL standard is the single quotes. And the standard of all the mainstream languages I know are double quotation marks, although I may, and some prefer, use the simple ones in those languages. I particularly adopt this pattern because it works well in more than 99% of cases. The contrary pattern works well in far fewer cases, depending on the database, something up to close to 99%, but there are cases that pretty much do not work. It will one day need to adapt the code to another bank ...

The bottom line is that it's best to just use double quotation marks where you really do. If I found any situation where I was obliged - I do not think I ever found it - I would try to find a different solution. I do not know if everyone agrees.

    
answered by 25.01.2016 / 12:45
source