Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

mysql

mariadb

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.

like image 962
Anders Feder Avatar asked Apr 01 '15 23:04

Anders Feder


People also ask

What is the difference between backticks and apostrophe?

The single quote should be used in your writing instead of using the back quote. In other words, use a single quote instead of a back quote when writing the apostrophe in words like "don't" and "I'm." When used as an accent, the grave accent indicates pitch, stress, or tone of a letter.

What is the Backtick used for?

The backtick ` is a typographical mark used mainly in computing. It is also known as backquote, grave, or grave accent. The character was designed for typewriters to add a grave accent to a (lower-case) base letter, by overtyping it atop that letter.

What is the difference between single double and Backtick quotes for strings?

As you may have understood now, there is no real difference between using single quotes, double quotes, or backticks. You can choose one or multiple styles based on your preference. However, It is always good to stick to a single format throughout the project to keep it neat and consistent.

What is the difference between single quote and apostrophe?

They are two entirely different punctuation symbols. Single quotes are limited to one real function in written U.S. English, which is to indicate a quotation within a quotation. Apostrophes, on the other hand, are used to denote possessive form and to indicate omission.


1 Answers

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

like image 190
Rick James Avatar answered Sep 28 '22 05:09

Rick James