I have mostly been a frontend developer, but recently I got interested in full-stack work.
Along the way, I discovered a nice way to grasp SQL concepts, which works if you are already a developer: look at the equivalent code (AI can write it for you) for the SQL concept or query you are trying to understand.
Our examples will focus on an online store's customers and orders. We'll calculate how much each customer spent and how many orders they placed, find customers whose total spending exceeds a certain amount, and list what customers ordered-including customers who haven't ordered anything yet. Along the way, we'll explain GROUP BY, SUM, COUNT, HAVING, and LEFT JOIN through equivalent JavaScript.
First Example: GROUP BY with SUM and COUNT
Suppose we have an orders table with a customer and an amount column.
We want to find, for each customer, how much they spent in total and how many orders they placed.
Here's the SQL:
SELECT customer, SUM(amount) AS total_spent, COUNT(*) AS order_count FROM orders GROUP BY customer;
Here's the equivalent JavaScript:
// Sample table data const orders = [ { customer: "Alice", amount: 120 }, { customer: "Bob", amount: 80 }, { customer: "Alice", amount: 50 }, { customer: "Bob", amount: 200 }, { customer: "Carol", amount: 75 }, { customer: "Alice", amount: 30 }, ]; const groups = {}; for (const order of orders) { // SQL: GROUP BY customer // Create a group the first time we see a customer. if (!groups[order.customer]) { groups[order.customer] = { customer: order.customer, total_spent: 0, order_count: 0, }; } // SQL: SUM(amount) // Accumulate the total amount spent by this customer. groups[order.customer].total_spent += order.amount; // SQL: COUNT(*) // Count how many orders belong to this customer. groups[order.customer].order_count += 1; } // One row per customer, like a query result const result = Object.values(groups);
Here is what result contains:
[ { customer: "Alice", total_spent: 200, order_count: 3 }, { customer: "Bob", total_spent: 280, order_count: 2 }, { customer: "Carol", total_spent: 75, order_count: 1 }, ];
Each customer's orders are grouped together, their amounts are added up, and the orders are counted. The result is one row per customer with their total spending and number of orders.
Hopefully, the JavaScript example made the query a little easier to understand.
Next Example: HAVING
Using the same orders table, we want to find each customer's total spending and keep only customers whose total is above 100.
Here's the SQL:
SELECT customer, SUM(amount) AS total_spent FROM orders GROUP BY customer HAVING SUM(amount) > 100;
Here's the equivalent JavaScript:
const groups = {}; for (const order of orders) { // SQL: GROUP BY customer if (!groups[order.customer]) { groups[order.customer] = { customer: order.customer, total_spent: 0 }; } // SQL: SUM(amount) groups[order.customer].total_spent += order.amount; } // SQL: HAVING SUM(amount) > 100 // Filter the groups AFTER they have been built. const result = Object.values(groups).filter((group) => group.total_spent > 100);
Here is what result contains:
[ { customer: "Alice", total_spent: 200 }, { customer: "Bob", total_spent: 280 }, ];
Each customer's spending is added up first, then only customers with totals above 100 are kept. Carol is left out because her total is 75.
HAVING vs. WHERE
WHERE also filters, but it runs at a different moment.
Here's the SQL:
SELECT customer, SUM(amount) AS total_spent FROM orders WHERE amount > 100 GROUP BY customer;
Here's the equivalent JavaScript:
const groups = {}; for (const order of orders) { // SQL: WHERE amount > 100 // Filter the individual rows BEFORE grouping. if (order.amount <= 100) continue; if (!groups[order.customer]) { groups[order.customer] = { customer: order.customer, total_spent: 0 }; } groups[order.customer].total_spent += order.amount; } const result = Object.values(groups);
Here is what result contains:
[ { customer: "Alice", total_spent: 120 }, { customer: "Bob", total_spent: 200 }, ];
This time, individual orders of 100 or less are removed before each customer's spending is added up. Alice's total is now 120 instead of 200 because her orders of 50 and 30 no longer contribute.
The rule of thumb is simple: WHERE filters rows before grouping, and HAVING filters groups after grouping.
Another Example: LEFT JOIN
Suppose we have two tables: customers, with customer details, and orders, with each ordered product and the ID of the customer who ordered it.
We want to list all customers along with the products they ordered, including customers who haven't placed any orders yet.
Here's the SQL:
SELECT customers.name, orders.product FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;
Here's the equivalent JavaScript:
// Sample data for the two tables const customers = [ { id: 1, name: "Alice", email: "alice@example.com" }, { id: 2, name: "Bob", email: "bob@example.com" }, { id: 3, name: "Carol", email: "carol@example.com" }, ]; const orders = [ { customer_id: 1, product: "Keyboard", amount: 120 }, { customer_id: 1, product: "Mouse", amount: 50 }, { customer_id: 2, product: "Monitor", amount: 80 }, ]; const result = []; for (const customer of customers) { // SQL: ON customers.id = orders.customer_id const matchingOrders = orders.filter((order) => order.customer_id === customer.id); // SQL: LEFT JOIN - keep customers who have no matching orders. if (matchingOrders.length === 0) { result.push({ name: customer.name, product: null }); } // SQL: LEFT JOIN - pair the customer with each matching order. for (const order of matchingOrders) { result.push({ name: customer.name, product: order.product }); } }
Here's what result contains:
[ { name: "Alice", product: "Keyboard" }, { name: "Alice", product: "Mouse" }, { name: "Bob", product: "Monitor" }, { name: "Carol", product: null }, ];
Each customer is paired with every order that belongs to them, producing one result row per match. Customers with no orders still appear, with an empty product value (null). That's why Alice appears twice and Carol still appears once.
And here's something interesting: if we remove the if (matchingOrders.length === 0) block, we get the equivalent of an INNER JOIN. Only customers with matching orders would appear in the result.
These JavaScript examples should give you a good understanding of the SQL concepts, even if a few edge cases work slightly differently. For example, SQL's SUM() returns NULL when all values are NULL, while the JavaScript version above would return 0.