From reading Redmond & Wilson's book "Seven Databases in Seven Weeks", my impression is that the CAP Theorem is a big idea (essentially, you get two choose any two: consistency, availability, and partition-tolerance). Relational databases have to be "consistent", i.e. transactions are atomic and reads always get the latest value. NoSQL databases make it possible to choose Availability instead of Consistency, so you can get faster response times even if it means you sometimes get out-of-date data.

I would use Mongo or Riak or something like those for "write once, then read-only" applications, for example a twitter or facebook type application, or simple blog. In those cases when the user hits "refresh" they're never going to know or care that they don't yet see a comment someone posted a half second ago. They're just going to be happy that the page refreshed fast.