Published / updated29 Aug 2026

Joins

Before you start

You need: filtering (Article 04) and functions (Article 05).

Time: about 50 minutes, plus the practice. Joins are where most real report bugs live.

Learning objective

Join any number of related tables and explain, for any unexpected row count, exactly which join caused it.

Topics

• INNER JOIN
• LEFT JOIN and the WHERE-clause trap
• RIGHT and FULL joins
• CROSS JOIN
• Self joins
• Joining three or more tables
• Duplicate rows from a join
• Join order and readability

INNER JOIN

Returns only rows where the condition matches on both sides.

SELECT   s.RollNumber,
         s.Name,
         e.ExamName,
         r.MarksObtained
FROM     dbo.ExamResult AS r
INNER JOIN dbo.Student  AS s ON s.Id = r.StudentId
INNER JOIN dbo.Exam     AS e ON e.Id = r.ExamId
WHERE    r.SchoolId = 1
ORDER BY s.Name, e.ExamDate;

INNER is the default — JOIN alone means INNER JOIN. Write it explicitly anyway; it makes the intent obvious next to LEFT JOIN lines.

Always alias tables. Once three tables are involved, unaliased column names are ambiguous and the query becomes unreadable.

LEFT JOIN

Returns every row from the left table, with NULL in the right table's columns where there is no match.

SELECT   s.RollNumber,
         s.Name,
         r.MarksObtained
FROM     dbo.Student    AS s
LEFT JOIN dbo.ExamResult AS r ON r.StudentId = s.Id AND r.ExamId = 5
WHERE    s.SchoolId = 1 AND s.Status = 0
ORDER BY s.Name;

Every active student appears. Those with no result for exam 5 show NULL marks — which is exactly what a "who has not been marked yet" report needs.

The trap that breaks LEFT JOIN

-- Looks like a LEFT JOIN, behaves like an INNER JOIN
SELECT s.Name, r.MarksObtained
FROM   dbo.Student     AS s
LEFT JOIN dbo.ExamResult AS r ON r.StudentId = s.Id
WHERE  r.ExamId = 5;

For a student with no result, r.ExamId is NULL. NULL = 5 is UNKNOWN, the WHERE clause discards the row, and the LEFT JOIN has been silently converted to an INNER JOIN.

Conditions on the right table belong in the ON clause. Conditions on the left table belong in WHERE.

-- Correct
FROM      dbo.Student    AS s
LEFT JOIN dbo.ExamResult AS r ON r.StudentId = s.Id AND r.ExamId = 5
WHERE     s.SchoolId = 1;

For INNER JOIN the distinction does not matter — ON and WHERE give the same result. For LEFT JOIN it changes the answer completely. This is the single most common join defect, and it shows up as "students are missing from the report".

Finding rows with no match

-- Students with no fee account for this academic year
SELECT s.RollNumber, s.Name
FROM   dbo.Student    AS s
LEFT JOIN dbo.FeeAccount AS fa
       ON fa.StudentId = s.Id AND fa.AcademicYear = '2024-25'
WHERE  s.SchoolId = 1
  AND  s.Status = 0
  AND  fa.Id IS NULL;

WHERE right.Id IS NULL after a LEFT JOIN is the anti-join pattern. Test the right table's primary key, not a nullable column — testing a nullable column also matches rows that did join but happen to hold NULL there.

RIGHT and FULL joins

-- Every exam, whether or not anyone has a result
SELECT e.ExamName, r.MarksObtained
FROM   dbo.ExamResult AS r
RIGHT JOIN dbo.Exam AS e ON e.Id = r.ExamId;

RIGHT JOIN is a LEFT JOIN with the tables the other way round. It is rare in practice and harder to read — most people write LEFT JOIN with the tables swapped instead.

-- Everything from both sides, matched where possible
SELECT s.Name, fa.TotalFees
FROM   dbo.Student   AS s
FULL OUTER JOIN dbo.FeeAccount AS fa ON fa.StudentId = s.Id;

FULL OUTER JOIN is genuinely useful for reconciliation — finding students with no fee account and fee accounts pointing at no student, in one pass:

SELECT s.RollNumber, fa.Id AS FeeAccountId
FROM   dbo.Student AS s
FULL OUTER JOIN dbo.FeeAccount AS fa ON fa.StudentId = s.Id
WHERE  s.Id IS NULL OR fa.Id IS NULL;

CROSS JOIN

Every row on the left paired with every row on the right — the Cartesian product.

-- 30 students x 6 subjects = 180 rows
SELECT s.Name, sub.Name AS SubjectName
FROM   dbo.Student AS s
CROSS JOIN dbo.Subject AS sub
WHERE  s.ClassName = '10th' AND sub.ClassName = '10th';

Deliberately useful for generating a grid — every student against every subject, so you can then LEFT JOIN results onto it and see the blanks:

SELECT   s.Name,
         sub.Name AS SubjectName,
         r.MarksObtained
FROM     dbo.Student AS s
CROSS JOIN dbo.Subject AS sub
LEFT JOIN dbo.ExamResult AS r
       ON r.StudentId = s.Id
      AND r.ExamId IN (SELECT Id FROM dbo.Exam WHERE SubjectId = sub.Id AND ExamName = 'Mid Term')
WHERE    s.ClassName = '10th' AND sub.ClassName = '10th'
ORDER BY s.Name, sub.Name;

An accidental cross join is the classic runaway query:

-- Missing ON — 30 students x 200 results = 6,000 rows
SELECT s.Name, r.MarksObtained
FROM   dbo.Student AS s, dbo.ExamResult AS r;

The old comma syntax makes this easy to do by omitting the WHERE. Use explicit JOIN ... ON syntax and the mistake becomes a syntax error instead of a silent one.

Self joins

A table joined to itself, using two aliases.

-- Students in the same class and section as Ravi Kumar
SELECT   classmate.RollNumber,
         classmate.Name
FROM     dbo.Student AS ravi
JOIN     dbo.Student AS classmate
      ON classmate.SchoolId  = ravi.SchoolId
     AND classmate.ClassName = ravi.ClassName
     AND classmate.Section   = ravi.Section
     AND classmate.Id       <> ravi.Id
WHERE    ravi.RollNumber = 'NCA-2024-0012'
ORDER BY classmate.Name;

The <> on the key excludes the student from their own classmate list.

Self joins also walk hierarchies:

-- Staff and their reporting manager
SELECT   staff.Name    AS StaffName,
         manager.Name  AS ManagerName
FROM     dbo.Staff AS staff
LEFT JOIN dbo.Staff AS manager ON manager.Id = staff.ReportsToId
WHERE    staff.SchoolId = 1
ORDER BY manager.Name, staff.Name;

LEFT JOIN here so staff with no manager still appear. An INNER JOIN would silently drop the head of the hierarchy.

Joining several tables

SELECT   s.RollNumber,
         s.Name,
         s.ClassName,
         sub.Name        AS SubjectName,
         t.Name          AS TeacherName,
         e.ExamName,
         e.ExamDate,
         r.MarksObtained,
         e.MaxMarks
FROM     dbo.ExamResult AS r
JOIN     dbo.Student    AS s   ON s.Id   = r.StudentId
JOIN     dbo.Exam       AS e   ON e.Id   = r.ExamId
JOIN     dbo.Subject    AS sub ON sub.Id = e.SubjectId
LEFT JOIN dbo.Teacher   AS t   ON t.Id   = sub.TeacherId
WHERE    r.SchoolId = 1
  AND    s.ClassName = '10th'
  AND    r.IsAbsent = 0
ORDER BY s.Name, sub.Name;

Read it as a chain: results connect to students, to exams, to subjects, to teachers. Each JOIN narrows or extends the set.

The LEFT JOIN on Teacher is deliberate — a subject with no teacher assigned should still show its results, with a blank teacher name. Using INNER JOIN there silently drops every result for unassigned subjects, and nobody notices until a teacher leaves.

One INNER JOIN anywhere in the chain filters the whole result. When rows go missing from a multi-table query, change each INNER to LEFT one at a time and watch where the count changes.

Duplicate rows from a join

The second most common join problem: the row count is larger than expected.

-- One row per student? No — one row per PAYMENT
SELECT s.Name, fa.TotalFees
FROM   dbo.Student    AS s
JOIN   dbo.FeeAccount AS fa ON fa.StudentId = s.Id
JOIN   dbo.FeePayment AS p  ON p.FeeAccountId = fa.Id;

A student with four payments appears four times, and SUM(fa.TotalFees) over that result counts their fees four times. The reported symptom is "the fee report total is far too high".

Joining a one-to-many relationship multiplies rows. That is correct behaviour, not a bug — the fix is to aggregate before joining, not to add DISTINCT:

-- Wrong fix: DISTINCT hides the problem and still breaks SUM
SELECT DISTINCT s.Name, fa.TotalFees ...

-- Right fix: aggregate the many side first
SELECT   s.RollNumber,
         s.Name,
         fa.TotalFees,
         ISNULL(paid.TotalPaid, 0) AS TotalPaid,
         fa.TotalFees - ISNULL(paid.TotalPaid, 0) - fa.DiscountAmount AS Outstanding
FROM     dbo.Student    AS s
JOIN     dbo.FeeAccount AS fa ON fa.StudentId = s.Id
LEFT JOIN (
             SELECT   p.FeeAccountId,
                      SUM(p.Amount) AS TotalPaid
             FROM     dbo.FeePayment AS p
             GROUP BY p.FeeAccountId
         ) AS paid ON paid.FeeAccountId = fa.Id
WHERE    s.SchoolId = 1 AND fa.AcademicYear = '2024-25'
ORDER BY Outstanding DESC;

The subquery reduces payments to one row per account before the join, so each student appears once.

Diagnosing it: run the query with only the first join, note the count, then add each join one at a time. The join that changes the count is the one multiplying rows.

SELECT COUNT(*) FROM dbo.Student AS s WHERE s.SchoolId = 1;                    -- 400
SELECT COUNT(*) FROM dbo.Student AS s
JOIN dbo.FeeAccount AS fa ON fa.StudentId = s.Id WHERE s.SchoolId = 1;         -- 400  ok
SELECT COUNT(*) FROM dbo.Student AS s
JOIN dbo.FeeAccount AS fa ON fa.StudentId = s.Id
JOIN dbo.FeePayment AS p ON p.FeeAccountId = fa.Id WHERE s.SchoolId = 1;       -- 1,240  <- here

Join conditions and correctness

Two things to check on every join in a multi-tenant system.

Join on keys, not on data. Joining ON s.Name = t.Name works until two people share a name.

Carry the tenant filter. A join on StudentId alone is correct because the id is globally unique, but a join on a business key must include SchoolId:

-- Wrong: roll numbers are unique per school, not globally
JOIN dbo.StudentImport AS imp ON imp.RollNumber = s.RollNumber

-- Correct
JOIN dbo.StudentImport AS imp
  ON imp.RollNumber = s.RollNumber AND imp.SchoolId = s.SchoolId

Omitting SchoolId there joins one school's import rows to another school's students.

Readability

FROM      dbo.ExamResult AS r
JOIN      dbo.Student    AS s   ON s.Id   = r.StudentId
JOIN      dbo.Exam       AS e   ON e.Id   = r.ExamId
LEFT JOIN dbo.Teacher    AS t   ON t.Id   = sub.TeacherId

Conventions that pay for themselves:

• Short, meaningful aliases — s, r, e, sub, fa
• One join per line, ON on the same line when short
• Aligned ON clauses
• Write the joined table's column first in the condition: ON s.Id = r.StudentId
• Start FROM with the table the query is really about

For an INNER JOIN, table order does not change the result — the optimiser decides the physical order. For LEFT JOIN it absolutely does.

Errors you will hit

What you see	Cause	Fix
The multi-part identifier could not be bound	Alias wrong or table not in the FROM	Check the aliases
Ambiguous column name 'Id'	Same column in two joined tables	Qualify it: s.Id
Rows missing from a report	INNER JOIN dropped rows with no match	LEFT JOIN if they must appear
Totals are too high	The join multiplied rows	Compare COUNT(*) before and after
LEFT JOIN behaves like INNER	A WHERE on the right-hand table	Move the condition into the ON
A whole extra set of rows appears	Missing join condition — a cross join	Add the ON

Compare the row count before and after every join. Down means an inner join dropped rows; up means it multiplied them. Both produce a wrong report with no error.

Common mistakes

• A right-table condition in WHERE after a LEFT JOIN, turning it into an inner join
• INNER JOIN where LEFT JOIN was needed, silently dropping rows
• Testing a nullable column instead of the key in an anti-join
• Not aliasing tables
• DISTINCT to hide duplicates a join created
• Aggregating over a result that a one-to-many join multiplied
• An accidental cross join from the comma syntax
• A self join without excluding the row from itself
• Joining on a name or other non-key data
• Omitting SchoolId when joining on a per-tenant business key

Practice

Write and run these — the course exercise is build multi-table reports:

1. Every exam result for class 10th with student name, subject, teacher, marks and max marks.
2. Every active student with their marks for exam 5, including those with no result yet.
3. Take query 2 and move r.ExamId = 5 from ON to WHERE. Record both row counts and explain the difference.
4. Students with no fee account for 2024-25.
5. Students and their fee outstanding — correct, with no duplication from multiple payments.
6. Deliberately write query 5 without the aggregating subquery. Compare the total outstanding from both. Write down by how much the wrong version overstates it.
7. Classmates of Ravi Kumar via a self join.
8. Staff with their manager, including staff who have none.
9. A student-by-subject grid using CROSS JOIN plus LEFT JOIN, showing blanks where no result exists.
10. Reconcile students against fee accounts with FULL OUTER JOIN, listing orphans on both sides.

Then run the course debugging exercise — investigate duplicate rows from a join. Take query 6, and use the incremental COUNT(*) method: add one join at a time and record the count after each. Identify the exact join that multiplies rows, and state the relationship cardinality that caused it.

You can now

• Join any number of tables correctly
• Choose between INNER and LEFT from what the report must show
• Say why a WHERE on the right table turns a LEFT JOIN into an INNER
• Detect a join that dropped or multiplied rows
• Qualify columns so nothing is ambiguous

Review questions

1. Why does a right-table condition in WHERE turn a LEFT JOIN into an INNER JOIN?
2. Why is DISTINCT the wrong fix for duplicate rows from a join?
3. How do you find rows in one table with no match in another?
4. Why must a join on RollNumber also include SchoolId?

Next: Grouping and subqueries