Skip to content
Tutorials Center
My learning

Topics

Lesson 103 of 125 ยท Database Systems

SQL vs NoSQL

Compare relational SQL databases with document, key-value, wide-column and graph stores. Learn the real trade-offs and how SQL's JSON features bridge both worlds.

Everything in this course so far has been relational: data lives in tables with a fixed set of columns, tables point at each other with keys, and SQL combines them with joins. But you'll also hear about NoSQL databases such as MongoDB, Redis, Cassandra, DynamoDB and Neo4j. They store data as documents, key-value pairs, wide rows or graphs, and many of them don't use SQL at all.

"NoSQL" isn't one technology. It's a loose label, coined around 2009, for databases that gave up some relational features (fixed schemas, joins, multi-row transactions) to get something else in return: flexible documents, extreme write throughput, simple horizontal scaling or fast graph traversals. This lesson explains the four main families, what each is good and bad at, and how to choose. You'll also see how modern SQL databases absorbed many NoSQL ideas, especially JSON columns, so the choice is less either-or than it used to be.

The relational model in one paragraph

A relational database stores facts in tables with typed columns, uses primary and foreign keys to connect them, and enforces rules (NOT NULL, UNIQUE, CHECK, foreign keys) on every write. You describe what you want with SQL, and the query planner works out how to get it, using indexes and joins. Transactions make groups of changes all-or-nothing (the ACID guarantees from Transactions). Because data is normalized, each fact is stored once, and you can ask questions nobody anticipated when the schema was designed: "revenue per country per month for customers who signed up in 2024" is just another query.

The price is that the schema must be designed up front, changes need migrations, and spreading one database across many machines is hard, because joins and transactions want all related data in one place.

Document databases

A document database stores self-contained records, usually JSON (or a binary form of it, like MongoDB's BSON). Each document can have its own shape, and related data is often embedded inside it instead of split into separate tables. Examples: MongoDB, Couchbase, Firestore, Amazon DocumentDB.

Think about a shop order. In SQL, it's spread over four tables: orders, customers, order_items and products. A document store would usually keep the whole order as one document. You can build exactly that document from the shop's tables, which shows what "embedding" means:

SQL One order as a document ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The result is one JSON value: order 1 for Rania Quist from Berlin, with an items array holding the Camping Hammock, three Studio Headphones and a Yoga Mat. A document database would store and return that in one read, with no joins. That's the core trade-off:

  • Reads that match the document are fast and simple. Showing an order page is one lookup.
  • Data is duplicated. The customer's name and each product's name are copied into every order. If a product is renamed, old orders keep the old name, which may be exactly right for an order (it's a historical record) but wrong for, say, a shopping cart.
  • Questions across documents are harder. "Total quantity sold per product" means reaching into every order's array. Document databases answer it with aggregation pipelines, which work, but are less flexible than SQL joins.

In MongoDB, the query language is JSON-shaped instead of SQL (read-only):

JavaScript
// MongoDB shell: orders from Berlin customers that contain a Yoga Mat
db.orders.find(
  { "customer.city": "Berlin", "items.product": "Yoga Mat" },
  { order_id: 1, status: 1 }
)

// Total quantity sold per product (aggregation pipeline)
db.orders.aggregate([
  { $unwind: "$items" },
  { $group: { _id: "$items.product", sold: { $sum: "$items.quantity" } } },
  { $sort: { sold: -1 } }
])

"Schemaless" is a slight myth. The schema still exists; it just moves from the database into your application code, which must cope with every shape a document has ever had. Most mature document-database teams add validation rules (MongoDB supports JSON Schema validation) for that reason.

Key-value stores

A key-value store is the simplest model: a giant dictionary. You GET and SET a value by its key, and the store doesn't care what the value contains. Examples: Redis and Valkey (in memory), Memcached, Amazon DynamoDB (which is key-value and document), etcd.

Terminal
# Redis (read-only): cache a rendered page for 300 seconds, count page views
SET page:/sql/joins "<html>...</html>" EX 300
GET page:/sql/joins
INCR views:/sql/joins

Key-value stores are extremely fast (Redis answers in well under a millisecond), easy to scale by splitting keys across servers, and perfect for caches, sessions, rate limiters, counters, queues and feature flags. The limitation is just as clear: you can only look things up by key. "All sessions of user 42" or "keys whose value contains X" either needs extra data structures you maintain yourself or isn't possible.

A relational table with a primary key can play the same role, and for modest traffic it often does (Laravel's database cache and session drivers work exactly like this):

SQL A key-value table in SQL

Press Run (or Ctrl + Enter) to see the result here.

The final query returns views:/sql/joins with the value 2 and user:42:theme with dark. Every lookup is a primary-key search, so it stays fast as the table grows. What a dedicated store like Redis adds is speed from keeping everything in memory, automatic expiry, and data structures (lists, sets, sorted sets, streams) built for these jobs. See Upserts with ON CONFLICT for the INSERT ... ON CONFLICT pattern.

Wide-column stores

A wide-column store (also called a column-family store) organizes data by a partition key that decides which server holds the data, and a clustering key that sorts rows inside each partition. Examples: Apache Cassandra, ScyllaDB, Google Bigtable, HBase.

They are built for huge write volumes spread across many machines, with no single point of failure: sensor readings, message histories, activity feeds, event logs. Cassandra's query language, CQL, deliberately looks like SQL, but the rules are very different:

SQL
-- Cassandra CQL (read-only)
CREATE TABLE readings_by_sensor (
  sensor_id  text,
  day        date,
  reading_at timestamp,
  value      double,
  PRIMARY KEY ((sensor_id, day), reading_at)   -- partition key, then clustering key
) WITH CLUSTERING ORDER BY (reading_at DESC);

-- Fast: one partition, already sorted
SELECT reading_at, value
FROM readings_by_sensor
WHERE sensor_id = 'boiler-7' AND day = '2026-10-17'
LIMIT 100;

-- Rejected: no partition key, so it would have to ask every server
-- SELECT * FROM readings_by_sensor WHERE value > 90;

There are no joins, and you can only filter efficiently by the keys you designed. So you design tables around queries: if you also need "all readings above 90 per day", you create a second table organized for that question and write each reading to both. This is the opposite of relational design, where you model the data once and then ask any question.

Wide-column is not the same as columnar. Analytical databases such as ClickHouse, DuckDB, Snowflake, BigQuery and Amazon Redshift store data column by column to scan billions of rows fast for reports. They are SQL databases. Cassandra and Bigtable are something else: row-oriented stores with flexible, sparse columns.

Graph databases

A graph database stores nodes (people, products, places) and edges (relationships such as FOLLOWS, BOUGHT or LOCATED_IN), each with properties. Queries follow edges: "friends of my friends", "the shortest route between two stations", "accounts linked to this fraudulent card through any chain of shared devices". Examples: Neo4j, Amazon Neptune, Memgraph. Their query languages are Cypher (now standardized as GQL, ISO/IEC 39075, published in 2024), Gremlin and SPARQL.

TEXT
// Neo4j Cypher (read-only): people Ana's friends follow, whom Ana doesn't follow yet
MATCH (ana:Person {name: 'Ana'})-[:FOLLOWS]->(:Person)-[:FOLLOWS]->(suggestion:Person)
WHERE NOT (ana)-[:FOLLOWS]->(suggestion) AND suggestion <> ana
RETURN DISTINCT suggestion.name

A relational database can store the same graph as an edge table, and a recursive CTE can walk it:

SQL A small graph in SQL

Press Run (or Ctrl + Enter) to see the result here.

The first query suggests Dara and Eli. The second finds Ben and Chen at 1 hop, Dara and Eli at 2, and Femi at 3. For two or three hops on a modest graph, SQL handles this well. Graph databases shine when traversals go deep and unpredictable (fraud rings, network dependencies, recommendation engines over millions of edges), because they jump directly from a node to its neighbors instead of performing one join per hop. SQL itself is catching up: the SQL:2023 standard added SQL/PGQ (property graph queries with a GRAPH_TABLE clause), and Oracle already supports it, with PostgreSQL work in progress.

The trade-offs, side by side

Relational (SQL) Document Key-value Wide-column Graph
Data shape Tables, typed columns JSON documents Opaque values by key Partitions of sorted rows Nodes and edges
Schema Enforced up front Flexible (app-enforced) None Per table, query-driven Flexible
Relationships Joins, foreign keys Embedding or manual references None Denormalized copies First-class edges
Ad-hoc questions Excellent Good within a collection Lookup by key only Only by designed keys Excellent for paths
Transactions Multi-row ACID Single-document by default; multi-document in MongoDB 4.0+ Single key (some offer more) Per partition, limited ACID in Neo4j
Scaling writes Mostly vertical, plus replicas and sharding tools Built-in sharding Built-in sharding Built for many nodes Mostly single cluster
Typical uses Business data, money, most apps Content, catalogs, user profiles Caches, sessions, counters Time series, logs, feeds at huge scale Social, fraud, recommendations

Two ideas explain much of this table:

  • Data that's read together, stored together. NoSQL stores scale out easily because each read or write touches one key, one document or one partition, which lives on one machine. Joins and multi-row transactions need several pieces of data in one place, which is what makes relational databases harder to spread across servers.
  • Consistency vs availability. When a network splits a cluster, a distributed database must choose between refusing some requests (staying consistent) and answering with possibly stale data (staying available). This is the CAP theorem. Many NoSQL systems default to eventual consistency: a write reaches all copies "soon", so a read right after a write may see old data. Cassandra and DynamoDB let you choose per query; relational databases traditionally favor consistency.

The boundary is blurring from both sides. MongoDB added multi-document transactions and schema validation. Distributed SQL databases such as CockroachDB, YugabyteDB, Google Spanner and TiDB offer SQL, joins and ACID transactions across many machines. And PostgreSQL extensions like Citus shard a regular PostgreSQL database.

SQL's JSON features: the bridge

Modern SQL databases let you put JSON documents inside a relational table. That gives you strict columns for the data every row has, and a flexible JSON column for the part that varies, such as product attributes that differ by category. A CHECK (json_valid(...)) constraint keeps the column honest:

SQL Documents inside a table

Press Run (or Ctrl + Enter) to see the result here.

The first query returns Trail Shoe (blue) and Rain Jacket (yellow), because ->> turns the JSON true into the SQL integer 1. The second finds the same two products by expanding each sizes array into rows with json_each(). The third returns Notebook, whose document has no color key. Note what the CHECK buys you: inserting '{color: blue}' (not valid JSON) would fail, which is a guarantee many document stores don't give by default.

Querying JSON doesn't have to mean scanning every row. Index the exact expression your queries use, and the planner can search it like a normal column:

SQL Indexing a JSON field

Press Run (or Ctrl + Enter) to see the result here.

Before the index, the plan is SCAN events; after it, SEARCH events USING COVERING INDEX events_type_idx (<expr>=?) (covering, because the query needs only id, which every index entry stores). The query must use the same expression as the index (body ->> '$.type') for this to work. The final query shows user 1 with 2 purchases totaling 69.5 and user 2 with 1 purchase of 12. The full set of JSON functions is covered in Working with JSON, and expression indexes in Indexes.

Other databases go further (read-only):

SQL
-- PostgreSQL: binary JSONB with a GIN index for containment queries
CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  attributes JSONB NOT NULL DEFAULT '{}'
);
CREATE INDEX products_attributes_idx ON products USING gin (attributes);
SELECT name FROM products WHERE attributes @> '{"waterproof": true}';

-- MySQL: index a JSON field through a generated column
ALTER TABLE products
  ADD COLUMN color VARCHAR(30) AS (attributes ->> '$.color') STORED,
  ADD INDEX products_color_idx (color);

PostgreSQL's JSONB with GIN indexes is good enough that many teams use it instead of a separate document database. SQL Server has JSON functions such as JSON_VALUE and OPENJSON (and a native json type in Azure SQL and SQL Server 2025), and Oracle 21c added a native JSON type.

How to choose

Most applications should start with a relational database. PostgreSQL, MySQL or SQLite handle far more traffic than most projects ever see, keep your data consistent, and answer questions you haven't thought of yet. Add JSON columns for the parts that really vary. Then add a specialized store when a specific need appears:

  • Money, inventory, bookings, anything with rules between records: relational. Constraints and multi-row transactions are worth a lot.
  • A cache, sessions, rate limits, leaderboards or queues: a key-value store like Redis, next to your main database.
  • Content whose shape varies and is read as a whole (CMS pages, product catalogs with wildly different attributes, mobile app data synced per user): a document database, or JSON columns in SQL.
  • Millions of writes per second spread across data centers (time series, event logs, chat history): a wide-column store, accepting query-first design.
  • Deep relationship queries (fraud detection, network topology, "how is X connected to Y"): a graph database, or recursive CTEs while the graph is small.
  • Reports over billions of rows: a columnar analytics database, which still speaks SQL.

Using several stores in one system is called polyglot persistence, and it's common: a typical web app might use PostgreSQL for orders, Redis for caching and Meilisearch or Elasticsearch for full-text search. Each extra system is another thing to back up, monitor, secure and keep in sync, so add one only when it clearly earns its place.

Know SQL either way. Many "NoSQL" systems now offer SQL-like languages (CQL, DynamoDB's PartiQL, Couchbase's SQL++), analytics tools all speak SQL, and data from every store usually ends up in a SQL warehouse for reporting. The skills from this course carry over everywhere.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A shop stores each order as one document with the customer's name and product names copied inside. What is the main downside?

    Embedding makes single-document reads fast but copies data into every document. Aggregating across documents (like total sales per product) needs extra work compared with SQL joins.
  2. 2. Which workload is the most natural fit for a key-value store like Redis?

    Key-value stores excel at lookups and updates by key, such as caches, sessions and counters. Reports and graph traversals need query capabilities they don't have.
  3. 3. In Cassandra, a table's primary key is ((sensor_id, day), reading_at). Which query can it answer efficiently?

    Efficient queries name the full partition key (sensor_id and day). Filtering on other columns or joining isn't what the table was designed for; you'd create another table for that question.
  4. 4. With the products table from this lesson, what does SELECT name FROM products WHERE attributes ->> 'color' IS NULL; return?

    ->> returns NULL when the key is missing, so documents without a color match IS NULL. Only the Notebook lacks one.
  5. 5. You index events (body ->> '$.type'). Which WHERE clause can use that index?

    An expression index helps only queries that use the same expression. LIKE with a leading wildcard and json_valid() still scan the table.

Summary

  • NoSQL is a family of non-relational models that trade some relational features for flexibility, scale or speed on specific access patterns.
  • Document stores keep self-contained JSON documents with embedded data: fast whole-document reads, but duplication and harder cross-document questions.
  • Key-value stores look things up by key only and are ideal for caches, sessions and counters.
  • Wide-column stores partition data across many machines for huge write volumes; you design one table per query, with no joins.
  • Graph databases make relationships first-class for deep traversals; SQL handles shallow ones with joins and recursive CTEs.
  • Relational databases give enforced schemas, joins, ad-hoc queries and multi-row ACID transactions; distributed SQL and NoSQL features are converging.
  • SQL's JSON support (json_valid checks, ->>, json_each, expression indexes, PostgreSQL JSONB) lets one relational database hold flexible documents too.
  • Start relational, add JSON columns where data varies, and add specialized stores only when a concrete need justifies the extra system.

Next: Connecting to Databases from Code: Drivers, ODBC and ORMs.