What if you could approach any database and its query language was just syntax? The trick isn’t more SQL, it’s relational algebra (RA): the intent layer driving every query.
Don’t worry, no scary math here. We’ll use plain JavaScript to break down joins, groups, differences, and more.
RA is your mental toolbox to reason about any SQL query before writing a single line of code. Once RA clicked for me, I could scan any DB, SQL, NoSQL, graph, and instantly “get” it.
Ever messed up a deep-dive database analysis at work? (guilty.) This’ll let you boss your next one.
Relational Algebra
The language of intent.
Its one job? Express intent clearly. You do that, and the system gives you exactly what you asked for. Think of it like a restaurant menu.
You’re not making the food. You’re pointing at an item and saying “this.”
You trust the kitchen to bring what you meant.
That’s SQL. You don’t know where the data lives, how it’s queried, or optimized, you just describe what you want, and the system delivers (if you asked correctly).
Here’s that same idea in RA:
RA: σ age > 30 ( Users ) // select statement
This says: “Wherever you are, if you find a table called Users, filter all entries where age > 30 and hand them to me.”
That’s algebra. And it works.
In tech terms:
- The query language is the menu.
- TCP is the waiter.
- The database engine is the kitchen, from parser to optimizer to storage engine.
Your job? Point at the menu with clear intent.
That’s the secret of relational algebra. Let’s walk through the 7 building blocks using JS to build your mental model:
σ: select(predicate)(table)
You’re saying: “Give me all rows from this table where the condition is true.”
In JavaScript:
const users = [
{ id: 1, name: "Alice", age: 30 },
{ id: 2, name: "Bob", age: 24 },
{ id: 3, name: "Carol", age: 35 },
];
const select = predicate => table => table.filter(predicate); // higher order function
Example:
// SQL Equivalent:
// SELECT *
// FROM Users
// WHERE age > 30;
const over30 = select(u => u.age > 30); // takes a predicate and returns a function that takes a table and apply's the predicate
console.log(over30(users));
Or more compact:
const over30 = select(u => u.age > 30)(users);
That’s σ, select.
π: project(fields)(table)
Project doesn’t care about rows, it’s about picking columns.
const project2 = fields => table =>
table.map(row => {
const obj = {};
for (const f of fields) {
obj[f] = row[f]; // pick each field
}
return obj;
});
Usage:
// SQL Equivalent:
// SELECT name
// FROM Users;
const namesOnly = project(["name"]);
console.log(namesOnly(users));
What’s going on?
obj[f] = row[f]; // pick each field
This grabs just the fields you care about from each row.
Or more compact:
const project = fields => table =>
table.map(row =>
fields.reduce((obj, f) => ((obj[f] = row[f]), obj), {})
);
Easy.
⋈: join(onFn)(leftTable, rightTable)
Join combines two tables where a relationship exists.
const orders = [
{ id: 101, userId: 1, total: 50 },
{ id: 102, userId: 2, total: 150 },
{ id: 103, userId: 1, total: 20 },
];
const join = onFn => (L, R) =>
L.flatMap(l =>
R.filter(r => onFn(l, r)) // focus here
.map(r => ({ ...l, ...r })) // and here flatmap explained below
);
Example:
// SQL Equivalent:
// SELECT Users.*, Orders.*
// FROM Users
// JOIN Orders
// ON Users.id = Orders.userId;
const userOrders = join((u, o) => u.id === o.userId)(users, orders);
Let’s unpack:
-
R.filter(...)finds all matches inRfor the current row inL. -
.map(...)merges the two rows ({ ...l, ...r }) -
.flatMap(...)squashes all matched rows into a flat result list.
So for each user, we find all orders that match.
Join complete.
∪: union(tableA, tableB)
Combines two tables and drops duplicates.
// SELECT * FROM A
// UNION
// SELECT * FROM B
const union = (A, B) => {
const key = row => JSON.stringify(row); // quick hash
const seen = new Set();
return [...A, ...B].filter(row => {
const k = key(row);
if (seen.has(k)) return false;
seen.add(k);
return true;
});
};
Example:
const t1 = [{ name: "Alice" }, { name: "Carol" }];
const t2 = [{ name: "Bob" }, { name: "Alice" }];
console.log(union(t1, t2));
The secret sauce:
const key = row => JSON.stringify(row);
This gives each row a unique fingerprint. If we’ve already seen it, skip it.
if (seen.has(k)) return false;
What it does: Return every row that appears in either A or B, no duplicates.
Keyword: either.
(Spoiler: That’s what makes it different from difference.)
−: difference(tableA, tableB)
Return every row in A that doesn’t exist in B.
// SELECT * FROM A
// EXCEPT
// SELECT * FROM B
const difference = (A, B) => {
const keyB = new Set(B.map(r => JSON.stringify(r)));
return A.filter(r => !keyB.has(JSON.stringify(r)));
};
Think: subtraction. Keep what’s only in A.
∩: intersection(tableA, tableB)
Keep only rows that appear in both A and B.
// SELECT * FROM A
// INTERSECT
// SELECT * FROM B
const intersection = (A, B) => {
const keyB = new Set(B.map(r => JSON.stringify(r)));
return A.filter(r => keyB.has(JSON.stringify(r)));
};
Same hash trick, reversed condition. That’s the nice thing, once you get the pattern, the variations write themselves.
There’s more:
-
÷Division: Find rows in A that match all in B (advanced use case). -
groupBy: For aggregations and counts. -
rename: Alias columns.
But the principle never changes:
Relational algebra is about expressing intent.
You’re building a menu. The engine is the kitchen. If your intent is clear, the system delivers.
Once that clicks, databases stop being intimidating and start feeling like tools again.
I’ll be posting more deep dives on backend topics,JavaScript, Golang, C++, and low-level systems on Substack. Would love to have you there; come say hi:
Thanks for reading.
Top comments (2)
Resources
In database theory, relational algebra is a theory that uses algebraic structures for modeling data and defining queries on it with well founded semantics. The theory was introduced by Edgar F. Codd.
Love how you broke down RA into real JS - it makes the concepts instantly click for me. Have you found this mental model helps when tackling NoSQL or graph DBs in practice too?