SQL Joins, Subqueries & Aggregate Functions in DBMS: Easy Examples

DBMS Chapter 12 · SQL Relationship & Analysis Atlas

SQL Joins, Subqueries & Aggregate Functions in DBMS: Easy Examples

SQL joins combine related rows from multiple tables, subqueries let one query use the result of another query, and aggregate functions summarize many rows into useful values. In this chapter, you will learn INNER, LEFT, RIGHT, FULL, CROSS and SELF JOIN, join conditions and duplicate-row problems, scalar and multi-row subqueries, correlated subqueries, IN and EXISTS, COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING, NULL behavior and combined real queries. Every concept uses the same small college database so you can understand the logic instead of memorising disconnected syntax.

Direct answer first Join visuals Subquery nesting GROUP BY + HAVING Solved examples Checked: 18 September 2026

Quick answer for revision

What Are Joins, Subqueries and Aggregate Functions?

JOIN Combines related rows from two or more tables using a matching condition.

Subquery A query written inside another SQL query. Its result helps the outer query decide what to return.

Aggregate Function Summarizes many rows into one value per result group, such as COUNT, SUM or AVG.

Shortcut #1: Connect → Ask inside → Summarize

JOIN = connect tables · Subquery = query inside query · Aggregate = summarize rows

If SELECT, WHERE, ORDER BY and conditions are not comfortable yet, revise SQL SELECT Queries for Beginners first. This chapter is the next step after basic query writing.

One database for every example

College Database Used in This Chapter

STUDENT

student_id	name	department_id	marks
S01	Asha	D01	85
S02	Ravi	D02	72
S03	Neha	D01	91
S04	Aman	NULL	66
S05	Riya	D03	78

DEPARTMENT

department_id	department_name
D01	Computer Science
D02	Electronics
D04	Civil

ENROLLMENT

student_id	course_id	grade
S01	C101	A
S01	C102	B
S02	C101	B
S03	C101	A
S05	C103	A

Notice that S04 has no department, D04 has no student, and S05 points to D03 even though D03 is intentionally absent from the sample DEPARTMENT table. That gives us useful cases for understanding outer joins and missing matches.

See the three ideas visually

Interactive SQL Relationship Atlas

JOIN: connect related tables

SELECT s.name, d.department_name FROM Student s INNER JOIN Department d ON s.department_id = d.department_id;

INNER JOIN keeps only rows where the join condition finds a match in both tables.

Result idea

name	department_name
Asha	Computer Science
Ravi	Electronics
Neha	Computer Science

Part 01 · joins

What Is a JOIN in SQL?

A SQL JOIN combines columns from related rows in two or more tables. The ON condition tells SQL how one table is connected to the other.

STUDENT

S01 · Asha · D01
S02 · Ravi · D02
S03 · Neha · D01

Matching key Student.department_id = Department.department_id

DEPARTMENT

D01 · Computer Science
D02 · Electronics
D04 · Civil

The foreign key and referenced key explain why the tables are related. If that relationship is not clear yet, revise Integrity Constraints in DBMS.

Shortcut #2: JOIN = Match key values, then show columns from both tables

Join type 01

INNER JOIN: Keep Only Matching Rows

SELECT s.student_id,
       s.name,
       d.department_name
FROM Student AS s
INNER JOIN Department AS d
  ON s.department_id = d.department_id;

student_id	name	department_name
S01	Asha	Computer Science
S02	Ravi	Electronics
S03	Neha	Computer Science

S04 is not included because department_id is NULL. S05 is not included because D03 has no matching row in our sample DEPARTMENT table.

Shortcut #3: INNER = common matches only

Join type 02

LEFT JOIN: Keep Every Row From the Left Table

SELECT s.name,
       d.department_name
FROM Student AS s
LEFT JOIN Department AS d
  ON s.department_id = d.department_id;

LEFT JOIN keeps every Student row. If no Department match exists, the right-side Department columns become NULL.

name	department_name
Asha	Computer Science
Ravi	Electronics
Neha	Computer Science
Aman	NULL
Riya	NULL

Find students without a matching department

SELECT s.student_id, s.name
FROM Student AS s
LEFT JOIN Department AS d
  ON s.department_id = d.department_id
WHERE d.department_id IS NULL;

This pattern is often called an anti-join pattern because it finds left-side rows with no match.

Join type 03 and 04

RIGHT JOIN and FULL OUTER JOIN

RIGHT JOIN

SELECT s.name,
       d.department_name
FROM Student AS s
RIGHT JOIN Department AS d
  ON s.department_id = d.department_id;

RIGHT JOIN keeps every row from the right table. Department D04 appears even though no student matches it.

FULL OUTER JOIN

SELECT s.name,
       d.department_name
FROM Student AS s
FULL OUTER JOIN Department AS d
  ON s.department_id = d.department_id;

FULL OUTER JOIN keeps matched rows plus unmatched rows from both sides.

DBMS note: support differs. For example, MySQL does not provide a direct FULL OUTER JOIN keyword, while PostgreSQL and SQL Server support it. In a MySQL lab, your teacher may show an equivalent pattern using UNION.

Other important join forms

CROSS JOIN and SELF JOIN

CROSS JOIN

Pairs every row from the first table with every row from the second table.

SELECT s.name, c.course_id
FROM Student s
CROSS JOIN Course c;

If Student has 5 rows and Course has 3 rows, the result has 15 combinations.

SELF JOIN

Joins a table to itself using two aliases.

SELECT a.name, b.name
FROM Employee a
JOIN Employee b
  ON a.manager_id = b.employee_id;

This is useful for hierarchical data such as employee-manager relationships.

A join students often misuse

NATURAL JOIN vs Explicit JOIN

A NATURAL JOIN automatically matches columns with the same names. It may look shorter, but it can become risky if schemas change or same-named columns have different meanings.

SELECT *
FROM Student
NATURAL JOIN Department;

For learning and production readability, an explicit JOIN ... ON condition is often clearer because you can see exactly which columns are being matched.

Shortcut #4: Explicit ON condition = easier to read and debug

Very important outer-join rule

ON vs WHERE in a JOIN

In INNER JOIN queries, moving a condition between ON and WHERE may sometimes produce the same result. With OUTER JOINs, it can completely change the meaning.

Keep all students, match only D01

SELECT s.name, d.department_name
FROM Student s
LEFT JOIN Department d
  ON s.department_id = d.department_id
 AND d.department_id = 'D01';

WHERE removes NULL outer rows

SELECT s.name, d.department_name
FROM Student s
LEFT JOIN Department d
  ON s.department_id = d.department_id
WHERE d.department_id = 'D01';

The second query filters after the join, so unmatched rows fail the WHERE condition. This can make a LEFT JOIN behave like a filtered match-only result.

Why joins suddenly create extra rows

Why Can a JOIN Create Duplicate-Looking Rows?

Suppose Asha is enrolled in two courses. Joining STUDENT with ENROLLMENT produces two rows for Asha because there are two matching Enrollment rows.

SELECT s.name, e.course_id
FROM Student s
JOIN Enrollment e
  ON s.student_id = e.student_id;

This is not a join error. It reflects a one-to-many relationship.

Shortcut #5: One parent + many matches = many result rows

Do not add DISTINCT automatically. First understand why the extra rows exist.

Part 02 · subqueries

What Is a Subquery in SQL?

A subquery is a SELECT query written inside another SQL statement. The inner query produces a value or set of values that the outer query uses.

Outer query

SELECT name, marks
FROM Student
WHERE marks > ( ...inner query... );

Inner query

SELECT AVG(marks)
FROM Student

Complete query:

SELECT name, marks
FROM Student
WHERE marks > (
  SELECT AVG(marks)
  FROM Student
);

The inner query finds the average marks. The outer query returns students whose marks are above that average.

Shortcut #6: Solve the inner query first

Main subquery types

Scalar, Single-Row, Multi-Row and Correlated Subqueries

Scalar SubqueryReturns one value, such as AVG(marks).

Single-Row SubqueryReturns one row. It may contain one or several columns depending on context and DBMS syntax.

Multi-Row SubqueryReturns several rows, commonly used with IN, EXISTS, ANY or ALL.

Correlated SubqueryReferences the current row of the outer query and is logically re-evaluated for outer rows.

Multi-row example with IN

SELECT name
FROM Student
WHERE student_id IN (
  SELECT student_id
  FROM Enrollment
  WHERE course_id = 'C101'
);

The inner query returns student IDs enrolled in C101. The outer query finds their names.

Existence tests

EXISTS and NOT EXISTS

EXISTS checks whether the subquery returns at least one row.

SELECT s.name
FROM Student s
WHERE EXISTS (
  SELECT 1
  FROM Enrollment e
  WHERE e.student_id = s.student_id
);

This returns students who have at least one Enrollment row.

Find students with no enrollment

SELECT s.name
FROM Student s
WHERE NOT EXISTS (
  SELECT 1
  FROM Enrollment e
  WHERE e.student_id = s.student_id
);

Shortcut #7: EXISTS asks “Is there at least one matching row?”

Subquery that depends on the outer row

Correlated Subquery Explained Simply

Find students whose marks are above the average marks of their own department:

SELECT s1.name,
       s1.department_id,
       s1.marks
FROM Student s1
WHERE s1.marks > (
  SELECT AVG(s2.marks)
  FROM Student s2
  WHERE s2.department_id = s1.department_id
);

Take one outer studentExample: Asha in D01.

Inner query uses Asha's departmentCalculate AVG marks for D01.

Compare Asha with that averageIf Asha's marks are higher, keep her row.

Repeat logically for other outer studentsThe inner condition depends on each outer row.

Database optimizers may transform correlated queries internally, but this row-by-row explanation is the easiest way to understand the logic.

Common interview and exam comparison

IN vs EXISTS: Which Should You Use?

IN

Easy to read when you are checking whether a value belongs to a returned list/set.

WHERE student_id IN (
  SELECT student_id
  FROM Enrollment
)

EXISTS

Natural when the question is whether at least one related row exists.

WHERE EXISTS (
  SELECT 1
  FROM Enrollment e
  WHERE e.student_id = s.student_id
)

Modern optimizers can often produce similar efficient plans. Choose the form that expresses the logic clearly, then inspect performance when working with real large datasets.

NULL warning: NOT IN can produce surprising UNKNOWN results if the subquery contains NULL. NOT EXISTS is often easier to reason about for “no matching row” logic.

Useful multi-row comparisons

ANY and ALL With Subqueries

ANY means the comparison must be true for at least one value returned by the subquery. ALL means it must be true for every returned value.

SELECT name, marks
FROM Student
WHERE marks > ALL (
  SELECT marks
  FROM Student
  WHERE department_id = 'D02'
);

This asks for students whose marks are greater than every marks value returned for D02.

ANY/ALL support and exact syntax should be checked in your DBMS, but the logical meaning is worth knowing for exams.

Part 03 · aggregate functions

Aggregate Functions in SQL

COUNTHow many rows or non-NULL values?

SUMTotal of numeric values.

AVGAverage of numeric non-NULL values.

MINSmallest value.

MAXLargest value.

COUNT(*)

SELECT COUNT(*) AS total_students
FROM Student;

COUNT(*) counts result rows.

COUNT(column)

SELECT COUNT(department_id)
FROM Student;

This counts non-NULL department_id values, so S04 is not counted.

COUNT(DISTINCT column)

SELECT COUNT(DISTINCT department_id)
FROM Student;

This counts distinct non-NULL department IDs.

Shortcut #8: COUNT(*) counts rows; COUNT(column) ignores NULL values

Numeric summaries

SUM, AVG, MIN and MAX With Examples

SELECT SUM(marks) AS total_marks,
       AVG(marks) AS average_marks,
       MIN(marks) AS lowest_marks,
       MAX(marks) AS highest_marks
FROM Student;

Aggregate functions usually ignore NULL input values, except that COUNT(*) counts rows regardless of whether individual columns contain NULL.

If every input value for an aggregate expression is NULL, the result behavior follows SQL aggregate rules and may be NULL rather than zero. Do not automatically replace NULL with zero unless that matches the real meaning of your data.

Summarize each group separately

GROUP BY Explained Simply

GROUP BY divides rows into groups and lets aggregate functions calculate one result per group.

Average marks by department

SELECT department_id,
       AVG(marks) AS average_marks
FROM Student
GROUP BY department_id;

Instead of one average for the whole table, SQL calculates one average for each department_id group.

Read Student rowsStart with all candidate rows.

Create groupsRows with the same department_id belong to one group.

Run AVG inside each groupEach group gets its own average.

Return one result row per groupdepartment_id + average_marks.

Shortcut #9: GROUP BY = make buckets, aggregate = summarize each bucket

A rule that causes many SQL errors

Which Columns Can Appear With GROUP BY?

In standard SQL-style grouping, a selected expression should generally be either:

GroupedIncluded in the GROUP BY key.

AggregatedCalculated using COUNT, SUM, AVG, MIN, MAX or another aggregate.

Clear grouped query

SELECT department_id,
       AVG(marks)
FROM Student
GROUP BY department_id;

Problematic idea

SELECT department_id,
       name,
       AVG(marks)
FROM Student
GROUP BY department_id;

Which one name should represent an entire department group? Strict SQL modes reject this unless special functional-dependency rules apply.

Filter groups, not normal rows

HAVING vs WHERE

WHERE

Filters rows before grouping.

WHERE marks >= 60

HAVING

Filters groups after aggregate results are calculated.

HAVING AVG(marks) >= 80

Departments with average marks at least 80

SELECT department_id,
       AVG(marks) AS average_marks
FROM Student
GROUP BY department_id
HAVING AVG(marks) >= 80;

WHERE and HAVING together

SELECT department_id,
       AVG(marks) AS average_marks
FROM Student
WHERE marks >= 60
GROUP BY department_id
HAVING COUNT(*) >= 2;

WHERE first removes students below 60. GROUP BY then forms department groups. HAVING finally keeps only groups containing at least two remaining students.

Shortcut #10: WHERE = rows, HAVING = groups

Where SQL becomes useful for reports

JOIN + GROUP BY + Aggregate Functions Together

Question: Show each department name and number of matched students

SELECT d.department_name,
       COUNT(s.student_id) AS student_count
FROM Department d
LEFT JOIN Student s
  ON s.department_id = d.department_id
GROUP BY d.department_id, d.department_name
ORDER BY student_count DESC;

Why COUNT(s.student_id) instead of COUNT(*)? For a LEFT JOIN, Department D04 still creates an output row even without a matching student. COUNT(*) would count that output row, while COUNT(s.student_id) counts only non-NULL matched student IDs.

Shortcut #11: Outer join count? Decide exactly what you want to count

Aggregate inside a subquery

Subqueries + Aggregate Functions Together

Students above the overall average

SELECT name, marks
FROM Student
WHERE marks > (
  SELECT AVG(marks)
  FROM Student
);

Students with the maximum marks

SELECT name, marks
FROM Student
WHERE marks = (
  SELECT MAX(marks)
  FROM Student
);

This second pattern safely returns all students tied for the maximum value.

Classic interview-style query

Second-Highest Distinct Marks Using a Subquery

SELECT MAX(marks) AS second_highest_marks
FROM Student
WHERE marks < (
  SELECT MAX(marks)
  FROM Student
);

Inner queryFind the highest marks.

Outer WHEREIgnore all rows equal to that highest value.

Outer MAXFind the largest value among the remaining marks.

If fewer than two distinct marks values exist, the result can be NULL.

Decision help

JOIN vs Subquery: Which One Should You Use?

JOIN is natural when...

You need columns from multiple related tables in the same result, such as student name plus department name.

Subquery is natural when...

You need another query to calculate a comparison value, list, or existence test, such as marks above average.

Many problems can be written in more than one way. Modern DBMS optimizers may rewrite equivalent forms. For learning, choose the form that makes the logic easiest to explain, then test performance on real data if optimization matters.

Understand why clauses behave differently

Logical Query Processing Order With JOIN and GROUP BY

A useful conceptual order for a grouped query is:

1. FROM + JOIN Build the source row set.

2. WHERE Remove rows before grouping.

3. GROUP BY + HAVING Create groups and filter groups.

4. SELECT Create output expressions.

5. DISTINCT Remove duplicate result rows if requested.

6. ORDER BY Sort the final result.

This is a semantic learning model. The physical execution plan can be different because the optimizer may reorder operations while preserving the query's meaning.

One complete solved problem

Complete Example: Departments With Strong Students

Question

Show department names where at least two matched students exist and their average marks are above 75. Sort by average marks from high to low.

Join Department and StudentWe need the readable department name and student marks.

Group by departmentEach department becomes one summary group.

Count students and calculate averageUse COUNT and AVG.

Filter groupsHAVING count at least 2 and average above 75.

Sort resultHighest average first.

SELECT d.department_name,
       COUNT(s.student_id) AS student_count,
       AVG(s.marks) AS average_marks
FROM Department d
JOIN Student s
  ON s.department_id = d.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(s.student_id) >= 2
   AND AVG(s.marks) > 75
ORDER BY average_marks DESC;

This one query combines the three main skills from the chapter: relationship handling with JOIN, row summarization with aggregate functions, and group filtering with HAVING.

Mistakes that cause wrong results

Common JOIN, Subquery and Aggregate Mistakes

Missing JOIN conditionCan create a Cartesian-product style explosion of rows.

Joining on the wrong columnsMatching names instead of stable keys can produce incorrect relationships.

Using WHERE on the right side of LEFT JOIN carelesslyCan remove unmatched rows and change the intended outer-join behavior.

Adding DISTINCT to hide duplicate-looking rowsUnderstand the relationship cardinality first.

Using = with a multi-row subqueryUse IN, EXISTS, ANY or ALL when the subquery can return multiple values.

Using NOT IN with nullable subquery resultsNULL can make the condition UNKNOWN.

Confusing COUNT(*) and COUNT(column)COUNT(column) ignores NULL values.

Putting aggregate conditions in WHEREUse HAVING for group-level aggregate conditions.

Selecting random non-grouped columnsEvery selected expression should follow valid grouping rules.

Assuming all JOIN syntax is supported everywhereFULL OUTER JOIN support differs across DBMS products.

Connect practical SQL with DBMS theory

How These SQL Topics Connect to Relational Theory

JOIN comes directly from the idea of combining related relations. Aggregate functions and GROUP BY are practical SQL extensions for summarizing data. Subqueries fit SQL's declarative style: you describe a result condition and let the DBMS decide an execution plan.

JOINConnects naturally with relational-algebra join operations.

SubqueryMatches SQL's declarative “what result?” style.

GROUP BYBuilds groups for aggregation in practical SQL.

HAVINGApplies conditions to grouped results.

For theory, revise Relational Algebra in DBMS and Relational Calculus. Those chapters explain the ideas behind SQL query transformation and declarative querying.

Write according to marks

How to Write These Topics in Semester Exams

2 marks

Definition + one query

Define the asked concept directly: JOIN, subquery, COUNT or GROUP BY, then give one short syntax example.

5 marks

Explain with two tables

For JOIN, draw parent and child tables. For subquery, show inner and outer query. For aggregates, show input rows and summarized output.

8-10 marks

Compare and solve

Explain join types, subquery types, aggregate functions, GROUP BY, HAVING and one combined solved query.

For writing clean university answers, use How to Write High-Scoring Answers in University Semester Exams. For topic priority, use the previous-year-paper method.

Practice questions with answers

Practice Questions With Answers

1. What is an INNER JOIN?

INNER JOIN returns rows where the join condition finds matching rows in both participating table sources.

2. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN keeps only matching rows. LEFT JOIN keeps every row from the left table and uses NULL values for right-side columns when no match exists.

3. What is a subquery?

A subquery is a SELECT query written inside another SQL statement. Its result is used by the outer query.

4. What is a correlated subquery?

A correlated subquery references values from the current outer-query row, so its logic depends on the outer row.

5. What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts result rows. COUNT(column) counts only rows where that column expression is not NULL.

6. What does GROUP BY do?

GROUP BY divides rows into groups with the same grouping-key values so aggregate functions can calculate one summary per group.

7. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters groups after GROUP BY and aggregate calculations.

8. Why can a JOIN create repeated student names?

If one student matches several child rows, the joined result contains one result row for each match. That is normal for a one-to-many relationship.

9. When should EXISTS be used?

EXISTS is useful when the question is whether at least one related row exists for the current outer row.

10. Why can NOT IN be risky with NULL?

If the compared subquery contains NULL, SQL's three-valued logic can make NOT IN evaluate to UNKNOWN in cases beginners do not expect. NOT EXISTS is often clearer for no-match logic.

60-second revision

Joins, Subqueries & Aggregates One-Minute Revision

INNER JOINOnly matching rows.

LEFT JOINAll left rows + matches.

RIGHT JOINAll right rows + matches.

FULL OUTER JOINMatched + unmatched rows from both sides.

CROSS JOINEvery combination.

SELF JOINA table joined to itself.

SubqueryQuery inside another query.

Correlated SubqueryInner query depends on outer row.

EXISTSAt least one matching row exists.

COUNTCount rows or non-NULL values.

SUM / AVGTotal and average.

MIN / MAXSmallest and largest.

GROUP BYMake summary groups.

HAVINGFilter grouped results.

Ultimate memory line

JOIN connects · Subquery asks inside · Aggregate summarizes · GROUP BY groups · HAVING filters groups

Frequently asked questions

Frequently Asked Questions

What is the easiest way to understand SQL joins?

Think of a join as matching key values between related tables, then showing columns from the matching rows. INNER JOIN keeps matches only, while outer joins can also keep unmatched rows.

What is a subquery in SQL?

A subquery is a SELECT query inside another query. It can return one value, one row or several rows depending on how the outer query uses it.

What are the main aggregate functions in SQL?

The main beginner aggregate functions are COUNT, SUM, AVG, MIN and MAX.

What is the difference between GROUP BY and HAVING?

GROUP BY creates groups of rows. HAVING filters those groups after aggregate calculations.

What is the difference between JOIN and subquery?

A JOIN is natural for combining related table columns in one result, while a subquery is natural when one query needs another query's value, list or existence result. Some problems can be written either way.

What should I study after joins, subqueries and aggregate functions?

Continue with CTEs, window functions, set operations, views, indexes, transactions and query optimization.

Next chapter

What Should You Learn Next?

Next: CTEs, Window Functions & Advanced SQL Query Patterns

You now know how to connect tables, nest queries and summarize groups. The next logical step is Common Table Expressions (CTEs), ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG and windowed aggregates.

After that, study views, indexes, transactions and query optimization. For video revision after reading the written chapter, use the DBMS YouTube study guide as a secondary resource.