SQL query no order by question

Question

Got into a heated debate (all caps sentences were involved) with a bunch of friends

In SQL,

create table test(
    id int,
    code varchar(10),
    name varchar(30)
)
insert into test values (1,'BE','BENGALOORU')
insert into test values (2,' CH','CHENNAI')
insert into test values (3,' DE','DELHI')
insert into test values (4,'MU','MUMBAI')
select name from test where code in ('BE','CH','DE','MU')
drop table test

What is the result of this query ?

a. BENGALOORU, CHENNAI, DELHI, MUMBAI
b. BENGALOORU, MUMBAI
c. BENGALOORU
d. BENGALOORU, CHENNAI, DELHI 
e. None of above options since there is no order by

Answer 1

mysql> select name from test where code in ('BE','CH','DE','MU');
+------------+
| name       |
+------------+
| BENGALOORU | 
| MUMBAI     | 
+------------+
2 rows in set (0.00 sec)

but in truth, the ordering is arbitrary and depends on the underlying implementation of the db store. tables are not inherently ordered. if you want a specific ordering, use an ORDER BY clause.

Answer 2

The result of the query should be (b) but anyone who argues against (e) is very brave - if there is no 'order by' then the order is undefined, and it is an unordered set.

Sounds like a silly argument to have. If you want an order, specify an 'order by' clause.

edit: Answer (e) is very poorly worded. It should state 'the result is undefined and it not guaranteed to be any of the above'. It is incorrect to state 'none of the above', because it's quite likely to result in (b), even if it's not true that it will 'always' be (b).

Answer 3

The sensible answer is (b).

This is because those two rows do have code in ('BE', 'CH', 'DE','MU'), so those two will return true; thus the rows will be selected. The extra spaces in the codes for Chennai and Delhi mean that it's not an exact match, as the comments below rightly point out.

Technically, you are not guaranteed to have (b) as the answer, as database results are not guaranteed to be in any order unless you specify an "order by". But if you're after the set of results, you'll get (b). If order of results is important to you, then (e) is more "correct".

Answer 4

e. The order of returned results only depends on the order of insertion where there has been no fragmentation.

We saw this in a large database where we got by for years on reports without sorting on the RecirdId (which was am Identity field). For years, the results came back in numerical order without an order by clause. However, one day the reports started coming out in a goofy order.

We added an Order By clause AND created jobs to rebuild the indexes more frequently.

Answer 5

The inserted order will not necessarily be the order in the result set.

The answer COULD be b., but nothing in the SQL specification for SELECT guarantees it (without ORDER BY clause, the order is undefined).

The best answer is e.

Answer 6

I'd edit this into one of the exisiting answers, but alas, not enough reputation ...

I think that everybody agrees on the following:

1. The result set consists of the two values in answer (b) in no particular order.
2. The SQL standard does not dictate any order that these two values are returned from the statement. So (e) is the formally correct answer.
3. There seems to be some evidence that there is logic to the order a database would return the values, either order of insertion or a primary key "index". That means that most of the time, the result returned will be deterministic, including possibly (b).
4. As noted in (2), this is not guaranteed, so if you want an order or rely on it, use an order by clause.

Hope this sums all the answers up.