What's the difference between SQL and NoSQL databases?
Quick Answer
SQL (relational) databases enforce a fixed schema, use tables with rows/columns, relationships via foreign keys, and typically prioritize strong consistency (ACID transactions). NoSQL databases relax one or more of these — flexible/schemaless documents, denormalized data models, and often prioritize availability/partition tolerance and horizontal scalability over strict consistency (per the CAP theorem). Neither is universally "better" — the choice depends on data shape, consistency needs, and scale requirements.
Detailed Answer
Structural differences
| SQL (relational) | NoSQL | |
|---|---|---|
| Schema | Fixed, defined upfront, enforced by the engine | Flexible/dynamic, often enforced (if at all) by the application |
| Data model | Tables, rows, columns, normalized via foreign keys | Documents, key-value pairs, wide columns, or graphs — varies by type |
| Relationships | Joins across normalized tables | Usually denormalized/embedded, or handled application-side |
| Query language | SQL (largely standardized) | Varies per product (MongoDB query language, Cypher for Neo4j, etc.) |
| Consistency model | Typically strong (ACID transactions) | Varies — many default to eventual consistency for scalability |
| Scaling | Traditionally vertical (bigger server); horizontal via sharding is possible but harder to bolt on | Many designed from the ground up for horizontal scaling/sharding |
Why NoSQL emerged
Relational databases enforce a fixed schema and strong consistency, which is exactly right for data with stable structure and strict correctness needs (financial ledgers, inventory), but can be a poor fit for: data whose shape varies significantly between records (product catalogs with wildly different attributes per category), extremely high write throughput distributed across many nodes, or workloads where slightly stale reads are an acceptable tradeoff for lower latency and higher availability at scale.
What you give up with most NoSQL systems
- Joins — most NoSQL databases either don't support them or support them poorly; the data model is usually designed to avoid needing them by embedding related data together (see the embedding vs. referencing question).
- Strict schema enforcement — flexibility cuts both ways: it's easy to evolve the data model, but it's also easy to accumulate inconsistent documents (some missing fields, some using different types for the "same" field) without the database itself catching it.
- Multi-record ACID transactions — historically weaker or entirely absent in early NoSQL systems (though many, like MongoDB since v4.0, have since added multi-document transaction support, narrowing this gap).
The realistic modern answer: polyglot persistence
Most non-trivial systems today use both — a relational database for the core transactional data (orders, accounts, inventory) where consistency and relationships matter most, plus one or more NoSQL stores for specific workloads that fit them better (a document store for a flexible content catalog, a key-value store for session/cache data, a graph database for a recommendation engine). The interview-relevant skill isn't "SQL vs. NoSQL" as an either/or — it's recognizing which data shape and consistency requirement each part of a system actually has, and picking the right tool per use case.