Understanding of CASE with ORDER BY in SQL

Question

I am trying to revise my SQL concepts, and while doing so, I faced a curious case which I was not able to understand. Below is my schema and data:

CREATE TABLE Customer 
(
    name varchar(255),
    city varchar(255),
    country varchar(255),
    id int
);

INSERT INTO customer VALUES ("ram923", null, "aIndia3",1);
INSERT INTO customer VALUES ("ram92", null, "dIndia3",1);
INSERT INTO customer VALUES ("ram83", null, "dIndia4",1);
INSERT INTO customer VALUES ("ram94", null, "dIndia4",1);
INSERT INTO customer VALUES ("ram", "city1", "bIndia1",1);
INSERT INTO customer VALUES ("ram1", "city2", "aIndia1",1);
INSERT INTO customer VALUES ("ram2", "city3", "aIndia1",1);
INSERT INTO customer VALUES ("ram3", "city4", "bIndia2",1);
INSERT INTO customer VALUES ("ram4", "city5", "bIndia2",1);
INSERT INTO customer VALUES ("ram8", null, "bIndia2",1);
INSERT INTO customer VALUES ("ram9", null, "bIndia2",1);
INSERT INTO customer VALUES ("ram5", "city6", "cIndia3",1);
INSERT INTO customer VALUES ("ram6", "city7", "dIndia4",1);
INSERT INTO customer VALUES ("ram67", "city71", "dIndia3",1);
INSERT INTO customer VALUES ("ram622", null, "eIndia3",1);
INSERT INTO customer VALUES ("ram81", null, "cIndia3",1);

Then I execute this select query which would use CASE statement with ORDER BY to order the customers by City. However, if City is NULL, then order by Country:

SELECT 
    name, City, Country
FROM
    Customer
ORDER BY
    (CASE
        WHEN City IS NULL THEN Country
        ELSE City
     END);
   

The output I get is not quite the way I thought it would be:

name    City    Country
------------------------
ram923  (null)  aIndia3
ram8    (null)  bIndia2
ram9    (null)  bIndia2
ram81   (null)  cIndia3
ram     city1   bIndia1
ram1    city2   aIndia1
ram2    city3   aIndia1
ram3    city4   bIndia2
ram4    city5   bIndia2
ram5    city6   cIndia3
ram6    city7   dIndia4
ram67   city71  dIndia3
ram92   (null)  dIndia3
ram83   (null)  dIndia4
ram94   (null)  dIndia4
ram622  (null)  eIndia3

Can anyone explain how the CASE with ORDER BY is working here? Why it would not put all null cities together and then do ORDER BY country?

Answer 1

What is hard to understand? You can see the sorting key by replacing the expression, although I would simplify it as:

select c.*, coalesce(city, country)
from customers c
order by coalesce(city, country);

In your sample data, all the country values are before the city values alphabetically.