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)
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 ...]
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.
These are analogous to
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).