SQL Joins and Aggregations

A Swiggy operations analyst opens a dashboard on a Monday morning and asks a question that sounds trivial: "which Bengaluru restaurants earned more than ₹1,000 last week?" The data she needs is split across two tables that were never designed to answer that question directly. One table, restaurants, lists every partner restaurant with its city. Another, orders, records every transaction with an amount and a foreign key pointing back to the restaurant that fulfilled it. Neither table alone contains the answer. The restaurant table has no revenue column; the order table has no city column. The answer only exists in the relationship between the two — and extracting it correctly, without silently inflating or dropping numbers, is exactly what this chapter is about. Joins stitch related tables together row by row; aggregations then collapse many rows into one summary number per group. Used together they are the single most common operation in every production database at Swiggy, IRCTC, Zomato, or the NSE — and they are also the single most common source of silently wrong dashboards, because a join that goes wrong does not throw an error. It just returns a number that looks plausible and is not.

The relational setup

A relational database splits information into normalized tables so that each fact is stored exactly once. A restaurant's name and city belong in restaurants; an order's amount and timestamp belong in orders. The two are connected by a foreign key: every row in orders carries a restaurant_id that must match the restaurant_id — the primary key — of exactly one row in restaurants. This is the standard one-to-many relationship: one restaurant, many orders. A JOIN is the operation that walks this relationship and reassembles the two tables into one wide view, matching rows on equal key values.

Here is the exact dataset used for every worked example in this chapter. Six restaurants:

restaurant_id | name               | city
1             | Meghana Foods      | Bengaluru
2             | Truffles           | Bengaluru
3             | Empire Restaurant  | Bengaluru
4             | Paradise Biryani   | Hyderabad
5             | Sagar Ratna        | Delhi
6             | Only Parathas      | Bengaluru

Seven orders, each referencing a restaurant:

order_id | restaurant_id | amount
101      | 1             | 450
102      | 1             | 620
103      | 2             | 300
104      | 3             | 900
105      | 3             | 250
106      | 4             | 700
107      | 5             | 500

Notice restaurant 6, Only Parathas, has zero matching rows in orders — it just opened this week. That gap is deliberate; it is what exposes the difference between INNER JOIN and LEFT JOIN later in the chapter.

INNER JOIN: matching rows only

An INNER JOIN returns only the rows where the join condition finds a match on both sides. To get restaurant names next to their order amounts:

SELECT r.name, o.amount
FROM restaurants r
JOIN orders o ON o.restaurant_id = r.restaurant_id
WHERE r.city = 'Bengaluru';

Trace it by hand. The engine conceptually walks every row of orders, looks up the matching restaurants row by restaurant_id, and keeps the pair only if the restaurant's city is Bengaluru. Orders 101 and 102 match restaurant 1 (Meghana Foods, Bengaluru) → kept. Order 103 matches restaurant 2 (Truffles, Bengaluru) → kept. Orders 104 and 105 match restaurant 3 (Empire Restaurant, Bengaluru) → kept. Orders 106 and 107 match restaurants in Hyderabad and Delhi → filtered out by the WHERE. Restaurant 6, Only Parathas, contributes nothing because it has no row in orders to match against — an INNER JOIN never invents a row for it. The result set has exactly five rows: (Meghana Foods, 450), (Meghana Foods, 620), (Truffles, 300), (Empire Restaurant, 900), (Empire Restaurant, 250).

How the engine actually executes a join

It is worth knowing what happens underneath, because it explains why the same query can take milliseconds on one table and minutes on another. A query planner picks from three standard algorithms:

Nested loop join. For every row of the outer table (size n), scan the inner table (size m) looking for a match. Without any index this costs O(n·m) comparisons — for our tiny tables, 6×7 = 42 comparisons, trivial, but at Swiggy's real scale (millions of restaurants, billions of orders) a full O(n·m) scan is unusable. If the inner table has a B-tree index on the join column, each lookup drops from a linear scan to O(log m), so the total cost becomes O(n·log m) — this is why restaurant_id in a real orders table is always indexed.

Hash join. Build an in-memory hash table on the smaller side, keyed by the join column — O(m) to build — then stream through the larger side, probing the hash table for each row in O(1) average time. Total cost O(n+m). This is the default choice when there is no useful index and the tables are large, because it avoids the quadratic blow-up of an unindexed nested loop.

Sort-merge join. Sort both tables by the join column — O(n log n + m log m) — then walk them together with two pointers in a single O(n+m) linear pass, advancing whichever pointer points to the smaller key. This wins when the data is already sorted (for example, both sides are clustered on the join key), since the expensive sort step is skipped entirely.

For our six-row and seven-row tables the optimizer would not bother with any of this sophistication — it would just do a sequential scan and a nested loop, because the fixed overhead of building a hash table exceeds any saving at this size. The algorithm choice only starts to matter once table sizes cross a few thousand rows, which is precisely the regime every real Swiggy table lives in.

LEFT JOIN and the meaning of NULL

The operations team also wants to know about restaurants with zero orders — exactly the row that INNER JOIN deletes. A LEFT JOIN keeps every row from the left table regardless of whether the right side matches, filling in NULL for every right-side column when there is no match:

SELECT r.name,
       COALESCE(SUM(o.amount), 0) AS revenue,
       COUNT(o.order_id)          AS num_orders
FROM restaurants r
LEFT JOIN orders o ON o.restaurant_id = r.restaurant_id
WHERE r.city = 'Bengaluru'
GROUP BY r.name;

Trace the four Bengaluru restaurants. Meghana Foods matches orders 101 and 102: revenue 450+620 = 1070, num_orders 2. Truffles matches order 103: revenue 300, num_orders 1. Empire Restaurant matches orders 104 and 105: revenue 900+250 = 1150, num_orders 2. Only Parathas matches nothing, so the join produces one row for it with o.amount and o.order_id both NULL. COUNT(o.order_id) counts non-null values only, so it correctly returns 0 — but COUNT(*) on that same row would return 1, because it counts the joined row itself, NULLs and all. SUM(o.amount) over zero non-null values is NULL, not 0, which is why the query wraps it in COALESCE(..., 0). Skip that and the dashboard would print a blank cell for a restaurant that genuinely earned nothing — a subtle but common bug.

Aggregation: GROUP BY, and WHERE versus HAVING

GROUP BY partitions rows into buckets sharing a key and collapses each bucket into one row via an aggregate function (SUM, COUNT, AVG, MIN, MAX). The engine's logical order of operations is: FROM/JOIN builds the combined row set, WHERE filters individual rows, GROUP BY buckets what survives, the aggregate functions compute one value per bucket, and only then does HAVING filter the buckets. That ordering is the whole story: WHERE can never reference an aggregate, because aggregates do not exist yet when WHERE runs; HAVING exists specifically to filter on values that only exist after grouping.

To find every restaurant nationwide with total revenue above ₹1,000:

SELECT r.name, SUM(o.amount) AS revenue
FROM restaurants r
JOIN orders o ON o.restaurant_id = r.restaurant_id
GROUP BY r.name
HAVING SUM(o.amount) > 1000;

Compute every restaurant's total first: Meghana Foods 1070, Truffles 300, Empire Restaurant 1150, Paradise Biryani 700, Sagar Ratna 500. Only Meghana Foods and Empire Restaurant clear the 1000 bar, so those are the only two rows in the final result. Note that filtering by city (a row-level fact, known before grouping) belongs in WHERE, while filtering by total revenue (a group-level fact, known only after aggregating) can only be expressed in HAVING — writing WHERE SUM(o.amount) > 1000 is not merely bad style, it is a syntax error in every major SQL engine.

The misconception: joins can multiply your aggregates

The single most common mistake at this level is assuming that adding a second JOIN to a query never changes the result of an aggregate already computed over the first join. It does — often silently, and in both directions. Suppose the food-delivery app also stores customer reviews, one row per review, and an order can receive more than one review:

review_id | order_id | rating
1         | 104      | 5
2         | 104      | 4
3         | 105      | 3
5         | 102      | 4

Order 104 (Empire Restaurant, ₹900) has two reviews; order 105 (Empire Restaurant, ₹250) has one; order 102 (Meghana Foods, ₹620) has one; order 101 (Meghana Foods, ₹450) has none. Now suppose an analyst wants "revenue and review count per restaurant" and, reasonably enough, joins all three tables in one query:

SELECT r.name, SUM(o.amount) AS revenue, COUNT(*) AS row_count
FROM restaurants r
JOIN orders  o  ON o.restaurant_id = r.restaurant_id
JOIN reviews rv ON rv.order_id     = o.order_id
GROUP BY r.name;

For Empire Restaurant, walk the join literally, one output row per matching pair: order 104 pairs with review 1 → row (900), order 104 pairs with review 2 → a second row, also carrying amount 900 again, order 105 pairs with review 3 → row (250). Three rows total, and SUM(o.amount) adds 900 + 900 + 250 = 2050 — not the true revenue of 1150. The amount from order 104 was counted twice because it had two reviews; the join multiplied it by its own fan-out. For Meghana Foods the opposite failure appears: order 102 (620) pairs with review 5 and survives, but order 101 (450) has no review at all, so the INNER JOIN drops it entirely. SUM(o.amount) for Meghana Foods comes out to 620, not the true 1070. Restaurant 2 (Truffles), restaurant 4 (Paradise Biryani), restaurant 5 (Sagar Ratna), and restaurant 6 (Only Parathas) — none of which have a single matching review — vanish from the result completely. One join, three different silent failure modes: double-counted, under-counted, and missing — and the query runs without any error, returning numbers that look perfectly reasonable to anyone who doesn't already know the right answer.

The fix is to never aggregate a fact table across a fan-out relationship in the same breath as joining it to another fact table. Aggregate each one-to-many relationship separately first, then join the pre-aggregated results together:

WITH order_totals AS (
  SELECT restaurant_id, SUM(amount) AS revenue, COUNT(*) AS num_orders
  FROM orders
  GROUP BY restaurant_id
),
review_counts AS (
  SELECT o.restaurant_id, COUNT(*) AS num_reviews
  FROM orders o
  JOIN reviews rv ON rv.order_id = o.order_id
  GROUP BY o.restaurant_id
)
SELECT r.name,
       COALESCE(ot.revenue, 0)     AS revenue,
       COALESCE(ot.num_orders, 0)  AS num_orders,
       COALESCE(rc.num_reviews, 0) AS num_reviews
FROM restaurants r
LEFT JOIN order_totals ot  ON ot.restaurant_id = r.restaurant_id
LEFT JOIN review_counts rc ON rc.restaurant_id = r.restaurant_id;

Now Empire Restaurant correctly reports revenue 1150 with num_reviews 3, and Meghana Foods correctly reports revenue 1070 with num_reviews 1 — the two aggregates were computed inside their own grouped subquery, where each fact table's own fan-out is contained before any join with a different-grained table happens. Only Parathas, which has neither orders nor reviews, now correctly reports revenue 0, num_orders 0, and num_reviews 0 instead of being silently dropped — the LEFT JOIN to order_totals keeps every restaurant row exactly as the chapter's opening section established it must. This pattern — aggregate first, join second, sometimes called "grain matching" — is the single habit that prevents the most expensive class of dashboard bug: the one that is wrong by a plausible-looking amount and never gets noticed.

Diagram: how a join multiplies matching rows

Active recall

Attempt every question before reading its answer.

Q1. Write one query that lists every restaurant with its order count and total revenue, including restaurants with zero orders, sorted by revenue from highest to lowest.

Q2. Using the orders and reviews tables above (with review 5 attached to order 102), what does SELECT r.name, SUM(o.amount) FROM restaurants r JOIN orders o ON o.restaurant_id = r.restaurant_id JOIN reviews rv ON rv.order_id = o.order_id WHERE r.name = 'Meghana Foods' GROUP BY r.name; return, and why does it differ from Meghana Foods' true revenue of 1070?

Q3. Write a query that lists restaurants in Hyderabad or Delhi with more than one order and total revenue above ₹600.

Q4. For the Bengaluru LEFT JOIN query in the aggregation section, what does COUNT(*) return for Only Parathas, and how does that differ from COUNT(o.order_id)?

Q5. If both restaurants.restaurant_id and orders.restaurant_id have B-tree indexes, and the optimizer picks a nested loop join with orders as the outer table, what is the approximate cost in Big-O terms, using n for the number of orders and m for the number of restaurants?

Q6. Suppose a review row — review_id 4, order_id 103 — is added to reviews just before Truffles (restaurant 2) shuts down: its only order, 103, is then deleted from the orders table, and the review row is never cleaned up. Trace what happens to (a) the Bengaluru LEFT JOIN revenue query from the aggregation section, and (b) the review_counts CTE from the fan-out fix, for Truffles specifically.

Answers

A1.

SELECT r.name,
       COUNT(o.order_id)          AS num_orders,
       COALESCE(SUM(o.amount), 0) AS revenue
FROM restaurants r
LEFT JOIN orders o ON o.restaurant_id = r.restaurant_id
GROUP BY r.name
ORDER BY revenue DESC;

This returns all six restaurants: Empire Restaurant 1150, Meghana Foods 1070, Paradise Biryani 700, Sagar Ratna 500, Truffles 300, Only Parathas 0. LEFT JOIN is required — an INNER JOIN would silently drop Only Parathas.

A2. It returns 620, not 1070. Order 102 (₹620) has one matching review (review 5) and survives the join intact. Order 101 (₹450) has zero matching reviews, so the INNER JOIN against reviews eliminates that row entirely before the SUM ever sees it. The aggregate is silently missing an entire order because the second join filtered on a relationship that has nothing to do with revenue.

A3.

SELECT r.name, SUM(o.amount) AS revenue, COUNT(*) AS num_orders
FROM restaurants r
JOIN orders o ON o.restaurant_id = r.restaurant_id
WHERE r.city IN ('Hyderabad', 'Delhi')
GROUP BY r.name
HAVING COUNT(*) > 1 AND SUM(o.amount) > 600;

Paradise Biryani (Hyderabad) has one order of 700 — fails the COUNT(*) > 1 condition. Sagar Ratna (Delhi) has one order of 500 — fails both conditions. The result set is empty with this exact dataset, which is itself a valid and useful answer: it shows the query is correct even though no row currently satisfies it.

A4. COUNT(*) returns 1 for Only Parathas, because the LEFT JOIN still produces one output row for it (with every orders column set to NULL), and COUNT(*) counts rows regardless of their contents. COUNT(o.order_id) returns 0, because COUNT on a specific column ignores NULL values, and o.order_id is NULL in that row. Using COUNT(*) where you meant "number of orders" is a common way to accidentally report 1 order for a restaurant that has none.

A5. With an index on the inner table's join column, each of the n outer-table probes costs O(log m) instead of a full O(m) scan, so the total cost is O(n log m) rather than the unindexed O(n·m).

A6. (a) In the Bengaluru LEFT JOIN revenue query, Truffles now shows num_orders = 0 and revenue = 0 (via COALESCE), exactly as if it had never had any orders — the deletion is reflected correctly here because that query only touches restaurants and orders. (b) In the review_counts CTE, the inner join is orders o JOIN reviews rv ON rv.order_id = o.order_id. Since order 103 no longer exists in orders, review 4 (which still points at order_id 103) finds no matching row on the orders side and is silently excluded from the join — not flagged as an error, just dropped. Truffles' num_reviews comes out as 0 (via the outer COALESCE in the final SELECT), identical to a restaurant that genuinely never received a review. The orphaned review row still physically exists in the reviews table, invisible to both queries, because nothing enforced referential integrity when order 103 was deleted. The ripple effect is not just "revenue goes to zero" — it is that a real customer review has become permanently unreachable through any join on order_id, and the aggregate reports look completely clean while hiding that data-integrity failure. This is exactly why production schemas declare FOREIGN KEY ... ON DELETE CASCADE or ON DELETE RESTRICT rather than leaving deletions to application code.

Think About It

Think about this: How would you explain sql joins and aggregations to a friend who has never seen a computer? What real-world analogy would you use? Imagine you had to build a system using these concepts — what would be your first step? Try this: before moving on, write down three things you learned and one question you still have.

Practice Exercises

Now it is time to practice! Complete these challenges to solidify your understanding:

• Exercise 1: Write a short program that demonstrates the core concept from this chapter. Test it with at least 3 different inputs.
• Exercise 2: Find a real-world example where sql joins and aggregations is used in an Indian company (like TCS, Infosys, Flipkart, or ISRO). Write a paragraph explaining the connection.
• Exercise 3: Create a mind-map connecting sql joins and aggregations to at least 3 other topics you have studied.