Row order differences in result tables

The rows of multiple result tables for the same SELECT statement might not be displayed in the same order. The database manager does not consider the order of rows as significant unless the SELECT statement uses ORDER BY. Thus, if there are several rows with the same DEPTNO value, the second SELECT statement can retrieve them in a different order from the first. The only guarantee is that they will all be in order by department number, as demanded by the clause ORDER BY DEPTNO.

The difference in ordering can occur even if you were to issue the same SQL statement, with the same host variables, a second time. For example, the statistics in the catalog can be updated between executions, or indexes can be created or dropped. You can then issue the SELECT statement again.

The ordering is more likely to change if the second SELECT has a predicate that the first did not have; the database manager can choose to use an index on the new predicate. For example, it can choose an index on LOCATION for the first statement in the example, and an index on DEPTNO for the second. Because rows are fetched in order by the index key, the second order need not be the same as the first.

Again, executing two similar SELECT statements can produce a different ordering of rows, even if no statistics change and no indexes are created or dropped. In the example, if there are many different values of LOCATION, the database manager can choose an index on LOCATION for both statements. Yet changing the value of DEPTNO in the second statement to the following example can cause the database manager to choose an index on DEPTNO:

     SELECT * FROM DEPARTMENT 
       WHERE LOCATION = 'CALIFORNIA' 
       AND DEPTNO >= 'Z98' 
       ORDER BY DEPTNO

Because of the subtle relationships between the form of an SQL statement and the values in this statement, never assume that two different SQL statements will return rows in the same order unless the order is uniquely determined by an ORDER BY clause.