Please help with a valid explanation

[Tommy.net]

Evaluate the following SQL statement:
SELECT 2 col1,'y' col2
FROM dual
UNION
SELECT 1,'x'
FROM dual
UNION
SELECT 3,NULL
FROM dual
ORDER BY 2;
Which statement is true regarding the output of the SQL statement?
A. It would execute and the order of the values in the first column would be 3, 2, 1.
B. It would execute and the order of the values in the first column would be 1, 2, 3.
C. It would not execute because the column alias name has not been used in the ORDER BY clause.
D. It would not execute because the number 2 in the ORDER BY clause would conflict with the value 2 in the
first SELECT statement.

Answer: B

but i cant get the logic

Please help

[jmcilhinney]

It performs three separate queries that return one row each, unions them all together and then sorts by the second column.

[leinad31]

You can indicate columns in ORDER BY clause via their names or position in column list, with some restrictions if both will be used at the same time.

ORDER BY 2 means sort on second column, since you can't use the alias col2; reference doesn't exist in data dictionary. In other words ORDER BY col2 is invalid. The only way to access col2 alias for use in ORDER BY clause would be to return resultset that uses col2 first before doing a sort (intermediate result of inline query treated as a table, hence col2 reference can now be used), as shown in query below.

SELECT *
   FROM ( SELECT 2 col1,'y' col2
                FROM dual
               UNION
              SELECT 1,'x'
                FROM dual
               UNION
              SELECT 3,NULL
                FROM dual
           )
ORDER BY col2;

As to the ascending sort order, alphanum comes before NULL, hence col2 = ('x', 'y', NULL) or col1 = (1, 2, 3)

[Pradeep1210]

You do:

ORDER BY 2;

this means that the data should be sorted by 2nd column.

Your result is:

2    y
1    x
3    NULL

If you sort it on 2nd column you will get:

1    x
2    y
3    NULL

[Tommy.net]

What is the dual table all about?This is still going over my head

if i do this :

Select * from Dual

then i get this:

[Tommy.net]

You do:

this means that the data should be sorted by 2nd column.

Your result is:

2*   y
1    x
3    NULL

If you sort it on 2nd column you will get:

1    x
2    y
3    NULL

even if i dont sort it on 2nd column,my result is:

1    x
2    y
3    NULL

[si_the_geek]

I don't use Oracle myself, but from what I have seen on the forums Dual seems to be a "fake" table so that you can run SQL statements which don't need data from a real table.

[si_the_geek]

even if i dont sort it on 2nd column,my result is:

1    x
2    y
3    NULL

Again I'm not sure about Oracle, but that is presumably due to the use of UNION rather than UNION ALL.

UNION tends to do a DISTINCT and an ORDER BY after adding the records to the result set, whereas UNION ALL just adds the records to the result set.

[leinad31]

UNION has an implicit sort operation. This facilitates identification of similar records since they will end up as adjacent after the sort and before reduction. Implicit sort will sort on the columns as they appear in the column list, namely col1 ascending then col2 ascending. Your results are the same because of your data, the sort order of 1,2,3 is similar to x,y,NULL. try reversing the values as 3,2,1 paired with x,y,NULL

UNION ALL just appends records from succeeding SELECT statements without additional sort+reduce operation, that is why it's performance is better and why you should use it if records from SELECT statements are mutually exclusive (no similar records, or in terms of venn diagram the sets do not intersect).

DUAL is just a system table with one column and one record since some operations need to be done in SQL context (not possible in PL/SQL context). In the case of your query you need to generate an SQL resultset but you are not querying from any particular table you created, or you don't want to go through the trouble of creating a pipeline function that returns records. Another use (in older versions of Oracle) is to retrieve next value from sequence object because it is not possible to do so from PL/SQL context.

SELECT your_sequence_obj.next_value
INTO a_variable_you_declared_or_assigned_directly_to_column_if_used_in_trigger
FROM DUAL

Providing the DUAL table with SYS schema instead of leaving it to users to create their own single row, single column table implementation gives Oracle more control in tuning operations involving the DUAL table. In latter versions, it is made more efficient as it is no longer treated simlar to an ordinary table.