Combining ORDER BY and UNION

Combining ORDER BY and UNION Without a transformation, a statement that contains both ORDER BY and UNION would require two separate sorting steps-one to satisfy ORDER BY and one to satisfy UNION. UNIONavoiding ordering during

Currently uses sorting to eliminate duplicates from a UNION. You can use UNION ALL to avoid sorting, but UNION ALL will return duplicates. So you only use UNION ALL to avoid sorting if you know that there are no duplicate rows in the tables.

In some situations, can transform the statement internally into one that contains only one of these keywords (the ORDER BY is thrown out). The requirements are as follows:

The columns in the ORDER BY list must be a subset of the columns in the select list of the left side of the union.
All the columns in the ORDER BY list must be sorted in ascending order, and they must be an in-order prefix of the columns in the target list of the left side of the UNION.

will be able to transform the following statements:

SELECT miles, meal FROM Flights UNION VALUES (1000, 'D') ORDER BY 1

cannot avoid two sorting nodes in the following statement, because of the order of the columns in the ORDER BY clause:

SELECT flight_id, segment_number FROM Flights UNION SELECT flight_id, segment_number FROM FlightAvailability ORDER BY segment_number , flight_id