The statement provided uses the UNION operator, which combines the results of two or more queries into a single result set. However, UNION also eliminates duplicate rows. Both queries in the union are selecting the same values 1 and ' John', thus the result is one row because duplicates will be removed.

SELECT 1 AS id, ' John' AS first name FROM DUAL UNION SELECT 1 , ' John' AS name FROM DUAL ORDER BY 1;

Given that both SELECT statements are actually identical in the values they produce, despite the differing column aliases, the UNION will eliminate one of the duplicate rows, resulting in a single row being returned.

References:

Oracle Documentation on UNION: SQL Language Reference - UNION