It is just not tenable where latency poses a practical constraint under an implementation that is not theoretically sound. There are plenty of hacks to work around it, though, so it is not really a problem in practice. To call those hacks to work around a system with shortcomings "using the database properly" is a bit of a stretch, though.
Just so we're clear, the use of JOIN, IN, etc. are not hacks in and of themselves. They serve a purpose even where latency doesn't exist. But when applied to solving the N+1 problem, their use is a hack. Again, one that gets the job done and solves the problem – hacks aren't bad, but is not theoretically sound. If you did not have latency constraints to contend with, you would not prefer it.
The better question is: Why use a database that sucks? Latency periods are small enough in SQLite, for example, that you don't have to worry about the N+1 problem in any real-world situation. It doesn't have to be a problem in need of hacks.