Difference Between Single and Double Quotes in Sql

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

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.

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

Double quotes are for names of tables or fields. Sometimes You can omit them. The single quotes are for string constants. This is the SQL standard. In the verbose form, your query looks like this:

select * from "employee" where "employee_name"='elina';

single vs double quotes in WHERE clause returning different results

Just to expand on Gordon's answer a little. Your first query:

SELECT
  file_name
FROM table
WHERE file_name = "file_name"

In this case, the double quotes are causing the query engine to treat "file_name" as a column identifier, not a value, so that query is functionally the same as:

SELECT
  file_name
FROM table
WHERE file_name = file_name

Obviously (when written that way) the condition is always true, so the full table is returned.

When to use single quotes, double quotes, and backticks in MySQL

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

Single quotes should be used for string values like in the VALUES() list. Double quotes are supported by MySQL for string values as well, but single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double.

MySQL also expects DATE and DATETIME literal values to be single-quoted as strings like '2001-01-01 00:00:00'. Consult the Date and Time Literals documentation for more details, in particular alternatives to using the hyphen - as a segment delimiter in date strings.

So using your example, I would double-quote the PHP string and use single quotes on the values 'val1', 'val2'. NULL is a MySQL keyword, and a special (non)-value, and is therefore unquoted.

None of these table or column identifiers are reserved words or make use of characters requiring quoting, but I've quoted them anyway with backticks (more on this later...).

Functions native to the RDBMS (for example, NOW() in MySQL) should not be quoted, although their arguments are subject to the same string or identifier quoting rules already mentioned.

Backtick (`)
table & column ───────┬─────┬──┬──┬──┬────┬──┬────┬──┬────┬──┬───────┐
                      ↓     ↓  ↓  ↓  ↓    ↓  ↓    ↓  ↓    ↓  ↓       ↓
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`, `updated`) 
                       VALUES (NULL, 'val1', 'val2', '2001-01-01', NOW())";
                               ↑↑↑↑  ↑    ↑  ↑    ↑  ↑          ↑  ↑↑↑↑↑ 
Unquoted keyword          ─────┴┴┴┘  │    │  │    │  │          │  │││││
Single-quoted (') strings ───────────┴────┴──┴────┘  │          │  │││││
Single-quoted (') DATE    ───────────────────────────┴──────────┘  │││││
Unquoted function         ─────────────────────────────────────────┴┴┴┴┘    

Variable interpolation

The quoting patterns for variables do not change, although if you intend to interpolate the variables directly in a string, it must be double-quoted in PHP. Just make sure that you have properly escaped the variables for use in SQL. (It is recommended to use an API supporting prepared statements instead, as protection against SQL injection).

// Same thing with some variable replacements
// Here, a variable table name $table is backtick-quoted, and variables
// in the VALUES list are single-quoted 
$query = "INSERT INTO `$table` (`id`, `col1`, `col2`, `date`) VALUES (NULL, '$val1', '$val2', '$date')";

Prepared statements

When working with prepared statements, consult the documentation to determine whether or not the statement's placeholders must be quoted. The most popular APIs available in PHP, PDO and MySQLi, expect unquoted placeholders, as do most prepared statement APIs in other languages:

// PDO example with named parameters, unquoted
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (:id, :col1, :col2, :date)";

// MySQLi example with ? parameters, unquoted
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (?, ?, ?, ?)";

Characters requring backtick quoting in identifiers:

According to MySQL documentation, you do not need to quote (backtick) identifiers using the following character set:

ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

You can use characters beyond that set as table or column identifiers, including whitespace for example, but then you must quote (backtick) them.

Also, although numbers are valid characters for identifiers, identifiers cannot consist solely of numbers. If they do they must be wrapped in backticks.

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

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.

What is the difference between quote and double quote in mssql

Single quotes are for literal strings. Double quotes are the ANSI SQL Delimit Identifier for object names (T-SQL's delimit identifier is brackets ([])).

Don't use single quotes (') for aliases though, as (as mentioned) single quotes are for literal strings, not delimit identifying object names. Single quotes only work as delimit identifiers on aliases when you define said alias, no where else. This means that you can easily confuse those new to the language (and even those who've been using it for years if your formatting is poor).

As an example, ORDER BY 'column1' would not order by your column aliased as 'column1', it would order by the varchar literal 'column1'; effectively your data would not be ordered at all and would be returned in an arbitrary order (as every row has the same value for it ordering). Like wise in the following the 1st statement would return no rows and the second would error:

SELECT 'Column1'
FROM (SELECT [Column] + '_suffix' AS Column1
      FROM dbo.MyTable) MT
WHERE 'Column1' LIKE 'ID%';
GO
SELECT 'Column1' + 2
FROM (SELECT [SomeIntColumn] AS 'Column1'
      FROM dbo.MyTable) MT;

The first would return no rows as the literal varchar value 'Column1' will never be LIKE 'ID%'. The second would error as although the column has been aliased as 'Column1', and it's name suggests it's an int column, the outer SELECT would try to convert the literal varchar value 'Column1' to an int, and (obviously) that won't work.

Also some syntaxes with literal string aliases are deprecated (specifically 'string_alias' = expression).

Stick to object and alias names that don't need delimit identifying, and if you must delimit identify them use the T-SQL identifier, brackets ([]), or ANSI-SQL's, double quotes ("). column1 does not need identifying, so you should just define is as column1.

What is the difference between double quotes and single quotes in mysql query

I suspect that your plot string contains a single quote. To avoid the problem you should be escaping your string values using mysql_real_escape_string, or (better) use parameterized queries.

Your "solution" of changing the single quote to a double quote may appear to work in this case, but it will fail if the plot string contains a double quote.

---

Related Topics