What is the difference between using the LIMIT clause and using a fixed index to retrieve data from a query?

Question

This code recover the exact same account, I thought there could be differences with the way it ask the database ... Seems not to be the way it works, So an idea ?

public static void TestSOQLLimitOrArrayIndex () {
    List<Account> L1 = DataBase.query('SELECT Id, Name, Type, ParentId, Fax, Phone FROM Account LIMIT 1');
    System.debug('Using LIMIT 1 --- Account : ' + a);
    // avec [0]
    Account a = [SELECT Id, Name, Type, ParentId, Fax, Phone FROM Account][0];
    System.debug('Using [0] --- Account : ' + a);
}

Answer 1

actually, there is a difference, in current example it impacts on total number of records retrieved by SOQL queries limits. Limits is one of the most important challenge working with salesforce platform. try to use them so less, as possible.

---

E.g. you have 50000 Accounts and you need to query only one, so that query [SELECT Id, Name, Type, ParentId, Fax, Phone FROM Account][0] will return all 50000 records, but you will get only one, that you need. In this case, not necessary limit will be hit. So if you know all restrictions to required record - apply them. In this case we can set, that only one record will be returned SELECT Id, Name, Type, ParentId, Fax, Phone FROM Account LIMIT 1. In such way only one record will be counted in total number of records retrieved by SOQL queries apex limit.

---

also there is difference between assigning SOQL result to List of records, or to Sobject

e.g. if no Accounts exists

List<Account> accts = [
    select Id
    from Account
    limit 1
    ];
// no exception is thrown. accts.isEmpty() returns true

Account acct = [
    select Id
    from Account
    limit 1
    ];
// exception is thrown System.QueryException: List has no rows for assignment to SObject

Answer 2

I'll just add to Alexander's answer.

As per the documentation for ORDER BY here

There is no guarantee of the order of results unless you use an ORDER BY clause in a query

So if you don't use ORDER BY, there can be a possibility you can get differently ordered of records each time you execute the query.

An ORDER BY clause would define the order in the SOQL rows returned. In that case, the first row returned by using LIMIT clause or just fetching the first record[0] will give the same record.