I'd add "level of abstraction" to this list (ie., people say no because SQL provides the wrong level of abstraction for a problem). You know you're getting old when when the "high level" languages of your youth are now viewed as "low-level" building blocks, but this has clearly happened in the SQL world. I initially resisted hibernate, JPA, active record, and so forth, probably for the same reason some C/C++ programmers resisted Java's memory management - it felt strange to give up this control. Now that I've gotten used to these tools, I'd never want to go back to hand-written SQL for basic object persistence and relational mapping, and I'm more than happy to use JPA or ActiveRecord to automate my CRUD. In this sense, I'd say "NoSql" for the same reason I'd rather have garbage collection than malloc or the pointer asterisk.

But I do see huge and fundamental memory mistakes made by people who use ORM abstractions without understanding SQL (such as selecting all into an arraylist and filtering through code rather than using the where clause, or generating a list and using each element to generate a new list, rather than using a JOIN).

It could be that some of these folks never encounter a data set large enough or a relation complex enough to need SQL, or maybe SQL is actually not necessary to manage relations in huge, intertwined data sets, or maybe you can avoid huge, intertwined data sets through careful planning. But for me, this is where the "NoSQL" thing breaks down, and I definitely haven't heard a whole lot of discussion about it.