As an example of the difference, dotenv is useful for running programs inside Docker containers — which do not inherit your interactive shell's environment variables — whereas direnv isn't particularly useful there. Ditto for programs run via init systems like systemd or even classic SysV init. On the other hand, direnv is convenient for end-user env var config, since it's aware of your shell's working directory and updates the env vars based on it without needing to run extra commands.
From your description, direnv is implicit and noisy, whereas dotenv seems to be (unless you embed it in a script) explicit and quiet.
My assumption overall in this is that most people have just one .env per project (or perhaps in sub-folders per environment, e.g prod, staging, local), but these don't nest. With nested .env files, the mental overhead they bring remove (IMO) most of the benefits, if not more.