There are multiple ways of let's say instanciate a list of contacts using SOQL ; My question is : is there a difference between each other, and is there a best practise ?


String name = '%a%'; // vous pouvez changer cette variable, ici on cherche tous les noms contenant un a

// 1- instancier une new List
List<Account> accList = new List<Account>([SELECT Id FROM Account WHERE Name LIKE :name]);

// 2 - interroger Database.query(), le paramètre est alors une String
List<Account> accList2 = Database.query('SELECT Id FROM Account WHERE Name LIKE :name');

// 3 - simplement la requête
List<Account> accList3 = [SELECT Id FROM Account WHERE Name LIKE :name];

EDIT : Also, what differenciate the '=' from the LIKE operator ? If accList.size() is let's say 10, replacing the LIKE by a = does not seem to work, When to use the =, that seems to be not so powerfull (not accepting _ nor % ?)


Nb : not sure my code is well presented (used CTRL + K, though)


sObject[] records = new List<sObject>([SELECT ...])

"Waste of Resources"

This should never be used. While not incorrect, you're basically copying the list in to a new list, then discarding the old list. As a real-life example, that'd be like going to the store, buying stuff, putting the items in to your car, driving home, taking all the stuff out of one car and putting it in to another car, then unloading that car in to your house. No normal person would do this, nor would any competent developer use the this technique. It's inefficient and a waste of code.

sObject[] records = Database.query(string)

"Dynamic Query"

There are times to use this technique. Namely, if you don't know the list of fields beforehand, or the conditions can change. You should not use this method unless you need to. The main reason I cite is that doing this prevents the compiler from flagging the fields/objects used in the query, as the compiler cannot assert the value of the string at compile-time.

This typically matters when you go to find out where a field/object is used, or when you go about deleting/renaming fields. In the inline-query syntax ([select ...]), the system flags those fields and objects as "in use", and will actively prevent you from renaming those fields and objects. This is generally a good thing, because without this protection, you could rename a field and cause your code to fail, and not even unit tests can save you from this sort of blunder.

This technique is useful in some rare circumstances, but should generally be avoided unless you identify a clear need to use the benefits that Database.query offers, such as dynamically selecting fields or creating dynamic where clauses (the two most common use cases). If you can identify the fields, objects, and criteria beforehand, it's much better to use an inline-query instead.

sObject[] records = [SELECT ...]

"Inline Query"

This is the preferred form of querying. Use it by default, unless you have a clear need to use Database.query. This method protects you from accidentally renaming/deleting the referenced fields, and allows you to identify which classes use a specific field/object.

= Versus LIKE

These are analogous to = and LIKE in SQL. The = operator finds an exact (case-insensitive) match, which is great if you want to find an account named Bob's_Fun_House (for whatever reason that account name might match). There's a minor performance benefit by using =, as the database doesn't need to worry about wildcards, and there's a minor developer benefit, as they don't have to worry about escaping wildcard characters. You should prefer = when possible, and use LIKE only if you know that wildcards may be used (typically because a user needs a wildcard query).

| improve this answer | |
  • Well, it was worth asking !! I always instantiated a new List() ... Very bad habbit, thanks for all those usefull explanations !! – Alexis MASSON Oct 22 '19 at 9:58

Using LIKE instead of = will make a comparison and use wildcards(%) in the search. So in your case, a query

SELECT Id FROM Account WHERE Name LIKE :name

Would transform to:

SELECT Id FROM Account WHERE Name LIKE '%a%'

Resulting in returning all accounts that have 'a' in their name. If you use = instead, the query will try to find an Account whose name is exactly '%a%'.

I do not have a good answer for which way to use to instantiate a new list, but it seems that most people almost always use the third option:

List<Account> accList3 = [SELECT Id FROM Account WHERE Name LIKE :name];


Take a look at What's the difference between Database.query vs [query]?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.