A lot of the difference between SQL and NoSQL that is talked about focuses on the fact that SQL support ACID properties while in many NoSQL dbs ACID is compromised.
I'm unsure of the reason but scalability seems to be the culprit from what I've read(do correct me here). A lot of discussion wrongly associate the consistency in CAP with the consistency in ACID. They say that NoSQL doesn't provide ACID but it provides BASE (Basically Available, Soft state, Eventual consistency). E stands for eventual consistency. But SQL dbs too can provide eventual consistency(master slave architecture - MySQL by default performs asynchronous replication).
Apart from the fact that SQL have a rigid schema and NoSQL have a flexible schema. What other difference do they have? Is there difference on the scalability part? I know NoSQL is an umbrella term but I'm mentioning about most of the NoSQL dbs here.
If both SQL and NoSQL(I found some NoSQL dbs provide ACID) can scale then why can't some NoSQL db provide ACID? And when it says that some NoSQLs(pointing to the dbs that don't provide ACID properties) can't provide ACID, does it mean that a transaction can make a database table inconsistent? This inconsistency is wrongly interpreted to be the CAP inconsistency when in fact it actually means the ACID inconsistency. Can concurrent transaction make a NoSQL table into inconsistent state - for eg - is it possible that dirty reads can be performed by some transaction that is running concurrently with another parallel transaction?
One of them that seems to mix the ACID and CAP consistency: (I'll add a few if I find more)
