What makes the big difference between a backtick and an apostrophe?

Question

What is the reason that the following two queries give wildly different results?

MariaDB [mydatabase]> SELECT COUNT(DISTINCT(`price`)) FROM `products`; --Good
+--------------------------+
| COUNT(DISTINCT(`price`)) |
+--------------------------+
|                     2059 |
+--------------------------+
1 row in set (0.01 sec)

MariaDB [mydatabase]> SELECT COUNT(DISTINCT('price')) FROM `products`; --Bad
+--------------------------+
| COUNT(DISTINCT('price')) |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.01 sec)

I've googled around for an explanation of the difference between backticks and apostrophes (aka. single quotes), but I am unable to find any indication as to why they would be interpreted differently for a column name like in the above.

Is it that the single-quoted string in the latter query is actually not interpreted as a column name, but just as an arbitrary string literal, of which there could be said to be "1"? If so, it ain't easy to find any pages expounding on this meaning of the apostrophe.

Blather

Answer 1

'price' (apostrophes or quotes) is a string. It never changes, so the count is always 1.

`price` (backtics) refers to the column price. So it could be more than 1.

The inner parentheses are irrelevant. COUNT(DISTINCT price) is the same as your backtic version.

• SELECT COUNT(*) FROM tbl WHERE ... is a common way to ask how many rows.
• SELECT foo, COUNT(*) FROM tbl GROUP BY foo is a common way to ask how many rows for each distinct value of foo.
• SELECT foo, COUNT(foo) FROM tbl GROUP BY foo is the same as above, but does not count rows where foo IS NULL.

SELECT DISTINCT ... GROUP BY ... is a nonsense statement. Either use DISTINCT or use GROUP BY.

Fellah

Answer 2

A straight single quote (') is used for string literals (along with straight double quote (")).

A backtick quote (`) is for quoting identifiers.

Identifiers must be quoted if they match a reserved word, or if they contain special characters. Quoted identifiers also can specify lowercase in case-insensitive fields (which otherwise might be shown as uppercase).

CREATE TABLE MyTable (Field INT);
DESCRIBE MyTable;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| FIELD   | INT         | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

See also ANSI quote mode.

Ansate

Answer 3

Please find MariaDB's documentation about identifier names and string literals:

https://mariadb.com/kb/en/mariadb/identifier-names/

https://mariadb.com/kb/en/mariadb/string-literals/

Backquotes always quote identifier namens. Single quotes always quote string literals.

Identifier names get replaced by their value, string literals don't:

SELECT `price`, 'price' FROM products;
+-------+-------+
|     1 | price |
|     1 | price |
|     2 | price |
|     3 | price |
|     3 | price |
+-------+-------+

Oni

I see nothing in those pages to suggest that COUNT(DISTINCT) would return 1 upon a string literal, which was what really threw me off, but thanks anyway. – Blather

Answer 4

The reason the two queries give wildly different results is because they are counting different things due to the way the values are being passed to the COUNT(DISTINCT ...) function.

1. In the first query:

   SELECT COUNT(DISTINCT(`price`)) FROM `products`;
   

   • Here, COUNT(DISTINCT(price)) counts the number of distinct values in the price column of the products table.
   • It counts the number of unique prices in the price column.

2. In the second query:

   SELECT COUNT(DISTINCT('price')) FROM `products`;
   

   • Here, COUNT(DISTINCT('price')) counts the number of distinct values of the string literal 'price'.
   • Since the string literal 'price' is passed to the DISTINCT function, there is only one distinct value ('price' itself).
   • Therefore, the result is 1.

To clarify:

• In the first query, you're counting the number of distinct prices stored in the price column of the products table.
• In the second query, you're counting the number of distinct values of the string literal 'price', which is always 1 because there's only one distinct value.

Make sure to pass column names or expressions to functions like DISTINCT in order to get meaningful results based on the data in your database. Passing a string literal like 'price' typically doesn't provide useful information in such contexts.

Sclerosed

Pardon me, but I don't see anything in your answer that does not already appear in the other answers. If that is correct, then what is the point of adding another answer that simply repeats what already appears in other answers? – Grenadines

You're correct, my answer reiterates points made in previous responses. I apologize for any redundancy. I aimed to provide a concise summary for clarity. If you have any further questions or need additional clarification, feel free to ask. – Sclerosed

Answer 5

Also when using Google Big Query or Microsoft SQL Server, the backtick serves an important purpose.

SELECT * FROM `<table name>`;

This really comes in handy especially when the table name is in multiple directories especially from Google Big Query. example

SELECT * FROM `domain.sub-domain.sql.mysql.tables.employee`;

Efficiency