sql - Different query results using * and column names

Question

When using * and column names differently, the query result is different in both the cases. There is a possibility that only the order is different but why is that as we haven't used any command to sort the data.

SELECT * FROM employees;

But when I query, the first_name, last_name column:

SELECT first_name,last_name FROM employees;

Lastly when I add a column that has an index on it,

the result is different again

SELECT employee_id, first_name,last_name FROM employees;

Why is the result is different?

I couldn't understand how first_name, last_name column values are ordered. Thanks and regards.

question from:https://stackoverflow.com/questions/66058547/different-query-results-using-and-column-names

Answer 1

The data is the same, just in a different order.

What I will add is that it looks like you have an index on (last_name, first_name). What is happening in your case is that Oracle recognizes that it can use this index for the query -- it is a covering index.

That is why the results with just those two column names look like they are ordered. Of course, you cannot depend on this. Results sets are unordered unless an order by clause is explicitly included in the SELECT.