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

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.