SQL Joins and Aggregations

SQL Joins and Aggregations

Why a school never keeps one register

Every CBSE school keeps two very different kinds of records. The class teacher keeps an admission register: roll number, name, and section for every student. Separately, each subject teacher keeps their own marks register, written after every test, with the roll number, the subject, and the score. Nobody merges these two registers into one giant notebook, because that would mean rewriting a student's name and section every single time a new mark is entered. It is faster to keep two small registers and combine them only when someone actually asks a question like "What is Aditi's average across all subjects?"

A relational database works on exactly this principle. Instead of one bloated table repeating a student's name next to every mark, data is split across multiple tables that stay small and easy to update. The moment you need an answer that depends on both tables at once, you join them. When you need one number out of many rows — a total, an average, a count — you aggregate them. This chapter is about exactly those two operations, because almost no real question ("which student scored highest in Science", "how many students are in 8B", "what is the class average") can be answered from a single table alone.

Two small tables to work with

Here is the admission register as a table called Students. It has 5 rows.

Students
student_id | name  | class
-----------|-------|------
1          | Aditi | 8A
2          | Rohan | 8B
3          | Meera | 8A
4          | Kabir | 8B
5          | Zara  | 8A

And here is the combined marks register from all subject teachers, called Marks. It has 6 rows — one row per test score, not one row per student.

Marks
mark_id | student_id | subject | marks
--------|------------|---------|------
1       | 1          | Math    | 88
2       | 1          | Science | 92
3       | 2          | Math    | 75
4       | 3          | Math    | 95
5       | 3          | Science | 89
6       | 4          | Math    | 60

Look closely at what is missing. Zara (student_id 5) has no rows at all in Marks — she joined the school after the last test. Rohan (student_id 2) has only one row — he has a Math score but no Science score yet, perhaps because he was absent for that test. These two gaps are not mistakes; real data is almost always incomplete like this, and a big part of learning SQL joins is learning exactly what happens to incomplete rows.

The column student_id is what makes these two tables connect. In Students, it uniquely identifies one student — this is called the primary key, because no two rows can share it. In Marks, the same column reappears, but here it is called a foreign key: it points back to a row in a different table. Whenever you see the same value in two tables like this, that is the "hook" a join grabs onto.

The INNER JOIN: keep only what matches on both sides

Suppose you want a report showing each mark alongside the student's actual name, not just their id number. You need columns from both tables, so you write:

SELECT Students.name, Marks.subject, Marks.marks
FROM Students
INNER JOIN Marks ON Students.student_id = Marks.student_id;

To understand what the database engine actually does, picture it going through Marks one row at a time and, for each row, searching Students for a matching student_id:

• Marks row 1 has student_id 1 → matches Students row for Aditi → output: Aditi, Math, 88
• Marks row 2 has student_id 1 → matches Aditi again → output: Aditi, Science, 92
• Marks row 3 has student_id 2 → matches Rohan → output: Rohan, Math, 75
• Marks row 4 has student_id 3 → matches Meera → output: Meera, Math, 95
• Marks row 5 has student_id 3 → matches Meera again → output: Meera, Science, 89
• Marks row 6 has student_id 4 → matches Kabir → output: Kabir, Math, 60

The final result has exactly 6 rows, one for every row that existed in Marks, because every single mark happened to belong to some student who exists in Students. Notice who is silent in this output: Zara does not appear anywhere, because she has zero rows in Marks to match against. This is the core rule of an INNER JOIN — a row survives only if it can find a partner on the other side. No partner, no row, no matter how important that row was in its own table.

Common misconception: listing two tables is not the same as joining them

A very common mistake, especially when students first see multiple tables in a query, is to write this:

SELECT * FROM Students, Marks;

This looks like it should "combine" the tables, and it does run without an error — but it does something almost useless: it pairs every row of Students with every row of Marks, regardless of whether the student_ids match. This is called a cartesian product. With 5 students and 6 marks rows, that query returns 5 × 6 = 30 rows, and most of them are nonsense — for example it will happily pair Kabir's row with Aditi's Science mark of 92, even though that mark has nothing to do with Kabir. The comma between table names does not mean "join correctly"; it only means "combine every possible pair." The ON Students.student_id = Marks.student_id condition in a real JOIN is not optional decoration — it is the filter that throws away the 24 wrong pairings and keeps only the 6 correct ones. If you ever see a query joining tables with a comma and no matching WHERE condition, that is very likely a bug, not a shortcut.

The LEFT JOIN: keep everyone from one side, matched or not

Sometimes the whole point of the report is to find the gaps — for instance, "list every student, and show their marks if they have any." An INNER JOIN would silently drop Zara, which is exactly the wrong behaviour here. Instead you use:

SELECT Students.name, Marks.subject, Marks.marks
FROM Students
LEFT JOIN Marks ON Students.student_id = Marks.student_id;

A LEFT JOIN starts from the table on the left (Students) and guarantees every one of its rows appears in the output at least once. It goes student by student instead of mark by mark:

• Aditi (id 1) has 2 matches in Marks → 2 output rows: (Aditi, Math, 88) and (Aditi, Science, 92)
• Rohan (id 2) has 1 match → 1 output row: (Rohan, Math, 75)
• Meera (id 3) has 2 matches → 2 output rows: (Meera, Math, 95) and (Meera, Science, 89)
• Kabir (id 4) has 1 match → 1 output row: (Kabir, Math, 60)
• Zara (id 5) has 0 matches → she still gets 1 output row, but with NULL in place of subject and marks: (Zara, NULL, NULL)

The total is 7 rows, not 6. That extra row for Zara is the entire reason LEFT JOIN exists: it never lets an unmatched row from the left table vanish, it just fills the missing right-side columns with NULL, which means "no value recorded," not zero and not blank text. Confusing NULL with 0 is another common error — if you later tried to compute Zara's average marks, treating her NULL as a 0 would unfairly drag her average down to something she never actually scored.

Aggregate functions: collapsing many rows into one number

A join produces a wider table by combining columns; an aggregate function produces a shorter table by combining rows. The five you will use constantly are COUNT, SUM, AVG, MIN, and MAX. Applied directly to the whole Marks table with no grouping:

SELECT COUNT(*) AS total_entries, SUM(marks) AS total_marks,
       AVG(marks) AS average_marks, MIN(marks) AS lowest, MAX(marks) AS highest
FROM Marks;

Trace it by hand using the 6 mark values 88, 92, 75, 95, 89, 60: COUNT(*) simply counts rows, giving 6. SUM adds every value: 88+92+75+95+89+60 = 499. AVG divides that sum by the count: 499 / 6 = 83.166..., which a database typically shows as 83.17. MIN scans for the smallest value, 60, and MAX scans for the largest, 95. One row comes back, holding all five numbers — the whole table has been collapsed into a single summary row.

GROUP BY: aggregating separately within each category

A single overall average is often too blunt. A more useful question is "what is the average score in each subject, separately?" This is where GROUP BY comes in — it sorts rows into buckets by a chosen column, and then runs the aggregate function once per bucket instead of once for the whole table.

SELECT subject, AVG(marks) AS average_marks, COUNT(*) AS num_scores
FROM Marks
GROUP BY subject;

Picture the engine sorting the 6 rows of Marks into two buckets by subject. The Math bucket collects 88, 75, 95, 60 — four values, summing to 318, giving an average of 318 / 4 = 79.5. The Science bucket collects 92, 89 — two values, summing to 181, giving an average of 181 / 2 = 90.5. The result is exactly two rows, one per distinct subject value:

subject | average_marks | num_scores
--------|---------------|-----------
Math    | 79.5          | 4
Science | 90.5          | 2

Every column you SELECT alongside a GROUP BY must either be the grouping column itself or the result of an aggregate function — you cannot ask for an individual student's name here, because within the Math bucket there are four different students and SQL has no way to pick just one name to show. This restriction trips up many beginners: if a query fails with an error like "column must appear in GROUP BY clause," it almost always means you asked for a raw column that isn't a group key alongside an aggregate.

Joining and grouping together: the real power move

The most useful queries combine both ideas: join first to pull in a column you need from another table, then group by that column to summarise. Suppose the school wants each student's total marks and how many subjects they have been tested in so far:

SELECT Students.name, SUM(Marks.marks) AS total_marks, COUNT(Marks.marks) AS subjects_taken
FROM Students
INNER JOIN Marks ON Students.student_id = Marks.student_id
GROUP BY Students.name;

Think of this as running in two clear stages, even though the database may optimise the actual execution differently. Stage one, the join, produces the same 6-row combined table from earlier (Aditi/Math/88, Aditi/Science/92, Rohan/Math/75, Meera/Math/95, Meera/Science/89, Kabir/Math/60). Stage two groups those 6 rows by name: Aditi's bucket holds 88 and 92, giving SUM = 180 and COUNT = 2. Rohan's bucket holds only 75, giving SUM = 75 and COUNT = 1. Meera's bucket holds 95 and 89, giving SUM = 184 and COUNT = 2. Kabir's bucket holds only 60, giving SUM = 60 and COUNT = 1. Zara never entered this pipeline at all — the INNER JOIN excluded her in stage one because she has no marks row, so she never gets a bucket. The final result has 4 rows, one per student who has at least one recorded mark.

Now try grouping by class instead of by name, to see the school-wide picture:

SELECT Students.class, AVG(Marks.marks) AS class_average
FROM Students
INNER JOIN Marks ON Students.student_id = Marks.student_id
GROUP BY Students.class;

Section 8A contains Aditi and Meera, whose combined mark values are 88, 92, 95, 89 — summing to 364 across 4 marks, giving a class average of 364 / 4 = 91. Section 8B contains Rohan and Kabir, whose combined mark values are 75 and 60 — summing to 135 across 2 marks, giving a class average of 135 / 2 = 67.5. Zara belongs to 8A on paper, but again the INNER JOIN drops her before grouping even begins, since she contributes zero mark rows. This is worth sitting with: the choice between INNER JOIN and LEFT JOIN, made at the very start of the query, can quietly change an aggregate result much later — a school administrator comparing "8A average = 91" against a different report that used LEFT JOIN (which would need to decide how to treat Zara's NULL, usually by ignoring it in AVG too, since aggregate functions skip NULLs by default) needs to know which join was used before trusting the number.

Filtering groups with HAVING, not WHERE

Suppose you only want to see subjects whose average score exceeds 80. It is tempting to write:

SELECT subject, AVG(marks) AS average_marks
FROM Marks
WHERE AVG(marks) > 80
GROUP BY subject;

This fails, and understanding why is important, not just memorising the fix. WHERE filters individual raw rows before any grouping or aggregation happens — at the point WHERE runs, the database is still looking at single mark values like 88 or 75, and AVG(marks) has not been computed yet for anything, so there is nothing for WHERE to compare 80 against. The correct clause is HAVING, which runs after GROUP BY has formed the buckets and the aggregate functions have already produced their per-bucket answers:

SELECT subject, AVG(marks) AS average_marks
FROM Marks
GROUP BY subject
HAVING AVG(marks) > 80;

Recall the two bucket averages computed earlier: Math averaged 79.5 and Science averaged 90.5. HAVING now checks each finished average against the condition > 80. Math's 79.5 fails the test and is dropped. Science's 90.5 passes and survives. The final output is a single row: Science, 90.5. The rule to keep permanently: use WHERE to filter which raw rows are allowed into the calculation in the first place (for example, only marks from this term), and use HAVING to filter which already-calculated group results are worth showing.

The order SQL actually thinks in

You type a query in the order SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING — but the database does not execute it in that reading order. It logically works through the clauses like this: first FROM/JOIN assembles the combined rows from all tables involved; then WHERE throws out raw rows that fail a condition; then GROUP BY sorts whatever survived into buckets; then HAVING throws out whole buckets that fail a condition; only then does SELECT pick which columns and aggregate results to actually display; and finally ORDER BY sorts the finished result for presentation. This explains, without any hand-waving, exactly why WHERE cannot see an aggregate value (it runs before grouping exists) while HAVING can (it runs after). It also explains why you can GROUP BY a column and still filter on it with WHERE before grouping even starts — WHERE and GROUP BY are simply operating at different stages of the same pipeline.

Watching a join happen

The diagram below shows the actual Students and Marks tables from this chapter side by side. Solid green lines connect a student to every mark row that matches their student_id — these are the rows an INNER JOIN keeps. The dashed red line shows Zara, who has no matching row at all; a LEFT JOIN would still keep her, filling the missing subject and marks with NULL, while an INNER JOIN would drop her entirely.

Test your understanding

Work these out by hand using only the Students and Marks tables from this chapter, tracing them the same way the examples above were traced. Do not just guess — write down which rows survive at each stage.

1. Write the query that lists every student's name together with every subject and mark they have, but this time make sure Zara still appears in the output even with no marks. Which join keyword must you use, and what will her subject and marks columns contain?
2. A teacher writes SELECT Students.name, Marks.subject FROM Students, Marks WHERE Students.student_id = Marks.student_id; without ever typing the word JOIN. Does this return the correct 6 rows, the same as a proper INNER JOIN, or does it return the 30-row cartesian product? Explain what the WHERE clause is doing here.
3. Using GROUP BY on Students.student_id (not name) joined with Marks, write a query that returns each student's highest single mark using MAX. What result would Meera's row show, and why would Zara never appear in this result even though student_id is unique for her too?
4. A query groups marks by subject and adds HAVING COUNT(*) >= 4. Using the two subject buckets computed earlier in this chapter (Math with 4 entries, Science with 2 entries), which subject, if any, survives this HAVING condition?
5. Explain in one or two sentences why WHERE COUNT(*) > 1 is invalid SQL but HAVING COUNT(*) > 1 is valid, referring to the actual order in which FROM, WHERE, GROUP BY, and HAVING are processed.

Answer notes: (1) LEFT JOIN, with NULL in both the subject and marks columns for Zara. (2) It returns the correct 6 rows — this old-style comma syntax first builds the full 30-row cartesian product and then the WHERE clause discards the 24 rows where the ids don't match, leaving the same 6 correct pairings an INNER JOIN would give directly. (3) Meera's row shows MAX = 95 (her two marks are 95 and 89); Zara never appears because the join happens before the grouping, and she has zero rows to contribute to any bucket in the first place. (4) Only Math survives, since Math has 4 mark entries (meeting >= 4) while Science has only 2. (5) WHERE runs before any grouping exists, so no COUNT has been computed yet for it to compare against; HAVING runs after GROUP BY has already produced a COUNT for each bucket, so there is an actual number to test.

Summary

Relational data is deliberately split across small tables connected by shared id columns, a primary key on one side and a matching foreign key on the other, so that INNER JOIN can stitch rows together wherever both sides agree, silently dropping anything on either side that has no partner. LEFT JOIN changes that rule for one named table, keeping every one of its rows and filling in NULL where no partner exists, which matters whenever "no data yet" is itself meaningful information, as it was for Zara. Aggregate functions — COUNT, SUM, AVG, MIN, MAX — collapse many rows into one summary number, and GROUP BY lets that collapse happen separately within each category instead of across the whole table at once, which is what makes per-subject or per-class averages possible. WHERE and HAVING look similar but act at different points in the pipeline: WHERE removes raw rows before any grouping happens, so it can never see an aggregate result, while HAVING removes whole groups after their aggregates have already been calculated. The biggest habit to build from this chapter is to always ask two questions before trusting any joined, aggregated number: which join was used, and therefore which rows were silently included or excluded before the counting even began?

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.