Should a retrieval method return 'null' or throw an exception when it can't produce the return value?

If you are always expecting to find a value then throw the exception if it is missing. The exception would mean that there was a problem.

If the value can be missing or present and both are valid for the application logic then return a null.

More important: What do you do other places in the code? Consistency is important.

Exceptions are related to Design by Contract.

The interface of an objects is actually a contract between two objects, the caller must meet the contract or else the receiver may just fail with an exception. There are two possible contracts

1) all input the method is valid, in which case you must return null when the object is not found.

2) only some input is valid, ie that which results in a found object. In which case you MUST offer a second method that allows the caller to determine if its input will be correct. For example

is_present(key)
find(key) throws Exception

IF and ONLY IF you provide both methods of the 2nd contract, you are allowed to throw an exception is nothing is found!

In some functions I add a parameter:

..., bool verify = true)

True means throw, false means return some error return value. This way, whoever uses this function has both options. The default should be true, for the benefit of those who forget about error handling.

Don't think anyone mentioned the overhead in exception handling - takes additional resources to load up and process the exception so unless its a true app killing or process stopping event (going forward would cause more harm than good) I would opt for passing back a value the calling environment could interpret as it sees fit.

In data layer code, I some times use the following code, allowing the caller to decide if "object not found" means an error has occured.

DataType GetObject(DBConnection conn, string id, bool throwOnNotFound) {
    DataType retval = ... // find object in database
    if (retval != null || ! throwOnNotFound) {
        return retval;
    } else {
        throw new NoRowsFoundException("DataType object with id {id} not found in database");
    }
}

DataType GetObject(DBConnection conn, string id) {
    return GetObject(conn, id, true);
}