Why does using '' or "" doesn't sort column aliases via ORDER BY in MySQL?

Question

Today in a live learning session with a friend of mine Dumisani Ndubane, we found out a slight change in behavior when using ORDER BY to sort a result set with column alias by using the ``, '' or "" quote types.

'' and "" aren't affect by the sorting, only `` works but all quotes are allowed to do column aliasing. See queries below;

This sort the Full name in ascending order (using `` quotes on column alias):

SELECT
    CONCAT_WS(', ', lastName, firstname) `Full name`
FROM
    employees
ORDER BY
    `Full name`;

This doesn't sort the result set. Note we used '' quotes

SELECT
    CONCAT_WS(', ', lastName, firstname) 'Full name'
FROM
    employees
ORDER BY
    'Full name';

This doesn't sort the result set. Note we used "" quotes

SELECT
    CONCAT_WS(', ', lastName, firstname) "Full name"
FROM
    employees
ORDER BY
    "Full name";

Also, we where using MySQL version mysql Ver 8.0.19 for osx10.13 on x86_64 (Homebrew) on Mac. Is this intentional, is there an explanation to this behavior? Also, why not stick with backticks(``) with MySQL identifiers and '' or "" for string literals. Why mix them?

I think this could be a user experience (UX) improvement for MySQL because the current status quo seems confusing for a newbie trying to learn.

What do the SO community think and thanks for your help in advance.

Backticks mean an identifier, e.g. column name (MySQL special). Straight quotes are for a string literal, a constant. — jarlh, Apr 27 '20 at 20:17
see https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql — nbk, Apr 27 '20 at 20:20
Thank you @jarlh for your response. Do you realize I asked this question because single quotes and double quotes can also be used for column aliasing? That's where the confusion came from. Thanks! Don't you think this is an opening for inconsistency? Why not stick to just back ticks for column aliasing and leave '' or "" for string literals? — Derick Alangi, Apr 27 '20 at 20:22

score 3 · Accepted Answer · answered Apr 27 '20 at 20:25

Single quotes (and, in MySQL, double quotes) stand for literal strings. So 'Full name' is just that: a literal string. Using that for sorting makes no sense, since the value is constant for all rows: as a result, the ordering of rows is undefined, meaning that the database is free to return rows in whatever order it likes.

Instead, use backticks, that are used for identifiers, so the order by refers to the expression aliases in the select clause.

Or better yet, use an alias that does not requires quoting, so you don’t have to worry about this all.

score 2 · Answer 2 · answered Apr 27 '20 at 20:24

The second example uses

ORDER BY 'Full Name'

This is a string literal, which is a constant value. It does not refer to the column alias of the same characters.

Any ORDER BY of a constant value results in an arbitrary order, because every row has an equal chance of being ordered before any other row. They are all tied.

Double-quoted strings are also treated as string literals by default in MySQL. This is different from ANSI SQL, where double-quotes are identifier delimiters. MySQL does that if you set sql_mode=ANSI or sql_mode=ANSI_QUOTES.

Why does using '' or "" doesn't sort column aliases via ORDER BY in MySQL?

2 Answers2