Skip to main content

Joins: INNER, LEFT and Self Joins – SQL Server | Lesson 17

Published / updated

Introduction​

Normalization split the school's data into 22 tables, each storing one kind of fact. That keeps data correct, but no single table answers "who is in 10-A?". The student's name is in Students, the section is in Enrollments, and the section's name is in Sections.

A join puts tables back together for one query. In this lesson, you write the query behind the Class 10-A list from Lesson 2, and several more.

By the end of this lesson, you will be able to:

  • Join two or more tables with INNER JOIN and an ON condition.
  • Use table aliases to keep long queries readable.
  • Keep unmatched rows with LEFT JOIN, and recognize the NULLs it produces.
  • Join a table to itself.
  • Explain why a join can multiply rows.

Why this matters​

Almost every real screen needs a join:

  • A class list needs students and enrollments.
  • A report card needs marks, papers, subjects, and exams.
  • A fee receipt needs payments, invoices, enrollments, and students.

If you cannot join confidently, you cannot build any of those. Worse, a join written slightly wrong does not fail: it quietly returns duplicated or missing rows, and the report looks fine until someone counts.

The concept​

A join matches rows from two tables using a condition, usually "this table's foreign key equals that table's primary key".

Think of the school office matching fee receipts to the student register. Each receipt has a student's number, and the clerk finds that number in the register.

The clerk then writes the receipt and the student's name on one line. A join does the same for every row, in milliseconds.

Example: INNER JOIN​

Example
SELECT s.FirstName, e.RollNumber
FROM dbo.Students AS s
INNER JOIN dbo.Enrollments AS e ON e.StudentId = s.Id;
  • AS s and AS e are table aliases: short names for the tables in this query.
  • ON e.StudentId = s.Id is the join condition: which rows belong together.
  • INNER JOIN keeps only rows that have a match on both sides.

Example: kinds of joins​

JoinKeepsSMS example
INNER JOINOnly rows that match on both sidesStudents with their enrollments
LEFT JOINEvery row from the left table, matched or notEvery teacher, with a section if they are a class teacher
RIGHT JOINEvery row from the right tableRarely used; write it as a LEFT JOIN instead
FULL JOINEvery row from both sidesComparing two lists, such as an import and the database
CROSS JOINEvery row combined with every rowBuilding all class and subject combinations

When a LEFT JOIN finds no match, the right table's columns are NULL in that row.

In daily work, INNER JOIN and LEFT JOIN cover almost everything. The word INNER is optional, since a plain JOIN means the same, but writing it makes your intent obvious.

Apply it to the SMS​

Save the full script below as queries\017-joins.sql and run one query at a time.

Example: the Class 10-A list​

Four tables, joined one after another. Read it as: start from students, find their enrollments, then each enrollment's section and year:

Example
SELECT
    s.Id,
    s.AdmissionNumber,
    s.FirstName,
    s.LastName,
    e.RollNumber
FROM dbo.Students AS s
INNER JOIN dbo.Enrollments AS e ON e.StudentId = s.Id
INNER JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
INNER JOIN dbo.AcademicYears AS ay ON ay.Id = e.AcademicYearId
WHERE sec.ClassId = 10
  AND sec.Name = 'A'
  AND ay.IsCurrent = 1
ORDER BY e.RollNumber;
IdAdmissionNumberFirstNameLastNameRollNumber
1ADM-2017-0001AaravSharma1
2ADM-2017-0002DiyaPatel2
3ADM-2017-0003KabirSingh3
4ADM-2017-0004MeeraIyer4

This is exactly the data the API returned as JSON in Lesson 2. In Module 11, the API runs this query.

Example: Kabir's attendance​

Students, enrollments, academic years, and attendance:

FirstNameAttendanceDateStatusRemarks
Kabir2026-07-01AbsentFever
Kabir2026-07-02PresentNULL
Kabir2026-07-03PresentNULL

Example: a report card row​

Aarav's Unit Test 1 marks need four tables: Marks, ExamSubjects, Exams, and Subjects. This is the report card from Lesson 13, built with joins:

ExamSubjectMarksObtainedMaxMarksPassMarks
Unit Test 1Mathematics92.00100.0035.00
Unit Test 1Science88.00100.0035.00

Example: LEFT JOIN and NULL​

Every teacher, and the section they are class teacher of this year:

Example
SELECT
    t.EmployeeCode,
    t.FirstName,
    t.LastName,
    ct.SectionId
FROM dbo.Teachers AS t
LEFT JOIN dbo.ClassTeachers AS ct
    ON ct.TeacherId = t.Id
   AND ct.AcademicYearId = (SELECT Id FROM dbo.AcademicYears WHERE IsCurrent = 1)
ORDER BY t.EmployeeCode;
EmployeeCodeFirstNameLastNameSectionId
TCH-001AnithaRao19
TCH-002RahulVerma20
TCH-003SarahThomasNULL
TCH-004ImranKhanNULL
TCH-005PriyaNairNULL
TCH-006DeepakJoshiNULL
TCH-007KavyaMenon11
TCH-008ArjunMehta12

All eight teachers appear. The four who are not class teachers have NULL. An INNER JOIN would have shown only four rows.

Notice the year condition is in ON, not in WHERE. In WHERE, ct.AcademicYearId = 2 would remove the NULL rows again and turn the LEFT JOIN back into an INNER JOIN. The (SELECT Id ...) part is a subquery; Lesson 19 covers them.

Example: many-to-many through a junction table​

The Sharma children and their guardians: Students, then StudentGuardians, then Guardians.

StudentGuardianRelationshipPhone
AaravRajeshFather9876540101
AaravNehaMother9876540102
AdvikaRajeshFather9876540101
AdvikaNehaMother9876540102

Example: a self join​

A self join joins a table to itself, with two different aliases. Which students share a guardian? Join StudentGuardians to itself on the same GuardianId:

Example
SELECT DISTINCT
    s1.FirstName AS Student1,
    s2.FirstName AS Student2
FROM dbo.StudentGuardians AS sg1
INNER JOIN dbo.StudentGuardians AS sg2
    ON sg2.GuardianId = sg1.GuardianId
   AND sg2.StudentId > sg1.StudentId
INNER JOIN dbo.Students AS s1 ON s1.Id = sg1.StudentId
INNER JOIN dbo.Students AS s2 ON s2.Id = sg2.StudentId;
Student1Student2
AaravAdvika

sg2.StudentId > sg1.StudentId stops a student matching themselves, and stops the same pair appearing twice in reverse order. DISTINCT removes the second match: they share two guardians, the father and the mother.

Example: joins can multiply rows​

There are 12 fixed students. Join them to StudentGuardians and count:

Example
SELECT COUNT(*) AS RowsAfterJoin
FROM dbo.Students AS s
INNER JOIN dbo.StudentGuardians AS sg ON sg.StudentId = s.Id
WHERE s.Id <= 12;

The result is 24, not 12: each student has two guardians, so each student appears twice. That is correct for a guardian list, and wrong if you meant to count students. Before adding a join, ask: "How many rows on the other side match each row?"

Show queries/017-joins.sql
queries/017-joins.sql
-- Lesson 17: joins. Read-only: it changes nothing.
USE SchoolDb;
GO

-- 1. INNER JOIN: the Class 10-A list for the current year (the request from Lesson 2).
SELECT
    s.Id,
    s.AdmissionNumber,
    s.FirstName,
    s.LastName,
    e.RollNumber
FROM dbo.Students AS s
INNER JOIN dbo.Enrollments AS e ON e.StudentId = s.Id
INNER JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
INNER JOIN dbo.AcademicYears AS ay ON ay.Id = e.AcademicYearId
WHERE sec.ClassId = 10
  AND sec.Name = 'A'
  AND ay.IsCurrent = 1
ORDER BY e.RollNumber;

-- 2. Kabir's attendance this year, with dates and status.
SELECT
    s.FirstName,
    a.AttendanceDate,
    a.Status,
    a.Remarks
FROM dbo.Students AS s
INNER JOIN dbo.Enrollments AS e ON e.StudentId = s.Id
INNER JOIN dbo.AcademicYears AS ay ON ay.Id = e.AcademicYearId
INNER JOIN dbo.AttendanceRecords AS a ON a.EnrollmentId = e.Id
WHERE s.AdmissionNumber = 'ADM-2017-0003'
  AND ay.IsCurrent = 1
ORDER BY a.AttendanceDate;

-- 3. Aarav's Unit Test 1 marks with subject names: one report card row per paper.
SELECT
    ex.Name AS Exam,
    sub.Name AS Subject,
    m.MarksObtained,
    es.MaxMarks,
    es.PassMarks
FROM dbo.Marks AS m
INNER JOIN dbo.ExamSubjects AS es ON es.Id = m.ExamSubjectId
INNER JOIN dbo.Exams AS ex ON ex.Id = es.ExamId
INNER JOIN dbo.Subjects AS sub ON sub.Id = es.SubjectId
WHERE m.EnrollmentId = 13
ORDER BY sub.Name;

-- 4. LEFT JOIN: every teacher, and the section they are class teacher of this year (if any).
SELECT
    t.EmployeeCode,
    t.FirstName,
    t.LastName,
    ct.SectionId
FROM dbo.Teachers AS t
LEFT JOIN dbo.ClassTeachers AS ct
    ON ct.TeacherId = t.Id
   AND ct.AcademicYearId = (SELECT Id FROM dbo.AcademicYears WHERE IsCurrent = 1)
ORDER BY t.EmployeeCode;

-- 5. Many-to-many: the Sharma children and their guardians, through StudentGuardians.
SELECT
    s.FirstName AS Student,
    g.FirstName AS Guardian,
    sg.Relationship,
    g.Phone
FROM dbo.Students AS s
INNER JOIN dbo.StudentGuardians AS sg ON sg.StudentId = s.Id
INNER JOIN dbo.Guardians AS g ON g.Id = sg.GuardianId
WHERE s.LastName = N'Sharma'
  AND s.Id <= 12
ORDER BY s.FirstName, sg.Relationship;

-- 6. Self join: pairs of students who share a guardian (brothers and sisters).
SELECT DISTINCT
    s1.FirstName AS Student1,
    s2.FirstName AS Student2
FROM dbo.StudentGuardians AS sg1
INNER JOIN dbo.StudentGuardians AS sg2
    ON sg2.GuardianId = sg1.GuardianId
   AND sg2.StudentId > sg1.StudentId
INNER JOIN dbo.Students AS s1 ON s1.Id = sg1.StudentId
INNER JOIN dbo.Students AS s2 ON s2.Id = sg2.StudentId;
GO

Common mistakes​

  1. Joining on the wrong columns. ON e.Id = s.Id compares two unrelated numbers and returns nonsense that looks real. Fix: join a foreign key to the primary key it references.
  2. Putting a LEFT JOIN's filter in WHERE. It removes the NULL rows. Fix: put conditions on the right table in the ON clause.
  3. Forgetting a join condition. The old comma style FROM A, B without a condition pairs every row with every row: 12 students and 6 subjects give 72 rows. Fix: always write INNER JOIN ... ON.
  4. Counting after a one-to-many join. Counts come out doubled. Fix: count the right column, or join after aggregating (Lesson 19).
  5. No aliases in multi-table queries. Ambiguous columns like Id cause errors. Fix: alias every table and prefix every column.

In real projects​

From 16 years of .NET projects

The most common report bug I fix is a total that is exactly double, or triple, what it should be. Every time, someone added one more join to show an extra column, and that join matched two rows instead of one. The numbers looked believable, so nobody noticed for months.

My habit: after writing a join, I check the row count against something I know. If 10-A has four students, the class list must have four rows. If it has eight, a join is multiplying rows, and I find it before anyone builds a report on top.

Best practices​

  • Join foreign keys to the primary keys they reference.
  • Alias every table and prefix every column with its alias.
  • Put conditions on a LEFT JOIN's right-hand table in the ON clause.
  • Check row counts against a number you already know.
  • Prefer LEFT JOIN over RIGHT JOIN; read every query from left to right.

Recap​

  • A join combines rows from tables that match on a condition.
  • INNER JOIN keeps matches only; LEFT JOIN keeps every left row, with NULLs.
  • Many-to-many data is joined through the junction table.
  • A self join uses one table twice with two aliases.
  • One-to-many joins multiply rows; check counts.

Homework​

Library module task: List every book issue with the book title, copy number, student's first and last name, issue date, and return date, oldest first.

A correct result looks like: two rows, Aarav with "Wings of Fire" (returned) and Advika with "The Jungle Book" (return date NULL).

Show the solution
library-assignment/017-library-joins.sql
-- Library homework, Lesson 17. Read-only.
USE SchoolDb;
GO

-- Every issue with the book title and the student's name.
SELECT
    b.Title,
    c.CopyNumber,
    s.FirstName,
    s.LastName,
    i.IssuedOn,
    i.ReturnedOn
FROM dbo.BookIssues AS i
INNER JOIN dbo.BookCopies AS c ON c.Id = i.BookCopyId
INNER JOIN dbo.Books AS b ON b.Id = c.BookId
INNER JOIN dbo.Students AS s ON s.Id = i.StudentId
ORDER BY i.IssuedOn;
GO

Quiz​

  1. What does INNER JOIN keep?

    1. Every row from both tables
    2. Only rows that match on both sides
    3. Every row from the left table
    4. Only rows with NULL
    Show answer

    Answer: B. Only rows that match on both sides Unmatched rows on either side are left out.

  2. Four teachers are not class teachers. In a LEFT JOIN from Teachers to ClassTeachers, what does their SectionId show?

    1. 0
    2. An error
    3. NULL
    4. They are not shown
    Show answer

    Answer: C. NULL A LEFT JOIN keeps the teacher and fills the missing side with NULL.

  3. 12 students each have 2 guardians. How many rows does Students INNER JOIN StudentGuardians return?

    1. 12
    2. 14
    3. 24
    4. 2
    Show answer

    Answer: C. 24 Each student matches two rows, so each appears twice.

  4. What is a self join?

    1. A join without ON
    2. A table joined to itself using two aliases
    3. A join that returns one row
    4. A join to a system view
    Show answer

    Answer: B. A table joined to itself using two aliases The same table plays two roles, such as two students sharing a guardian.

  5. Where should a condition on the right-hand table of a LEFT JOIN go?

    1. In WHERE
    2. In the ON clause
    3. In ORDER BY
    4. In SELECT
    Show answer

    Answer: B. In the ON clause In WHERE it would remove the NULL rows and turn it into an inner join.

Interview questions​

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)​

F What is the difference between INNER JOIN and LEFT JOIN? concept

INNER JOIN returns only rows that match on both sides. LEFT JOIN returns every row from the left table, and fills the right table's columns with NULL when there is no match. Every teacher with their class (LEFT) shows teachers who are not class teachers; INNER would hide them.

See the example · Permalink

F What is a join condition, and what usually goes in it? concept

The join condition, written after ON, says which rows of the two tables belong together. It is usually a foreign key equal to the primary key it references, such as ON e.StudentId = s.Id.

See the example · Permalink

F Write a query that lists the students of Class 10-A for the current year. coding

Join Students to Enrollments, then to Sections and AcademicYears, and filter on the class, the section name, and IsCurrent = 1, ordered by roll number. Four tables, each joined on its foreign key.

See the example · Permalink

Mid (3-5 yrs)​

M Why does adding a join sometimes multiply the number of rows? concept

If each row on the left matches several rows on the right, the left row appears once for each match. Joining 12 students to their guardians gives 24 rows, because each has two. Totals calculated after such a join are multiplied too.

See the example · Permalink

M What is a self join? Give an example. coding

A self join joins a table to itself using two aliases. To find students who share a guardian, join StudentGuardians to itself on the same GuardianId, with a condition like sg2.StudentId > sg1.StudentId so a student does not match themselves.

See the example · Permalink

M Why does a condition on the right table belong in the ON clause of a LEFT JOIN, not in WHERE? concept

WHERE runs after the join. A condition there on the right table removes the rows where that table is NULL, which turns the LEFT JOIN into an INNER JOIN. In the ON clause, it only limits which right rows can match, and unmatched left rows are kept.

See the example · Permalink

Senior (6-9 yrs)​

S A fee report total is exactly double what it should be. How do you find the cause? scenario

Doubled totals almost always come from a one-to-many join added after the data was already at the right level. Remove joins one at a time and watch the row count, or count rows per key to find the join that matches twice. Then aggregate before joining, or join only what the report needs.

How to answer: Show a systematic method (compare row counts with a known number, isolate the join), then the fix (pre-aggregate in a CTE). Use the SMS example of joining payments to guardians. Mention adding a test that checks the total against a simpler query.

See the example · Permalink

S When would you use FULL JOIN or CROSS JOIN in a real system? concept

FULL JOIN compares two lists where either side can have rows the other lacks, such as reconciling an imported fee file with the payments table. CROSS JOIN builds every combination, such as creating exam papers for every class and subject. Both are rare in daily screens.

How to answer: Give one concrete SMS use for each, and say how you keep CROSS JOIN results under control (filter both inputs first, check the expected row count). Mention that the bulk generator in Lesson 15 uses CROSS JOIN deliberately.

See the example · Permalink

Architect / Lead (10+ yrs)​

A A report needs eight joins and runs slowly every morning. Would you keep the joins or build a reporting table? design

First check whether the joins are the real problem: missing indexes on the join columns are the usual cause. If the report is still too slow, or many reports repeat the same joins, a reporting table or indexed view refreshed on a schedule can serve them, accepting slightly delayed data.

How to answer: Compare the options on freshness, cost, and complexity: tuned joins (always fresh), indexed views (fresh, slower writes), and scheduled reporting tables (fast reads, delayed data). Make the decision from measured timings and how fresh the report must be.

See the example · Permalink

Resources​

  • Code for this lesson: git tag lesson-17-joins (code repository coming soon)
  • Files: queries/017-joins.sql, library-assignment/017-library-joins.sql
  • Video: coming soon

What's next​

Joins give you rows. Next, you turn rows into numbers: counts, totals, and averages. Lesson 18: GROUP BY and aggregates

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.