The SQL vs NoSQL Map: How Concepts Translate

After two decades of "SQL vs NoSQL" debates, the consensus is: they're different tools for different jobs, both are useful, and the differences are smaller than 2010-era hype suggested. Developers regularly use both, sometimes in the same app. This reference maps the vocabulary and operations between them, what SQL calls a "table," MongoDB calls a "collection," DynamoDB calls a "table" (confusingly), Cassandra calls a "table" or "column family," and Redis doesn't have a direct equivalent.

Terminology Map

Operation Map

Insert

SQL:

MongoDB:

DynamoDB:

Find one by ID

SQL:

MongoDB:

DynamoDB:

Filter

SQL:

MongoDB:

DynamoDB (using Query, requires partition key match):

Update

SQL:

MongoDB:

DynamoDB:

Aggregate

SQL:

MongoDB:

DynamoDB: no aggregation. Either scan + aggregate client-side (slow, expensive), or maintain aggregate counters separately, or export to a SQL data warehouse for analytics.

Join

SQL:

MongoDB $lookup:

DynamoDB / Cassandra: typically do two queries from the app side and join in memory. The "single query per access pattern" design philosophy says you should denormalize (store user.name on the order document) to avoid the join entirely.

Modeling Differences

Normalization vs Denormalization

SQL theory says normalize: each piece of data lives in one place; relationships use foreign keys. Updates touch one row; reads use JOINs.

NoSQL theory says denormalize: store related data together for fast reads; accept that writes update in multiple places.

When to denormalize: if the embed is small (user name + email), rarely changes, and is always read with the parent.

When to keep separate: large or frequently-changing data (user comment count), data accessed independently.

The trade-off: SQL optimizes for write simplicity (update one row); NoSQL optimizes for read simplicity (one fetch returns everything).

One-to-many

SQL: child table with foreign key.

NoSQL: embed if bounded; reference if unbounded.

Many-to-many

SQL: junction table.

NoSQL: arrays of references in one or both sides.

Trade-off: NoSQL many-to-many is fast to read on one side but writes update both sides. SQL junction table is symmetric but requires a JOIN.

Consistency Models

ACID (SQL)

• Atomicity: transactions all-or-nothing.
• Consistency: constraints enforced.
• Isolation: concurrent transactions don't interfere.
• Durability: committed data survives crashes.

BASE (NoSQL traditional)

• Basically Available: reads always work, even if data is stale.
• Soft state: data can change without input due to eventual consistency propagation.
• Eventual consistency: replicas eventually converge.

CAP Theorem

In a distributed system you can have only 2 of: Consistency, Availability, Partition tolerance. Network partitions happen, so the real choice is C+P vs A+P.

• CP (consistency over availability): MongoDB (with majority writes), HBase, traditional SQL with sync replication.
• AP (availability over consistency): Cassandra, DynamoDB (with eventual reads), Riak.

Modern systems often allow per-query tuning: DynamoDB lets you choose strongly-consistent or eventually-consistent reads.

Indexing

Both SQL and NoSQL use indexes (B-tree style) for fast lookups. The mental model is the same: an index is a sorted data structure mapping values to row/document locations.

Differences:

• SQL: primary key has an implicit index. You can add B-tree, hash, GIN, GiST, etc. depending on database.
• MongoDB: _id has an implicit unique index. Add other indexes with db.users.createIndex({ email: 1 }). Compound indexes work like SQL multi-column indexes, order matters.
• DynamoDB: very different, partition key + sort key form the primary access pattern. Global Secondary Indexes (GSI) allow alternative access patterns but have eventual consistency and cost.
• Cassandra: secondary indexes exist but are limited; the recommended pattern is denormalizing into multiple tables, each indexed for an access pattern.

Indexing strategy in NoSQL often requires modeling around access patterns, designing the schema for the queries you'll run, not the abstract data model.

When to Use Which

Use SQL (PostgreSQL is the default) when:

• Data has clear, stable relationships.
• You need JOINs and aggregations.
• ACID transactions matter (banking, orders, inventory).
• You want a single source of truth with constraints.
• Team knows SQL (almost universal).

PostgreSQL with JSONB columns covers a lot of cases that used to require NoSQL, flexible documents inside a relational system.

Use MongoDB when:

• Data is document-shaped (user profiles with arbitrary fields, content articles with nested structures).
• Schema evolves fast and you don't want migration overhead.
• Single-document operations are the norm.
• Horizontal scaling matters but you also want some query flexibility.

Use DynamoDB when:

• You know your access patterns up front (and they're simple key lookups + range queries).
• Massive scale (billions of items, millions of QPS).
• AWS-native architecture.
• Predictable cost matters (DynamoDB has on-demand pricing).

Use Redis when:

• You need a cache.
• Session storage.
• Rate limiting counters.
• Pub/sub messaging.
• Real-time leaderboards (sorted sets).

Use Elasticsearch when:

• Full-text search.
• Complex filtering across many fields.
• Log/event analytics.

Pair: typically, an app uses PostgreSQL as primary + Redis as cache + Elasticsearch for search. Or PostgreSQL alone with full-text search (PG's built-in tsvector handles surprisingly large workloads).

The Modern Convergence

The SQL/NoSQL line has blurred since the early 2010s:

• PostgreSQL has JSONB (document storage), arrays, full-text search, partial indexes. Covers many "NoSQL" use cases.
• MongoDB has ACID transactions, schema validation, joins. Covers many "SQL" use cases.
• Cloud-native SQL (Spanner, CockroachDB, Aurora) scales horizontally like NoSQL while preserving ACID.
• NewSQL is the term for distributed-SQL databases that don't sacrifice ACID.

The takeaway: pick by access pattern and team expertise, not by SQL/NoSQL label. Both can fit many use cases. Most apps are fine with PostgreSQL.

Privacy

This reference is a static lookup index in your browser. Your queries, sometimes hinting at proprietary data models, unreleased schemas, or internal table names, stay in the tab. Open DevTools Network during use: zero outbound requests.