CODEWITHSUNDEEP
mysqlsqldatabaseselectsql-order-by
Derick Alangi
Derick Alangi

Reputation: 1100

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

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;

  1. 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`;

enter image description here

  1. This doesn't sort the result set. Note we used '' quotes
SELECT
    CONCAT_WS(', ', lastName, firstname) 'Full name'
FROM
    employees
ORDER BY
    'Full name';

enter image description here

  1. This doesn't sort the result set. Note we used "" quotes
SELECT
    CONCAT_WS(', ', lastName, firstname) "Full name"
FROM
    employees
ORDER BY
    "Full name";

enter image description here

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.

Upvotes: 2

Views: 610

Answers (2)

GMB
GMB

Reputation: 222402

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.

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562250

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.

Upvotes: 2

Related Questions