SQL vs NoSQL: When to Use Each

Reviewed & published by Brayan K

"NoSQL" is not one thing, and it is not a replacement for SQL. By the end of this lesson you'll understand the four main NoSQL families — document, key-value, wide-column, and graph — how ACID and BASE differ, what the CAP theorem really forces you to choose, and how to pick the right store for a job. The honest headline: a relational database is still the right default for most applications.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• The relational model vs the four NoSQL families
• ACID transactions vs the BASE philosophy
• What the CAP theorem actually forces you to trade
• Schema-on-write vs schema-on-read
• Normalization vs denormalization for access patterns
• Horizontal scaling, sharding, and polyglot persistence

Our Running Example: users & orders

We model the same small dataset — a few users who placed a few orders — in every storage style, so you can see how each one shapes the same information differently.

1. The Relational Baseline

A relational database (PostgreSQL, MySQL, SQL Server, SQLite, Oracle) stores data in tables of typed columns and rows, with explicit relationships expressed by foreign keys. The structure is fixed up front — that's schema-on-write — and the engine guarantees ACID transactions. This is the model the rest of this course teaches, and it is the right starting point for the overwhelming majority of applications.

A relational database is a well-organized filing cabinet: every drawer (table) holds the same kind of form (rows with identical fields), and cross-references between drawers (foreign keys) are checked so nothing dangles. You can pull and recombine forms in ways nobody planned — that's the join.

-- The relational model you already know: structured tables.
-- A "users" table with typed columns, plus a related "orders"
-- table linked by a foreign key. Joins reassemble the picture.
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
    id       INTEGER PRIMARY KEY,
    user_id  INTEGER REFERENCES users(id),  -- the relationship
    total    NUMERIC NOT NULL
);

-- Every order belongs to exactly one user; the schema enforces it.
-- This is schema-on-write: a bad shape is rejected at INSERT time.

-- The relational superpower: ad-hoc joins across normalized tables.
-- "Each user with their total spend" — assembled at query time.
SELECT u.name,
       COUNT(o.id)       AS order_count,
       SUM(o.total)      AS lifetime_value
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name
ORDER BY lifetime_value DESC;

-- You did NOT have to anticipate this query when designing the
-- schema. Normalization + joins let you ask new questions freely.
-- That flexibility is exactly what most NoSQL stores trade away.

-- ✅ Expected result:
-- name | order_count | lifetime_value

2. Document Stores — MongoDB

A document database stores semi-structured documents (JSON/BSON-like) grouped into collections. Related data is often embedded in one document, so reading an entity is a single lookup with no join. Documents in a collection need not share a shape — that's schema-on-read. MongoDB is the canonical example.

Content management, product catalogs, user profiles, event logs — anywhere the data is naturally hierarchical and you usually fetch a whole entity at once. Less ideal when you need many-to-many joins or transactions spanning lots of entities.

-- DOCUMENT model (e.g. MongoDB). Illustrative — NOT runnable here.
-- The same user, but their orders are EMBEDDED in one document.
-- A "collection" of documents replaces the table; each doc is JSON-like.

-- db.users.insertOne({
--   _id: 1,
--   name: "Ada",
--   email: "ada@example.com",
--   orders: [                      // embedded, denormalized
--     { id: 101, total: 49.99 },
--     { id: 102, total: 19.50 }
--   ]
-- })

-- Reading one user returns everything in a single lookup — no join.
-- Trade-off: if an order's shape must change, you touch many docs,
-- and there is no engine-enforced link between users and orders.

3. Key-Value Stores — Redis

A key-value store is the simplest model: unique keys mapped to values, fetched by key. Redis keeps data primarily in memory, making reads and writes extremely fast. There are no joins and no querying by value — you must know the key.

Caching, session storage, rate limiting, real-time counters, and leaderboards. Often sits alongside a relational database as a fast cache, not as the system of record.

-- KEY-VALUE model (e.g. Redis). Illustrative — NOT runnable here.
-- Just unique keys mapped to values. Blisteringly fast, very simple.

-- SET user:1:name      "Ada"
-- SET user:1:email     "ada@example.com"
-- INCR user:1:visits                 -- atomic counter
-- EXPIRE session:abc123  3600        -- auto-expiring session

-- GET user:1:name   ->   "Ada"

-- There are no queries "by value" and no joins: you fetch by key.
-- Perfect for caching, sessions, rate limits, leaderboards.
-- Wrong tool the moment you need to search or relate data.

4. Wide-Column Stores — Cassandra

A wide-column store organizes data into rows that share a partition key but can hold different columns. Apache Cassandra distributes those partitions across many nodes, giving enormous write throughput and linear horizontal scaling. The catch: you design tables around the queries you need, because efficient reads must hit the partition key.

Unlike relational normalization, here you denormalize and duplicate data into one table per access pattern. Flexible ad-hoc querying is gone; in return you get write scale that relational single-node setups can't match.

-- WIDE-COLUMN model (e.g. Cassandra). Illustrative — NOT runnable here.
-- Rows keyed by a partition key; columns can vary row to row.
-- You design the table AROUND the query you must serve.

-- CREATE TABLE orders_by_user (
--   user_id   int,
--   order_id  int,
--   total     decimal,
--   PRIMARY KEY (user_id, order_id)   -- partition by user_id
-- );

-- SELECT * FROM orders_by_user WHERE user_id = 1;   -- fast, single partition

-- Scales horizontally to huge write volumes across many nodes.
-- Cost: queries not matching the partition key are slow or disallowed.

5. Graph Databases — Neo4j

A graph database models data as nodes connected by typed edges, where relationships are first-class citizens. Neo4j (queried with Cypher) makes multi-hop traversals — friends-of-friends, shortest paths, recommendation chains — fast and natural, whereas the same questions become deeply nested self-joins in SQL.

Social networks, recommendation engines, fraud-ring detection, knowledge graphs, and network/IT topology — any domain where the connections matter as much as the entities.

-- GRAPH model (e.g. Neo4j, Cypher syntax). Illustrative — NOT runnable here.
-- Data is NODES connected by typed EDGES. Relationships are first-class.

-- (Ada:User)-[:PLACED]->(o1:Order {total: 49.99})
-- (Ada:User)-[:FOLLOWS]->(Grace:User)

-- "Friends-of-friends who bought product X":
-- MATCH (me:User)-[:FOLLOWS*2]->(fof)-[:PLACED]->(:Order)-[:CONTAINS]->(p:Product {name:'X'})
-- RETURN DISTINCT fof.name;

-- Multi-hop traversals like this are awkward joins in SQL but
-- natural here. Shine for social graphs, recommendations, fraud rings.

6. ACID vs BASE, and the CAP Theorem

ACID (Atomicity, Consistency, Isolation, Durability) is the strong guarantee relational transactions give: changes are all-or-nothing, valid, isolated from each other, and durable once committed. Many distributed NoSQL systems instead lean on BASE (Basically Available, Soft state, Eventual consistency): they stay available and converge to a consistent state over time, accepting brief inconsistency for scale.

The CAP theorem explains why. In a distributed system you cannot, during a network partition (P), have both perfect consistency (C) and full availability (A) — you must sacrifice one. A CP system (like a strongly-consistent config store) refuses some requests to stay correct; an AP system (like Cassandra in its default tuning) keeps answering and reconciles later. It is a spectrum and a tunable choice, not a rigid label.

7. Schema, Normalization & Horizontal Scaling

Schema-on-write (relational) validates structure when you insert, catching bad data early and keeping queries predictable. Schema-on-read (many NoSQL) accepts whatever you write and applies structure when reading, which is flexible but pushes the burden of consistency onto the application.

Normalization splits data into related tables to remove redundancy — ideal when you query flexibly and want a single source of truth. Denormalization deliberately duplicates data to serve a known read pattern fast, which is the default mindset in document and wide-column stores. Neither is "better"; they optimize for different things.

Relational databases traditionally scale vertically (a bigger machine) and scale reads with replicas; pushing writes beyond one node means sharding, which adds real complexity. Many NoSQL systems were built to scale horizontally from the start, spreading data across commodity nodes via automatic sharding — that built-in write-scaling is often their main draw.

Your Turn: the join NoSQL would denormalize away

Fill in the blanks to count each user's orders with a join. This is precisely the flexible query a document store trades away by embedding — and the relational model hands it to you for free.

-- 🎯 YOUR TURN — fill in the blanks, then press "Try it Yourself"
-- Goal: list each user with how many orders they placed.
-- (This is the kind of ad-hoc join relational DBs make easy.)

SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.___    -- 👉 the column orders points back to
GROUP BY u.___                             -- 👉 group so it is one row per user
ORDER BY order_count DESC;

-- ✅ Expected: every user with a count of their orders, busiest first.

Common Misconceptions (and the reality)

• "NoSQL is faster, so use it everywhere": it's faster for specific access patterns. For varied, relational queries a tuned relational database usually wins — and gives you transactions for free.
• "NoSQL means no schema": it means schema-on-read. The structure still exists — it just lives in your application code now, where it's easier to get inconsistent.
• "NoSQL can't do transactions": outdated. MongoDB has multi-document ACID transactions; many stores offer tunable consistency. But cross-entity guarantees are still generally weaker than a relational database.
• "You must pick one database": not so. Polyglot persistence uses the right store per job — e.g. PostgreSQL as the system of record, Redis for caching, Elasticsearch for search.
• "Relational doesn't scale": it scales to very large workloads with replicas, partitioning, and sharding; it just takes more effort to scale writes horizontally than systems designed for it from day one.

Frequently Asked Questions

Q: What should my default database be?

A relational database (PostgreSQL is an excellent default). It gives you ACID transactions, ad-hoc querying with joins, mature tooling, and the flexibility to answer questions you didn't foresee. Reach for NoSQL when a concrete requirement — scale, shape, or access pattern — justifies it.

Q: What is polyglot persistence?

Using multiple database types in one system, each for what it does best: the relational store as the source of truth, a key-value cache for hot reads, a document store for flexible content, a graph for relationships, and a search engine for full-text. Real systems are often a mix.

Q: Does NoSQL really have no joins?

Most NoSQL stores either lack joins or make them expensive, so you either embed/denormalize the related data or join in application code. Graph databases are the exception: traversing relationships is exactly what they optimize for.

Q: ACID or BASE — which do I need?

If correctness of every transaction matters (payments, inventory, accounting), favor ACID and a relational database. If availability and scale matter more and brief inconsistency is tolerable (activity feeds, view counts), BASE-style eventual consistency can be the right trade.

🎉 Lesson Complete

• ✅ "NoSQL" spans four families: document, key-value, wide-column, and graph
• ✅ Relational stays the right default — ACID, joins, and flexible querying
• ✅ ACID trades scale for strong guarantees; BASE trades the reverse
• ✅ The CAP theorem forces a C-vs-A choice only during a partition
• ✅ Schema-on-read and denormalization buy flexibility and scale at the cost of consistency you must enforce yourself
• ✅ Polyglot persistence mixes stores so each does what it's best at
• ✅ Next: streaming and real-time SQL over unbounded data

Practice quiz

Continue this course

• Previous: Using SQL with Big Data Tools (Hive, SparkSQL, DuckDB, Presto)
• Next: Streaming & Real-Time SQL — Continuous queries, windowing and watermarks with Flink SQL, ksqlDB and Materialize
• Quick reference: SQL cheat sheet

Related lessons

• JOINs
• normalization