SQL Joins and Aggregation: Combining Data

Open the IRCTC app and book a train ticket. The confirmation screen shows your name, your seat number, the train's departure time, and the platform number — all in one neat card. It feels like this information lives in one place. It doesn't. Behind that screen, IRCTC's database keeps passenger details in one table, booking details in another, and train schedules in a third. When you tap "Book," the database doesn't copy your name into the train schedule table. Instead, it runs a query that temporarily stitches the right rows from all three tables together, just long enough to build your ticket. That stitching operation is called a JOIN, and it is one of the two ideas this chapter is built around. The second idea, aggregation, is what lets the database answer questions like "how many tickets did this train sell today?" or "what is the average occupancy this month?" — turning thousands of rows into a single useful number. Both ideas are things you will use constantly the moment you touch any real dataset, so we are going to build them from the ground up, using numbers you can check by hand.

The Problem With One Giant Table

Suppose you are building a small database for a coaching class to track students and their test marks. The most obvious approach — the one almost everyone tries first — is to put everything in a single table:

StudentID | Name  | City      | Subject | Marks
1         | Aarav | Mumbai    | Maths   | 85
1         | Aarav | Mumbai    | Science | 90
2         | Diya  | Pune      | Maths   | 78
3         | Kabir | Delhi     | Maths   | 92
3         | Kabir | Delhi     | Science | 88
4         | Meera | Chennai   | Maths   | 65

Look closely at what happened to Aarav and Kabir. Because each of them took two subject tests, their name and city got typed out twice, word for word. This is not just untidy — it is a real bug waiting to happen. If Aarav's family moves from Mumbai to Delhi, someone has to remember to update both rows. Miss one, and your database now claims Aarav lives in two cities at once. Multiply this across a school of 2,000 students with 8 subjects each, and you get thousands of copies of the same name and city, any of which can silently drift out of sync with the others. Database designers call this an update anomaly, and the fix is always the same: stop repeating information that belongs to an entity — split it into its own table, store it exactly once, and use a small piece of shared data to reconnect the pieces only when you actually need them together.

Splitting the Data: Primary Keys and Foreign Keys

Let's split that flat table into two focused ones. A Students table holds each student's identity exactly once:

Students
StudentID | Name  | City
1         | Aarav | Mumbai
2         | Diya  | Pune
3         | Kabir | Delhi
4         | Meera | Chennai
5         | Rohan | Bengaluru

And a Scores table holds each test result, one row per test taken:

Scores
ScoreID | StudentID | Subject | Marks
101     | 1         | Maths   | 85
102     | 1         | Science | 90
103     | 2         | Maths   | 78
104     | 3         | Maths   | 92
105     | 3         | Science | 88
106     | 4         | Maths   | 65

Notice two things. First, StudentID in the Students table never repeats — 1, 2, 3, 4, 5 each appear exactly once. A column with this property, one that uniquely identifies every row, is called a primary key. Second, the StudentID column reappears inside Scores, where it can repeat freely (StudentID 1 appears twice, once per subject). When a column in one table exists purely to point back at the primary key of another table, it is called a foreign key. The foreign key is the thread that lets us reconnect a test score to the student who earned it, without ever storing that student's name or city more than once. Also notice Rohan: he is a real, valid student with no rows in Scores at all — perhaps he hasn't taken a test yet. Keep him in mind; he becomes important a few sections from now.

How a JOIN Actually Works

A JOIN is a query operation that reconnects rows from two tables using a matching column — almost always a primary key in one table and the matching foreign key in the other. Before you memorize any syntax, it helps to understand what the database engine is conceptually doing underneath, because it is a genuinely simple algorithm: for every row in the first table, scan every row in the second table, and whenever the join column values are equal, glue the two rows together into one combined row.

With 5 rows in Students and 6 rows in Scores, that naive process checks up to 5 × 6 = 30 possible pairings before keeping only the ones where StudentID actually matches on both sides. (Real database engines almost never check all 30 — they build an index on the key column so they can jump straight to matches instead of scanning everything, similar to using a book's index instead of reading every page. But the 30-pairing picture is exactly what the query means, even when the engine is smarter about computing it.) The diagram below shows which of those pairings survive the match for our two tables — a colored line means "these two rows share the same StudentID and get joined together":

INNER JOIN: Matching Rows Across Tables

The most common join, and the one you should learn first, is the INNER JOIN — it keeps only the rows that found a match on both sides, exactly like the colored lines in the diagram above. Here is the query that reconstructs a full "student + their test result" view without ever storing the name twice:

SELECT Students.Name, Scores.Subject, Scores.Marks
FROM Students
INNER JOIN Scores ON Students.StudentID = Scores.StudentID;

Read the ON clause literally: "combine a row from Students with a row from Scores whenever their StudentID values are equal." Trace it the same way the engine does — walk through Scores row by row, and for each one, look up the matching name in Students:

Name  | Subject | Marks
Aarav | Maths   | 85
Aarav | Science | 90
Diya  | Maths   | 78
Kabir | Maths   | 92
Kabir | Science | 88
Meera | Maths   | 65

Six rows out — exactly one output row per row in Scores, since every score has exactly one matching student. Rohan does not appear anywhere in this result. He is a perfectly valid row in Students, but because nothing in Scores carries StudentID 5, there is no pairing for him to join into, so INNER JOIN leaves him out entirely.

LEFT JOIN: Keeping the Unmatched

Sometimes losing Rohan is exactly the wrong answer — imagine you are building a report for a teacher who needs to see every enrolled student, including the ones who haven't taken a test yet, so she can follow up with them. For that, you switch one keyword:

SELECT Students.Name, Scores.Subject, Scores.Marks
FROM Students
LEFT JOIN Scores ON Students.StudentID = Scores.StudentID;

LEFT JOIN keeps every row from the table on the left (Students) no matter what, and fills in NULL for any right-side column when no match exists. The result now has seven rows — the same six as before, plus one new row for Rohan:

Name  | Subject | Marks
Aarav | Maths   | 85
Aarav | Science | 90
Diya  | Maths   | 78
Kabir | Maths   | 92
Kabir | Science | 88
Meera | Maths   | 65
Rohan | NULL    | NULL

NULL here does not mean zero and does not mean an empty string — it means "there was nothing here to join." That distinction matters a lot in a moment, because aggregate functions treat NULL specially: they skip it rather than treating it as zero.

Common Misconception: A JOIN Does Not Change Your Tables

A mistake many beginners make is imagining that running a JOIN somehow merges Students and Scores into one permanent table, the way you might physically staple two sheets of paper together. That is not what happens. Students and Scores remain exactly as they were before and after the query — untouched, still separate, still avoiding the redundancy we worked to eliminate. The joined result is a temporary view, computed fresh each time the query runs, that exists only for as long as it takes to display or use it. This is precisely why splitting data into separate tables works: you get the storage efficiency of never repeating Aarav's city, and you get the convenience of a combined view whenever you actually need one, on demand, without ever paying the redundancy cost in storage.

Aggregate Functions: Turning Many Rows Into One Answer

Now set joins aside for a moment and look at just the Scores table's Marks column: 85, 90, 78, 92, 88, 65. Often you don't want to see six individual numbers — you want one summary number. SQL gives you five core aggregate functions that each collapse a whole column of values into a single result:

• COUNT() — how many rows/values are there?
• SUM() — what do all the values add up to?
• AVG() — what is the average value?
• MAX() — what is the largest value?
• MIN() — what is the smallest value?

SELECT COUNT(*) AS Tests, SUM(Marks) AS Total, AVG(Marks) AS Average,
       MAX(Marks) AS Highest, MIN(Marks) AS Lowest
FROM Scores;

Trace the arithmetic by hand, the same way the engine does internally: it adds 85 + 90 + 78 + 92 + 88 + 65. Working left to right: 85 + 90 = 175, plus 78 = 253, plus 92 = 345, plus 88 = 433, plus 65 = 498. That is your SUM. There were 6 rows, so COUNT(*) is 6, and AVG is 498 ÷ 6 = 83. Scanning the six values for the largest and smallest gives MAX = 92 and MIN = 65. One query, five useful numbers, computed from six raw rows:

Tests | Total | Average | Highest | Lowest
6     | 498   | 83      | 92      | 65

GROUP BY: Sorting Before Summarizing

A single overall average is useful, but "what is the class average in each subject?" is a more interesting question, and it needs a different tool. Picture physically sorting all six test papers into two piles — a Maths pile and a Science pile — before you start adding anything up. That sorting-into-piles step is exactly what GROUP BY does. It splits the table into buckets that share the same value in a chosen column, and then runs your aggregate functions separately within each bucket, not across the whole table.

SELECT Subject, COUNT(*) AS Tests, SUM(Marks) AS Total, AVG(Marks) AS Average
FROM Scores
GROUP BY Subject;

Sort the rows into piles first. The Maths pile holds 85, 78, 92, 65 — four papers. Add them: 85 + 78 = 163, + 92 = 255, + 65 = 320. Divide by 4: average 80. The Science pile holds 90, 88 — two papers. Add them: 90 + 88 = 178. Divide by 2: average 89.

Subject | Tests | Total | Average
Maths   | 4     | 320   | 80
Science | 2     | 178   | 89

Every column you SELECT alongside a GROUP BY must either be the grouped column itself (here, Subject) or the output of an aggregate function. You cannot ask for a plain, un-aggregated column like ScoreID in this query — the engine would have no rule for which of the four Maths ScoreIDs to display next to a single summarized row, so most databases refuse to run it.

Combining JOIN and GROUP BY: Total Marks Per Student

The real power shows up when you chain both ideas together. Suppose you want each student's total and average marks, by name — but names live in Students while marks live in Scores, so you must join first, then group:

SELECT Students.Name, COUNT(*) AS Tests, SUM(Scores.Marks) AS Total,
       AVG(Scores.Marks) AS Average
FROM Students
INNER JOIN Scores ON Students.StudentID = Scores.StudentID
GROUP BY Students.Name;

Think of this as happening in two stages, in order. Stage one, the JOIN, builds the six-row combined table from earlier (Aarav/Maths/85, Aarav/Science/90, Diya/Maths/78, Kabir/Maths/92, Kabir/Science/88, Meera/Maths/65). Stage two, GROUP BY, sorts those six rows into four piles by name and aggregates each pile:

Name  | Tests | Total | Average
Aarav | 2     | 175   | 87.5
Diya  | 1     | 78    | 78
Kabir | 2     | 180   | 90
Meera | 1     | 65    | 65

Check Aarav by hand: 85 + 90 = 175, and 175 ÷ 2 = 87.5. Check Kabir: 92 + 88 = 180, and 180 ÷ 2 = 90. Notice Rohan is gone again — because this query used INNER JOIN, and Rohan never survived that first stage, he never reaches the grouping stage either. If the teacher's report needed to show Rohan with zero tests taken, this query would need to start from a LEFT JOIN instead, and use COUNT(Scores.ScoreID) rather than COUNT(*) — COUNT(*) would incorrectly count Rohan's single NULL-filled row as "1 test," while COUNT(Scores.ScoreID) correctly reports 0, because aggregate functions (other than COUNT(*)) skip NULL values entirely.

WHERE vs HAVING: The Mistake Almost Everyone Makes

Suppose you only want students whose total marks exceed 150. The instinctive move is to bolt a condition onto WHERE:

SELECT Students.Name, SUM(Scores.Marks) AS Total
FROM Students
INNER JOIN Scores ON Students.StudentID = Scores.StudentID
WHERE SUM(Scores.Marks) > 150
GROUP BY Students.Name;

This query does not run. It fails with an error along the lines of "aggregate functions are not allowed in WHERE." The reason is about order of operations: WHERE filters individual rows before any grouping or summing has happened, so at the point WHERE is evaluated, there is no such thing as "this student's SUM" yet — that number literally does not exist until after grouping. To filter on the result of an aggregate, you need the clause built specifically for that job, which runs after grouping: HAVING.

SELECT Students.Name, SUM(Scores.Marks) AS Total
FROM Students
INNER JOIN Scores ON Students.StudentID = Scores.StudentID
GROUP BY Students.Name
HAVING SUM(Scores.Marks) > 150;

Now trace it correctly: join, then group into the four per-student totals (175, 78, 180, 65), then keep only the groups whose total exceeds 150:

Name  | Total
Aarav | 175
Kabir | 180

WHERE and HAVING can also be combined, and when they are, remember which one runs first: WHERE trims raw rows before grouping, HAVING trims groups after aggregating. For instance, to find the average Maths mark per student, computed only from Maths papers:

SELECT Students.Name, AVG(Scores.Marks) AS AvgMaths
FROM Students
INNER JOIN Scores ON Students.StudentID = Scores.StudentID
WHERE Scores.Subject = 'Maths'
GROUP BY Students.Name;

WHERE Scores.Subject = 'Maths' runs first and throws away the two Science rows, leaving Aarav/85, Diya/78, Kabir/92, Meera/65 — one Maths row per student. Grouping then has nothing left to combine, so each student's "average" is just their single Maths mark: Aarav 85, Diya 78, Kabir 92, Meera 65.

Where This Fits in Your Learning

Splitting data into related tables the way we did with Students and Scores — instead of one repetitive flat table — is the starting idea behind relational database design, a topic CBSE Computer Science and Informatics Practices builds on through the senior years, and one that shows up constantly in real systems: IRCTC's passenger and booking tables, a UPI app's user and transaction tables, or a school ERP's student and attendance tables all follow this exact pattern. JOIN and GROUP BY are also two of the most frequently tested SQL concepts in school practicals and in early competitive/aptitude exams that include a database section, precisely because tracing them by hand — as you just did with Aarav, Kabir, Diya, Meera, and Rohan — proves whether you actually understand what a query does, rather than having memorized its syntax.

Check Your Understanding

Work these out by hand using the Students and Scores tables from this chapter before checking the answer.

1. Write a query using LEFT JOIN that lists every student's name alongside their Science marks, including students who never took a Science test. Which names would show NULL for Marks? (Answer: Diya, Meera, and Rohan — none of them has a Science row in Scores, so a LEFT JOIN keeps their names but fills Marks with NULL.)
2. A query groups Scores by Subject and computes MAX(Marks) for each. What are the two output rows? (Answer: Maths → 92, Science → 90 — the highest mark within each subject's pile.)
3. Why does SELECT Subject, ScoreID, AVG(Marks) FROM Scores GROUP BY Subject fail to run in most databases? (Answer: ScoreID is neither the grouped column nor wrapped in an aggregate function, so the engine has no rule for which of several ScoreIDs in a group to display.)
4. Rewrite WHERE COUNT(*) > 1 so it actually runs, assuming the query already has a GROUP BY Students.Name. (Answer: replace WHERE with HAVING — HAVING COUNT(*) > 1 — since COUNT is an aggregate and can only be filtered after grouping.)

Summary

Splitting data into separate tables removes redundancy but creates a new problem: how do you bring related information back together when you need it? A JOIN solves that by matching a primary key in one table against a foreign key in another — INNER JOIN keeps only matched pairs, while LEFT JOIN keeps every row from the left table even when nothing matches, filling the gap with NULL. Neither operation changes the underlying tables; the combined result is computed fresh each time. Aggregate functions — COUNT, SUM, AVG, MAX, MIN — collapse many rows into one summary number, and GROUP BY lets you compute those summaries separately for each distinct value in a column, like sorting test papers into subject piles before adding each pile up. Finally, WHERE and HAVING are not interchangeable: WHERE filters raw rows before any grouping happens, while HAVING filters the summarized groups afterward — and only HAVING can legally reference an aggregate function like SUM(Marks) > 150.