🔗 Grouping Records, Joins in SQL

After learning to query and filter a single table, this chapter adds two tools: GROUP BY, which summarises rows in groups using aggregate functions (with HAVING to filter the groups), and joins, which combine data from two or more tables, such as the Equi-Join and the Natural Join.

12.1 🏁 Introduction

In Chapter 11 we learnt to query a single table. But real-world databases almost always need answers like:

• "How many employees are in each department?"
• "What is the average salary per city?"
• "Show me each employee's name along with their department name from a separate table."

These questions need two new powerful tools:

graph LR
    G["📊 GROUP BY\nSummarise rows\ninto groups"]
    J["🔗 Joins\nCombine data from\nmultiple tables"]
    POWER["💪 Real-World\nSQL Power!"]

    G --> POWER
    J --> POWER

    style G fill:#2196F3,color:#fff
    style J fill:#9C27B0,color:#fff
    style POWER fill:#4CAF50,color:#fff

Think of it this way: Chapter 11 was like reading individual student report cards. Chapter 13 lets you summarise the whole class (GROUP BY) and combine the report card with the attendance register (Joins)!

12.2 ⚙️ Types of SQL Functions

Before diving into GROUP BY, it helps to understand the two broad categories of SQL functions:

graph TD
    F["SQL FUNCTIONS"]
    F1["🔢 Single-Row Functions\nOne input row → One output row\nOutput has same number of rows as input"]
    F2["📊 Aggregate Functions\nMany input rows → One output value\nCollapses many rows into a summary"]

    F --> F1
    F --> F2

    F1 --> EX1["UPPER(Name)\nUPPER('priya') → 'PRIYA'\nfor EVERY row"]
    F2 --> EX2["AVG(Salary)\nAll salaries → one average\nfor the WHOLE group"]

    style F fill:#FF9800,color:#fff
    style F1 fill:#2196F3,color:#fff
    style F2 fill:#9C27B0,color:#fff

Aggregate Functions — Quick Reference:

12.3 📊 Grouping Result — GROUP BY

Without GROUP BY, aggregate functions summarise the entire table into one row. With GROUP BY, they summarise each group separately — giving you one result row per group!

The Problem Without GROUP BY:

-- without_group_by.sql
SELECT COUNT(*) FROM Employee;
-- Output: 6 (total employees — just ONE number, not very useful!)

The Solution With GROUP BY:

-- with_group_by.sql
SELECT Dept, COUNT(*) AS TotalEmployees
FROM Employee
GROUP BY Dept;

Output:

graph TD
    ALL["📋 All 6 Employee Rows"]
    GRP["GROUP BY Dept"]
    G1["🏢 Sales Group\nPriya, Anjali\nCOUNT = 2"]
    G2["💻 IT Group\nArjun, Rohan\nCOUNT = 2"]
    G3["👥 HR Group\nSita, Vikram\nCOUNT = 2"]

    ALL --> GRP
    GRP --> G1
    GRP --> G2
    GRP --> G3

    style GRP fill:#FF9800,color:#fff
    style G1 fill:#4CAF50,color:#fff
    style G2 fill:#2196F3,color:#fff
    style G3 fill:#9C27B0,color:#fff

Our Sample Employee Table (used throughout this chapter):

More GROUP BY Examples:

-- group_by_examples.sql

-- Average salary per department:
SELECT Dept, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY Dept;

Notice: AVG(Salary) for Sales is 45000, not NULL-affected — because AVG ignores NULL. Only Priya's 45000 counts for Sales (Anjali is NULL).

-- Count employees per city:
SELECT City, COUNT(*) AS EmpCount
FROM Employee
GROUP BY City;

General GROUP BY Syntax:

SELECT column, aggregate_function(column)
FROM table_name
[WHERE condition]
GROUP BY column
[HAVING condition]
[ORDER BY column];

12.3.1 Nested Groups — Grouping on Multiple Columns 🪆

You can GROUP BY more than one column to create finer sub-groups — like grouping first by Department, then by City within each department.

Real-World Need: "How many employees are in each city, within each department?"

-- nested_groups.sql
SELECT Dept, City, COUNT(*) AS EmpCount
FROM Employee
GROUP BY Dept, City;

Output:

graph TD
    ALL["📋 All 6 Employees"]
    D1["💼 Sales Dept"]
    D2["💻 IT Dept"]
    D3["👥 HR Dept"]
    S1["Delhi → 1 emp\n(Priya)"]
    S2["Mumbai → 1 emp\n(Anjali)"]
    I1["Mumbai → 1 emp\n(Arjun)"]
    I2["Bangalore → 1 emp\n(Rohan)"]
    H1["Delhi → 2 emps\n(Sita, Vikram)"]

    ALL --> D1
    ALL --> D2
    ALL --> D3
    D1 --> S1
    D1 --> S2
    D2 --> I1
    D2 --> I2
    D3 --> H1

    style D1 fill:#4CAF50,color:#fff
    style D2 fill:#2196F3,color:#fff
    style D3 fill:#9C27B0,color:#fff

Another Example — Total salary per department per city:

-- nested_group_salary.sql
SELECT Dept, City, SUM(Salary) AS TotalSalary
FROM Employee
GROUP BY Dept, City;

12.3.2 Placing Conditions on Groups — HAVING Clause 🎯

WHERE filters individual rows before grouping. HAVING filters groups after grouping. You use HAVING when you want to show only groups that meet a certain condition.

Real-World Need: "Show only departments where the average salary is more than ₹50,000."

-- having_clause.sql
SELECT Dept, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY Dept
HAVING AVG(Salary) > 50000;

Output (only IT qualifies):

graph LR
    DATA["All Rows"]
    WHERE_STEP["WHERE\n(filters individual rows\nbefore grouping)"]
    GROUP_STEP["GROUP BY\n(creates groups)"]
    AGG_STEP["Aggregate\nFunctions\n(SUM, AVG etc.)"]
    HAVING_STEP["HAVING\n(filters groups\nafter grouping)"]
    RESULT["Final Result"]

    DATA --> WHERE_STEP
    WHERE_STEP --> GROUP_STEP
    GROUP_STEP --> AGG_STEP
    AGG_STEP --> HAVING_STEP
    HAVING_STEP --> RESULT

    style WHERE_STEP fill:#FF9800,color:#fff
    style HAVING_STEP fill:#F44336,color:#fff
    style GROUP_STEP fill:#2196F3,color:#fff

WHERE vs HAVING — The Critical Difference:

Using WHERE and HAVING Together:

-- where_and_having.sql
-- Show departments where average salary > 40000,
-- but only considering employees who joined before 2023

SELECT Dept, AVG(Salary) AS AvgSalary
FROM Employee
WHERE YEAR(DOJ) < 2023
GROUP BY Dept
HAVING AVG(Salary) > 40000;

Step-by-step:

1. WHERE first removes Sita Roy (2023) and Anjali Gupta (2023) — 4 rows remain
2. GROUP BY groups the remaining 4 rows by Dept
3. HAVING keeps only groups with AVG Salary > 40000

More HAVING Examples:

-- more_having_examples.sql

-- Departments with more than 1 employee:
SELECT Dept, COUNT(*) AS Total
FROM Employee
GROUP BY Dept
HAVING COUNT(*) > 1;

-- Cities where maximum salary exceeds 60000:
SELECT City, MAX(Salary) AS MaxSal
FROM Employee
GROUP BY City
HAVING MAX(Salary) > 60000;

12.3.3 Non-Group Expressions with GROUP BY 📐

When using GROUP BY, the SELECT list must follow strict rules — any column that is not in an aggregate function must be in the GROUP BY clause. This avoids ambiguity about which value to show.

Legal — column in GROUP BY:

-- legal_group_by.sql
SELECT Dept, COUNT(*) AS Total
FROM Employee
GROUP BY Dept;
-- ✅ Legal: Dept is in GROUP BY

Illegal — column NOT in GROUP BY:

-- illegal_group_by.sql
SELECT Dept, Name, COUNT(*) AS Total
FROM Employee
GROUP BY Dept;
-- ❌ ILLEGAL: Name is not in GROUP BY and not aggregated!
-- MySQL error: 'Name' is not in GROUP BY clause

Why is it illegal? If Sales has Priya and Anjali, which Name should MySQL show in the output row for Sales? It's ambiguous — MySQL has no way to know!

The fix — add Name to GROUP BY (creates finer groups):

-- fix_non_group.sql
SELECT Dept, Name, COUNT(*) AS Total
FROM Employee
GROUP BY Dept, Name;

12.4 🔗 Joins

So far, all queries used a single table. But real databases split data across multiple tables — a Join combines rows from two (or more) tables based on a related column between them.

Real-World Analogy: Imagine your school keeps student names in one register and their marks in another. To see "Priya's marks", someone needs to join the two registers using the Roll Number as the common link — that's exactly what SQL Joins do!

Our Two Sample Tables for Join Examples:

Table: Employee

Table: Department

12.4.1 Cartesian Product 🌐

A Cartesian Product (also called a Cross Join) combines every row of the first table with every row of the second table — producing all possible combinations.

-- cartesian_product.sql
SELECT * FROM Employee, Department;
-- OR equivalently:
SELECT * FROM Employee CROSS JOIN Department;

Result: 6 rows × 4 rows = 24 rows — every employee paired with every department!

graph LR
    E["Employee\n6 rows"]
    D["Department\n4 rows"]
    CP["Cartesian Product\n6 × 4 = 24 rows\n(Every combination!)"]

    E --> CP
    D --> CP

    style CP fill:#F44336,color:#fff

Sample Output (showing just a few rows):

12.4.2 Table Aliases 🏷️

When joining tables, column names from different tables may conflict (both tables might have a DeptId column). Table Aliases give tables short, temporary names to:

1. Distinguish columns from different tables
2. Make long queries shorter and more readable

-- table_aliases.sql
SELECT E.Name, E.Salary, D.DeptName
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId;
-- 'E' is an alias for Employee, 'D' is an alias for Department

Syntax Rules:

FROM Employee E         -- 'E' is the alias (no AS needed for table aliases)
FROM Employee AS E      -- Also valid with AS

12.4.3 Equi-Join and Natural Join 🤝

Equi-Join

An Equi-Join links two tables using an equality condition (=) on a common column — typically a Foreign Key in one table matching the Primary Key in another.

-- equi_join.sql
SELECT E.EmpId, E.Name, E.Salary, D.DeptName
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId;

Output (only rows where DeptId matches in both tables):

graph LR
    E["Employee Table\n(has DeptId FK)"]
    D["Department Table\n(has DeptId PK)"]
    MATCH["WHERE E.DeptId = D.DeptId\n🔗 Matching rows joined!"]
    RESULT["📊 Combined Result\nName + Salary + DeptName\nin one query!"]

    E --> MATCH
    D --> MATCH
    MATCH --> RESULT

    style MATCH fill:#4CAF50,color:#fff
    style RESULT fill:#2196F3,color:#fff

What happened to Department 'Finance' (D04)? Notice it's not in the output! Because no employee has DeptId = D04, there's no matching row — so Finance doesn't appear. This is a key property of Equi-Join (which is an Inner Join).

Alternative Modern Syntax (JOIN ... ON):

-- modern_join_syntax.sql
SELECT E.EmpId, E.Name, E.Salary, D.DeptName
FROM Employee E
JOIN Department D ON E.DeptId = D.DeptId;
-- Produces exactly the same result — cleaner, modern syntax

Natural Join

A Natural Join automatically joins tables on all columns with the same name and data type — no explicit join condition needed.

-- natural_join.sql
SELECT * FROM Employee NATURAL JOIN Department;
-- MySQL automatically joins on DeptId (the common column name)

Output: Same as Equi-Join above — MySQL finds DeptId as the common column name and joins on it automatically.

Equi-Join vs Natural Join — Quick Comparison:

12.4.4 Additional Search Conditions in Joins 🎯

Real queries often need both a Join condition AND a filter condition (using WHERE) at the same time. You simply add more conditions using AND.

Real-World Need: "Show employees who work in IT department, with their department location."

-- additional_search_conditions.sql
SELECT E.Name, E.Salary, D.DeptName, D.Location
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId
AND D.DeptName = 'IT';

Output:

The Logic:

WHERE E.DeptId = D.DeptId    ← Join condition (links the tables)
AND D.DeptName = 'IT'        ← Search condition (filters the result)

More Examples of Combined Conditions:

-- combined_join_conditions.sql

-- Show all employees earning above 50000 with their department name:
SELECT E.Name, E.Salary, D.DeptName
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId
AND E.Salary > 50000;

-- Show Delhi employees with their department location:
SELECT E.Name, E.City, D.DeptName, D.Location
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId
AND E.City = 'Delhi';

Output of last query:

SELECT E.Name, E.Salary, D.DeptName
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId
AND E.City = 'Delhi';

📐 Full SELECT Statement Order — The Complete Picture

Now that we have all clauses, here is the complete ORDER in which they must appear in a SELECT statement:

SELECT    column/aggregate/expression     -- WHAT to show
FROM      table(s)                        -- WHERE data comes from
WHERE     row_condition                   -- WHICH rows to include (before grouping)
GROUP BY  column(s)                       -- HOW to group the rows
HAVING    group_condition                 -- WHICH groups to include (after grouping)
ORDER BY  column [ASC|DESC]              -- HOW to sort the final result

The Processing Order (different from writing order!):

1. FROM — identify the table(s)
2. WHERE — filter individual rows
3. GROUP BY — group the remaining rows
4. HAVING — filter the groups
5. SELECT — compute and display the output columns
6. ORDER BY — sort the final result

⚠️ Common Errors and Misconceptions

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY

🎯 Sample Board Exam Questions

Q1: Very Short Answer [1 mark each]

a) Which clause is used to filter groups in SQL? → HAVING

b) What is a Cartesian Product of a 5-row table and a 3-row table? → 5 × 3 = 15 rows

c) Which type of Join automatically joins on all columns with the same name? → Natural Join

d) What does GROUP BY Dept do in a SELECT statement? → It groups all rows with the same department into a single group so aggregate functions can summarise each group separately.

e) Can you use an aggregate function like AVG() in a WHERE clause? → No — aggregate functions cannot be used in WHERE. Use HAVING instead.

Q2: Short Answer [2 marks]

Q: Differentiate between WHERE and HAVING clause with an example.

WHERE filters individual rows before grouping. It cannot use aggregate functions. SELECT Dept, AVG(Salary) FROM Employee WHERE City = 'Delhi' GROUP BY Dept; — WHERE here removes non-Delhi rows before grouping.

HAVING filters groups after GROUP BY. It can use aggregate functions. SELECT Dept, AVG(Salary) FROM Employee GROUP BY Dept HAVING AVG(Salary) > 50000; — HAVING here removes groups whose average salary is ≤ 50000.

Q3: Query Writing [3 marks]

Q: Write SQL queries based on the Employee and Department tables:

a) Display each department name and the number of employees in it.

SELECT D.DeptName, COUNT(*) AS TotalEmployees
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId
GROUP BY D.DeptName;

b) Display departments where total salary paid exceeds ₹80,000.

SELECT Dept, SUM(Salary) AS TotalSalary
FROM Employee
GROUP BY Dept
HAVING SUM(Salary) > 80000;

c) Display the name, salary, and department name of employees earning more than ₹50,000.

SELECT E.Name, E.Salary, D.DeptName
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId
AND E.Salary > 50000;

Q4: Output Based Question [3 marks]

Q: Based on the Employee table, what is the output of the following query?

SELECT City, COUNT(*) AS Total, MAX(Salary) AS MaxSal
FROM Employee
GROUP BY City
HAVING COUNT(*) > 1;

Output:

*(Bangalore has only 1 employee so it is filtered out by HAVING COUNT(*) > 1)*

Q5: Short Answer [2 marks]

Q: What is a Cartesian Product? Why should it be avoided in practice?

A Cartesian Product is produced when two tables are combined in a FROM clause without any Join condition — every row of the first table is paired with every row of the second table. If Employee has 6 rows and Department has 4 rows, the Cartesian Product has 24 rows. It should be avoided because it produces meaningless data (an employee paired with every department, even irrelevant ones) and can generate enormously large result sets that slow down the database.

✏️ Practice Problems

1. Write a query to display the average salary for each city, but only for cities where more than one employee works.

2. Write a query to find the total salary spent on each department. Display results in descending order of total salary.

3. Write a query to display the name and department name of all employees whose city is 'Mumbai', using an Equi-Join.

4. A table Student(RollNo, Name, Class, Marks) and Section(Class, SectionName, Teacher) are given. Write a query to display student names, their marks, and the teacher's name for all students scoring above 80.

5. Explain why SELECT Dept, Name, COUNT(*) FROM Employee GROUP BY Dept; produces an error. How would you fix it?

6. Write a query to display each department name along with the highest and lowest salary in that department.

7. Without running the query, predict how many rows SELECT * FROM Employee, Department; will return if Employee has 6 rows and Department has 4 rows.

8. Rewrite this Equi-Join using a Natural Join: SELECT E.Name, D.DeptName FROM Employee E, Department D WHERE E.DeptId = D.DeptId;

9. Write a query to display department names that have more than one employee earning above ₹40,000.

10. What is the difference between GROUP BY Dept and GROUP BY Dept, City? Explain with a real-world example using the Employee table.