There are roughly three kinds of exceptions.
The first is the set of exceptions that could potentially be caused by any basic instruction that are really more of a "the programmer screwed the code up, and our static type system isn't powerful enough to prove it can't happen." Think Java's RuntimeException, the basic processor signals in C/C++ (SIGSEGV, SIGFPE, SIGILL, SIGBUS), or Rust panics. For these exceptions, the standard handling behavior is to catch it at a very high level, log as much details as you can about what caused the exception, fail the task (e.g., return HTTP 500 error), and throw away the detritus. These exceptions should be as invisible to the user as possible, and ideally you want to spend a lot of time at the point the exception is thrown collecting as much information as possible for logging purposes (such as stack traces).
The second class is the exact opposite: you're doing a simple operation that is expected to fail. Such as opening a file (it might not exist) or querying a hashtable (the key might not exist). Errors here are almost always going to be handled immediately by the caller, and so collecting a lot of details about the error is not usually what you want to do. Using a simple error code for return is precisely what you want to do in this situation.
The final class of exceptions is perhaps the most common: you have failures that need to be communicated from point A in the system to point B somewhere distant. My prototypical example is a (recursive-descent) parser: if the lexer sees a token it doesn't understand, you want the entire lexer and parser call stack to propagate the error to the caller of the parse function. Sometimes with these errors, you want or need to attach more contextual information as they propagate: in a SAX-like parser, you might want to explain the path to the token that failed.
For the last error class, error-code handling is clearly too cumbersome for most use cases. The easy propagation of C++/Java-style exception helps, but unifying the underlying implementation with the first implementation may be unnecessarily penalizing speed. And the silent propagation turns out to be a disaster for both user maintainability and compiler optimization. That such an exception can be thrown needs to be a fundamental part of the type of the function. Java introduced checked exceptions to express this type correctly, but the way exceptions integrate with the rest of the type system has turned out to be less than satisfactory.
On top of these exception classes, there are two more important considerations. The first is the general need to recognize chaining of exceptions: you may need to explain the cause of a high-level exception in terms of a specific low-level exception. The second is that you may want to convert between the different classes--turning a file-not-found exception into a hard program crash, for example. (This is especially useful when writing exploratory code!)
In my opinion, none of the languages I'm familiar with have solved all of the issues with exceptions. Exceptions are hard to design right, and since they're so fundamental to how you express code, they're a feature that is baked so early in the process and is difficult or impossible to change as you discover problems with it. I do think that Rust's Result-versus-panic, with the ? syntax for Result propagation (as noted by several sibling comments) is a good starting point, but my feeling is that more work needs to be done on easing the burden of specifying exception types when you're plumbing together many stages and libraries.