Tips&Tricks: Oracle Union all query order of records
Posted by Jai on October 25, 2012
The union all query allows to combine the result of multiple select queries. The tip is how it behaves differently in MySQL and Oracle . How you can control the order of records from each select query.
The expected ordering, the default behavior is the order of results,
- The insert order of records
- Results from first select first and so on
For mysql, it works well. The records are returned in expected order (may be we just got lucky 🙂 )
For oracle, there is no specific order of results, it is random (may be oracle doing extra internal optimization 🙂 ) .
To control the order of records returned – Union all ordering recording, check Example
select field1, field2, . field_n from tables UNION ALL select field1, field2, . field_n from tables; ORDER BY 1;
The results in each select query will be ordered by first column.