13. INNER JOIN -- Connecting Tables

Level: Beginner

ℹ️ What You'll Learn

• INNER JOIN syntax -- connect two tables
• Join conditions (ON clause)
• Matching rows only
• JOIN with WHERE filter
• Multiple column joins
• Self-join basics
• Common JOIN mistakes
• Performance of JOINs

Tables relate via Foreign Keys. SELECT alone queries one table. JOIN queries multiple tables. This article teaches INNER JOIN -- return only matching rows.

Why JOINs Matter for Backend Developers

APIs need data from multiple tables:

Frontend asks: "Get exam result for student Ravi with marks"
    v
Backend needs: Student + Exam + ExamResult data
    v
SELECT * FROM Student
JOIN Exam ON ...
JOIN ExamResult ON ...
WHERE Student.Name = 'Ravi'
    v
Returns complete result with all related data

Without JOINs, backend makes multiple queries (slow). JOINs get all data in one query.

INNER JOIN Syntax

SELECT *
FROM Table1
INNER JOIN Table2 
ON Table1.ForeignKey = Table2.PrimaryKey;

Parts:

• INNER JOIN -- Connect two tables
• ON -- Join condition (how tables relate)

Effect: Returns only rows where join condition matches.

Example 1: Student + Class

Get student info with class teacher:

SELECT 
    s.StudentId,
    s.Name,
    s.ClassName,
    t.Name AS TeacherName
FROM Student s
INNER JOIN Subject t ON s.ClassName = t.ClassName;

Result (only classes with teachers):

StudentId | Name        | ClassName | TeacherName
1         | Ravi Kumar  | 10-A      | Dr. Mehta
2         | Priya Sharma| 10-A      | Dr. Mehta
3         | Arjun Reddy | 10-B      | Mrs. Rao

INNER JOIN ensures: Only students whose class matches subject.

Example 2: Student + Exam + Result

Get student marks:

SELECT 
    st.Name AS Student,
    s.Name AS Subject,
    e.ExamName,
    er.MarksObtained
FROM Student st
INNER JOIN ExamResult er ON st.StudentId = er.StudentId
INNER JOIN Exam e ON er.ExamId = e.ExamId
INNER JOIN Subject s ON e.SubjectId = s.SubjectId;

Chains multiple JOINs. Returns only complete records (student, exam, result, subject).

Example 3: Teacher + Subject

Get which teacher teaches which subject:

SELECT 
    t.Name AS Teacher,
    s.Name AS Subject
FROM Teacher t
INNER JOIN Subject s ON t.TeacherId = s.TeacherId;

Result:

Teacher    | Subject
Dr. Mehta  | Maths
Dr. Mehta  | Science
Mrs. Rao   | English

INNER JOIN with WHERE Filter

Get Maths subject taught by Dr. Mehta:

SELECT 
    t.Name AS Teacher,
    s.Name AS Subject
FROM Teacher t
INNER JOIN Subject s ON t.TeacherId = s.TeacherId
WHERE s.Name = 'Maths' AND t.Name = 'Dr. Mehta';

Result:

Teacher   | Subject
Dr. Mehta | Maths

Understanding INNER JOIN Logic

Only rows where join condition matches:

Teacher table:       Subject table:
TeacherId | Name     SubjectId | Name   | TeacherId
1         | Dr. Mehta  10      | Maths  | 1
2         | Mrs. Rao   11      | Science| 1
                      12      | English| 2
                      13      | Hindi  | 99 <- No matching teacher!

INNER JOIN result:
Teacher   | Subject
Dr. Mehta | Maths      (1 = 1) [OK]
Dr. Mehta | Science    (1 = 1) [OK]
Mrs. Rao  | English    (2 = 2) [OK]
(Hindi with TeacherId 99 excluded - no matching teacher)

INNER JOIN skips unmatched rows.

Table Aliases

Shorten table names in query:

SELECT 
    s.StudentId,
    s.Name,
    e.ExamName,
    r.MarksObtained
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
INNER JOIN Exam e ON r.ExamId = e.ExamId;

s = Student, r = ExamResult, e = Exam (aliases make queries readable).

---

Common JOIN Mistakes

Mistake 1: Wrong join condition

Wrong:

SELECT * 
FROM Student s
INNER JOIN Subject sub ON s.StudentId = sub.TeacherId;
-- Wrong: StudentId doesn't match TeacherId logically

Result: Nonsensical data.

Fix: Use correct FK:

SELECT * 
FROM Student s
INNER JOIN Exam e ON s.StudentId = e.StudentId;
-- Correct: Student's exam results

Mistake 2: Forgetting join condition

Wrong:

SELECT * 
FROM Student s, Teacher t;
-- No ON clause! Cartesian product (every student matched every teacher)
-- 3 students x 3 teachers = 9 rows (nonsense)

Result: Cartesian product (all combinations).

Fix: Add ON condition:

SELECT * 
FROM Student s
INNER JOIN Teacher t ON s.TeacherId = t.TeacherId;

Mistake 3: Confusing INNER with LEFT JOIN

INNER JOIN -> Only matching rows LEFT JOIN -> All from left table + matching from right

-- Student with no exam results excluded in INNER JOIN
-- But included in LEFT JOIN

Result: Different row counts, confusing results.

Fix: Choose correct type for your need.

Mistake 4: Not using aliases for clarity

Wrong:

SELECT Student.StudentId, Student.Name, Exam.ExamName
FROM Student
INNER JOIN Exam ON Student.StudentId = Exam.StudentId
INNER JOIN Subject ON Exam.SubjectId = Subject.SubjectId;
-- Long, repetitive

Better:

SELECT s.StudentId, s.Name, e.ExamName
FROM Student s
INNER JOIN Exam e ON s.StudentId = e.StudentId
INNER JOIN Subject sub ON e.SubjectId = sub.SubjectId;

---

Best Practices for JOINs

1. Use correct join condition -- Matches foreign keys
2. Use table aliases -- Shorter, more readable
3. Choose correct JOIN type -- INNER, LEFT, FULL based on need
4. Test before production -- Check row counts
5. Index join columns -- Speeds up large joins
6. Document join logic -- Comments explain why joined
7. Join on PK-FK -- Usually. Avoid complex conditions

---

🎯 Q1: What does INNER JOIN return?

INNER JOIN returns only rows where join condition matches.

Example:

Teacher: Dr. Mehta (1), Mrs. Rao (2)
Subject: Maths (TeacherId 1), English (TeacherId 2), History (TeacherId 99)

INNER JOIN Teacher t ON Subject.TeacherId = t.TeacherId
Result: Maths + English (matched)
        History excluded (TeacherId 99 doesn't exist in Teacher)

INNER = match required. No match = excluded.

🎯 Q2: Why use aliases in JOINs?

Aliases shorten table names for readability:

-- Without aliases (verbose)
FROM Student INNER JOIN Exam 
ON Student.StudentId = Exam.StudentId

-- With aliases (clean)
FROM Student s INNER JOIN Exam e 
ON s.StudentId = e.StudentId

Also allows distinguishing same table joined twice (SELF JOIN).

🎯 Q3: What's the difference between INNER and LEFT JOIN?

INNER JOIN:

SELECT * FROM Student s
INNER JOIN Exam e ON s.StudentId = e.StudentId;
-- Only students with exams

LEFT JOIN:

SELECT * FROM Student s
LEFT JOIN Exam e ON s.StudentId = e.StudentId;
-- All students, even without exams (NULL for exam columns)

INNER = match required. LEFT = all from left table.

🎯 Q4: Can I join more than 2 tables?

Yes:

SELECT * FROM Student s
INNER JOIN Exam e ON s.StudentId = e.StudentId
INNER JOIN ExamResult r ON e.ExamId = r.ExamId
INNER JOIN Subject sub ON e.SubjectId = sub.SubjectId;

Chains multiple JOINs. Each adds another table condition.

Rule: Each JOIN needs ON condition (one per join).

🎯 Q5: What's a Cartesian product in JOINs?

Join without ON condition = every row matches every row:

SELECT * FROM Student s, Teacher t;  -- No ON!
-- 3 students x 3 teachers = 9 rows
-- Every student matched every teacher (nonsense)

Result: Explosive row count, useless data.

Fix: Always include ON condition.

🎯 Q6: How do I know which JOIN to use?

INNER JOIN: Only matching rows (both tables)

• "Students with exams"
• "Teachers teaching subjects"

LEFT JOIN: All from left + matching from right

• "All students (with or without exams)"
• "All teachers (even if not teaching)"

Choose based on: Do you want unmatched rows from left table?

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on INNER JOIN in SQL Server for connecting tables. Try these prompts:

• "Explain INNER JOIN syntax with Student/Exam example"
• "What does INNER JOIN return? Show matching vs non-matching"
• "How do I join more than 2 tables? Show Student/Exam/Result chain"
• "What's a Cartesian product and why avoid it?"
• "Quiz me: ask 5 questions about INNER JOIN, conditions, syntax"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

14. LEFT JOIN & RIGHT JOIN -- Including Unmatched Rows ->