Strange behavior in SQL on the left

Question

I am posting this question in response to an answer I recently wrote on this question . The question consists of two tables:

Base:
ID  Field
A   1
B   2
D   NULL

Join:
ID
A
B
C
D

And here is the desired result:

Output:
ID  Newfield
A   1
B   2
C   NULL
D   None

In other words, we want to distinguish between missing fields due to missing values ​​in Base, and those fields are missing because they don’t even have IDone appearing Base. Nonemeans that was IDpresent in Basewith the field NULL, and NULLmeans that we did not even see IDin Base. I thought the following query (adapted to ANSI SQL) would work:

SELECT t1.ID, t2.Newfield, t2.ID
FROM `Join` t1
LEFT JOIN
(
    SELECT ID, COALESCE(Field, 'None') AS Newfield, Field
    FROM Base
) t2
    ON t1.ID = t2.ID
ORDER BY t1.ID;

, , C D None . , D None, C NULL.

- ? , ORDER BY /, , .

Answer 1

-.

SELECT t1.ID, t2.Newfield
FROM `Join` t1
LEFT JOIN
(
    SELECT ID, IFNULL(Field, 'None') AS Newfield, Field
    FROM Base
) t2
    ON t1.ID = t2.ID
ORDER BY t1.ID;

SQL Fiddle , .

Rex Tester .