Pop quiz answered: “what would be the results of the following queries?”

The quiz presented poses with an uncommon, though valid SQL syntax: one is allowed to use quoted name aliases. Thus, it is valid to write:

SELECT Name AS 'n', Continent AS 'c' FROM countries

But what does the above mean? Let’s see the results of our three questions:

Question #1

SELECT
  id,
  Name AS 'n',
  Continent AS 'c'
FROM
  countries
ORDER BY
  c
;
+----+--------------------------+---------------+
| id | n                        | c             |
+----+--------------------------+---------------+
| 15 | North Korea              | Asia          |
| 12 | Nepal                    | Asia          |
| 11 | Norway                   | Europe        |
| 10 | Netherlands              | Europe        |
|  8 | Nicaragua                | North America |
|  1 | Netherlands Antilles     | North America |
|  3 | Namibia                  | Africa        |
|  5 | Niger                    | Africa        |
|  7 | Nigeria                  | Africa        |
|  9 | Niue                     | Oceania       |
|  4 | New Caledonia            | Oceania       |
|  2 | Northern Mariana Islands | Oceania       |
| 13 | Nauru                    | Oceania       |
| 14 | New Zealand              | Oceania       |
|  6 | Norfolk Island           | Oceania       |
+----+--------------------------+---------------

Result is sorted by continent, as may be expected. We have aliased continent as ‘c’, and ORDER BY c went well. The quotes were meaningless here.

Question #2

SELECT
  MIN(Name) AS 'n',
  Continent AS 'c'
FROM
  countries
GROUP BY
  c
;
+----------------------+---------------+
| n                    | c             |
+----------------------+---------------+
| Nepal                | Asia          |
| Netherlands          | Europe        |
| Netherlands Antilles | North America |
| Namibia              | Africa        |
| Nauru                | Oceania       |
+----------------------+---------------+

As with first query, the quoted ‘c’ acts just like non-quoted c.

Question #3

SELECT
  MIN(Name) AS 'n',
  Continent AS 'c'
FROM
  countries
GROUP BY
  'c'
;
+---------+---------------+
| n       | c             |
+---------+---------------+
| Namibia | North America |
+---------+---------------+

Obviously something went wrong here. The query is valid, yet returns a single row. Why is that?

What we’ve just done is to group by the constant ‘c’, not on the alias c. Results are complete wrong.

This query is a simplified version of a true customer’s query. The GROUP BY clause included some 5 fields, the constant literal being one of them. The results of that query were used in very important business decision making. Well, decisions were misinformed…

Quoted aliases?

I’m not sure why anyone would want these. Why is this even a valid syntax?

Multi-word aliases? Seems like wrong coding convention. But, if you must, do not use single quotes: use `backticks`.