Joins 🔗

Master Joins in SSMS → Then Use in C# - Zero to Hero

Beginner Friendly 45 min read Lesson 5 of 13

Master Joins in 2 Steps! 🎯

PART 1: Learn Joins in SSMS (SQL Server Management Studio)
PART 2: Use Joins in C# (Visual Studio)

Why Joins Matter:

✅ Joins are the most powerful feature in SQL
✅ You'll use them in every real-world application
✅ Master joins and you master relational databases

PART 1 - SQL Server Management Studio (SSMS)

Learn Joins in SSMS First!

SSMS Step 1: Create Our Sample Database

First, let's create tables that have relationships.

-- ===== CREATE DATABASE =====
CREATE DATABASE SchoolDB;
GO

USE SchoolDB;
GO

-- ===== CREATE 4 TABLES WITH RELATIONSHIPS =====

-- 1️⃣ Students Table
CREATE TABLE Students (
    StudentId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100)
);
GO

-- 2️⃣ Courses Table
CREATE TABLE Courses (
    CourseId INT IDENTITY(1,1) PRIMARY KEY,
    Title NVARCHAR(100),
    Credits INT
);
GO

-- 3️⃣ Enrollments Table (Links Students and Courses)
CREATE TABLE Enrollments (
    EnrollmentId INT IDENTITY(1,1) PRIMARY KEY,
    StudentId INT REFERENCES Students(StudentId),
    CourseId INT REFERENCES Courses(CourseId),
    Grade CHAR(2)
);
GO

-- 4️⃣ Departments Table
CREATE TABLE Departments (
    DeptId INT IDENTITY(1,1) PRIMARY KEY,
    DeptName NVARCHAR(50),
    Location NVARCHAR(50)
);
GO

-- 5️⃣ Add DepartmentId to Courses
ALTER TABLE Courses
ADD DeptId INT REFERENCES Departments(DeptId);
GO

-- ===== INSERT SAMPLE DATA =====

-- 1️⃣ Insert Departments
INSERT INTO Departments (DeptName, Location)
VALUES 
    ('Computer Science', 'Building A'),
    ('Mathematics', 'Building B'),
    ('Physics', 'Building C');
GO

-- 2️⃣ Insert Students
INSERT INTO Students (FirstName, LastName, Email)
VALUES 
    ('John', 'Doe', 'john@school.com'),
    ('Jane', 'Smith', 'jane@school.com'),
    ('Bob', 'Johnson', 'bob@school.com'),
    ('Alice', 'Williams', 'alice@school.com'),
    ('Charlie', 'Brown', 'charlie@school.com');
GO

-- 3️⃣ Insert Courses
INSERT INTO Courses (Title, Credits, DeptId)
VALUES 
    ('C# Programming', 4, 1),
    ('Calculus I', 3, 2),
    ('Physics 101', 4, 3),
    ('Data Structures', 3, 1),
    ('Linear Algebra', 3, 2);
GO

-- 4️⃣ Insert Enrollments (Many-to-Many relationship)
INSERT INTO Enrollments (StudentId, CourseId, Grade)
VALUES 
    (1, 1, 'A'),
    (1, 2, 'B'),
    (2, 1, 'A'),
    (2, 3, 'B'),
    (3, 2, 'C'),
    (3, 4, 'A'),
    (4, 1, 'B'),
    (4, 5, 'A'),
    (5, 3, 'C');
GO

✅ Done! We have 4 tables with relationships. Let's learn joins!

SSMS Step 2: INNER JOIN - Only Matches

INNER JOIN: Returns ONLY rows that have a match in BOTH tables.

💻 INNER JOIN - Students + Enrollments

Find all students who are enrolled:

-- Students with enrollments
SELECT s.FirstName, s.LastName, e.CourseId, e.Grade
FROM Students s
INNER JOIN Enrollments e
    ON s.StudentId = e.StudentId;

✅ Only students with enrollments show up!

📌 Alias: s = Students, e = Enrollments

💻 INNER JOIN - Students + Courses

Find students with their course titles:

-- Students with course titles
SELECT s.FirstName, s.LastName, c.Title
FROM Students s
INNER JOIN Enrollments e ON s.StudentId = e.StudentId
INNER JOIN Courses c ON c.CourseId = e.CourseId;

✅ Students with their course names!

💡 Remember: INNER JOIN = "Show me ONLY what exists in BOTH tables"

SSMS Step 3: LEFT JOIN - Keep All Left Rows

LEFT JOIN: Returns ALL rows from the LEFT table, matching rows from the RIGHT. NULL if no match.

💻 LEFT JOIN - All Students

Show all students, even those with no enrollments:

-- All students (even with no enrollments)
SELECT s.FirstName, s.LastName, e.CourseId, e.Grade
FROM Students s
LEFT JOIN Enrollments e
    ON s.StudentId = e.StudentId;

✅ ALL students appear! (Some with NULL CourseId)

💻 LEFT JOIN - Students with Course Info

All students with course titles (NULL if no course):

-- All students with course titles
SELECT s.FirstName, s.LastName, c.Title
FROM Students s
LEFT JOIN Enrollments e ON s.StudentId = e.StudentId
LEFT JOIN Courses c ON c.CourseId = e.CourseId;

✅ ALL students - some with NULL course titles!

💡 Remember: LEFT JOIN = "Show me EVERYTHING from the left table, even if there's no match on the right"

SSMS Step 4: RIGHT JOIN - Keep All Right Rows

RIGHT JOIN: Returns ALL rows from the RIGHT table, matching rows from the LEFT. NULL if no match.

💻 RIGHT JOIN - All Courses

Show all courses, even those with no students:

-- All courses (even with no students)
SELECT c.Title, e.StudentId, e.Grade
FROM Enrollments e
RIGHT JOIN Courses c
    ON c.CourseId = e.CourseId;

✅ ALL courses appear! (Some with NULL StudentId)

💻 RIGHT JOIN - Courses with Students

All courses with student names (NULL if no student):

-- All courses with students
SELECT c.Title, s.FirstName, s.LastName
FROM Enrollments e
RIGHT JOIN Courses c ON c.CourseId = e.CourseId
LEFT JOIN Students s ON s.StudentId = e.StudentId;

✅ ALL courses - some with NULL student names!

💡 Remember: RIGHT JOIN is just LEFT JOIN flipped. Most developers prefer LEFT JOIN for readability.

SSMS Step 5: FULL JOIN - Keep Everything

FULL JOIN: Returns ALL rows from BOTH tables. Matching rows combined, NULL where no match.

💻 FULL JOIN - All Students and Courses

Show everything - students AND courses:

-- Everything - students AND courses
SELECT s.FirstName, c.Title
FROM Students s
FULL JOIN Enrollments e ON s.StudentId = e.StudentId
FULL JOIN Courses c ON c.CourseId = e.CourseId;

✅ EVERYTHING shows up - students without courses, courses without students!

💻 FULL JOIN Use Case

Find students not enrolled AND courses with no students:

-- Students with no courses AND courses with no students
SELECT COALESCE(s.FirstName, 'No Student') AS Student,
       COALESCE(c.Title, 'No Course') AS Course
FROM Students s
FULL JOIN Enrollments e ON s.StudentId = e.StudentId
FULL JOIN Courses c ON c.CourseId = e.CourseId
WHERE e.EnrollmentId IS NULL;

✅ Shows all "orphan" records!

💡 Remember: FULL JOIN = "Show me EVERYTHING from BOTH tables"

SSMS Step 6: CROSS JOIN - Every Combination

CROSS JOIN: Returns EVERY combination of rows from both tables. (Cartesian product)

💻 CROSS JOIN Example

All possible student + course combinations:

-- Every student paired with every course
SELECT s.FirstName, c.Title
FROM Students s
CROSS JOIN Courses c;

✅ 5 students × 5 courses = 25 rows!

💻 CROSS JOIN Use Case

Generate all possible combinations:

-- Generate all possible student-course pairs
SELECT 
    s.FirstName AS Student,
    c.Title AS Course,
    'Not Enrolled' AS Status
FROM Students s
CROSS JOIN Courses c
WHERE NOT EXISTS (
    SELECT 1 FROM Enrollments e
    WHERE e.StudentId = s.StudentId
    AND e.CourseId = c.CourseId
);

✅ Finds all possible enrollments that don't exist!

⚠️ Warning: CROSS JOIN can create HUGE result sets! 100 students × 100 courses = 10,000 rows!

SSMS Step 7: Joining 3+ Tables

Multi-Table Joins: Chain multiple joins together like a chain!

-- ===== JOIN 4 TABLES: Students + Enrollments + Courses + Departments =====

SELECT 
    s.FirstName AS Student,
    s.LastName,
    c.Title AS Course,
    d.DeptName AS Department,
    e.Grade
FROM Students s
INNER JOIN Enrollments e 
    ON s.StudentId = e.StudentId
INNER JOIN Courses c 
    ON c.CourseId = e.CourseId
INNER JOIN Departments d 
    ON d.DeptId = c.DeptId
ORDER BY s.LastName, c.Title;

✅ Result: Complete student report with course and department information!

PART 2 - Using Joins in C# (Visual Studio)

Now Let's Use Joins in C#!

C# Step 1: Create Your WinForms Project

Same joins, now in C#!

📁 Create Project

Open Visual Studio
Create Windows Forms App
Name it "JoinDemoCSharp"

💻 Add NuGet Package

Install Microsoft.Data.SqlClient
Add using statements
Add connection string

using System.Data;
using Microsoft.Data.SqlClient;

private string connString = 
    "Server=localhost;Database=SchoolDB;Trusted_Connection=True;TrustServerCertificate=True;";

C# Step 2: INNER JOIN in C#

// ===== INNER JOIN - Students with Courses =====

private void LoadStudentsWithCourses()
{
    // The SQL query (same as SSMS!)
    string query = "SELECT s.FirstName, s.LastName, c.Title, e.Grade " +
                  "FROM Students s " +
                  "INNER JOIN Enrollments e ON s.StudentId = e.StudentId " +
                  "INNER JOIN Courses c ON c.CourseId = e.CourseId";
    
    // Run the query
    using (var conn = new SqlConnection(connString))
    using (var adapter = new SqlDataAdapter(query, conn))
    {
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        dgvResults.DataSource = dt;
    }
}

✅ Same as SSMS! The SQL is identical, just in C#.

C# Step 3: LEFT JOIN in C#

// ===== LEFT JOIN - All Students with Courses =====

private void LoadAllStudentsWithCourses()
{
    // LEFT JOIN - All students
    string query = "SELECT s.FirstName, s.LastName, c.Title, e.Grade " +
                  "FROM Students s " +
                  "LEFT JOIN Enrollments e ON s.StudentId = e.StudentId " +
                  "LEFT JOIN Courses c ON c.CourseId = e.CourseId";
    
    using (var conn = new SqlConnection(connString))
    using (var adapter = new SqlDataAdapter(query, conn))
    {
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        dgvResults.DataSource = dt;
    }
}

✅ Shows ALL students, even those with no courses!

C# Step 4: Multi-Table JOIN in C#

// ===== 4-TABLE JOIN - Complete Student Report =====

private void LoadCompleteReport()
{
    string query = "SELECT s.FirstName, s.LastName, c.Title, d.DeptName, e.Grade " +
                  "FROM Students s " +
                  "INNER JOIN Enrollments e ON s.StudentId = e.StudentId " +
                  "INNER JOIN Courses c ON c.CourseId = e.CourseId " +
                  "INNER JOIN Departments d ON d.DeptId = c.DeptId " +
                  "ORDER BY s.LastName";
    
    using (var conn = new SqlConnection(connString))
    using (var adapter = new SqlDataAdapter(query, conn))
    {
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        dgvResults.DataSource = dt;
        lblStatus.Text = $"📊 Loaded {dt.Rows.Count} records";
    }
}

🏆 Complete Report! Students, Courses, Departments, and Grades all in one view!

Exercises: Test Your Join Skills!

1 Easy: INNER JOIN

Task: Write C# code to show all students with their course titles.

// SOLUTION (4 lines)
string query = "SELECT s.FirstName, c.Title FROM Students s " +
              "INNER JOIN Enrollments e ON s.StudentId = e.StudentId " +
              "INNER JOIN Courses c ON c.CourseId = e.CourseId";
DataTable dt = GetData(query);
dgvResults.DataSource = dt;

2 Medium: LEFT JOIN

Task: Show all students (including those with no courses) with course titles.

// SOLUTION (4 lines)
string query = "SELECT s.FirstName, c.Title FROM Students s " +
              "LEFT JOIN Enrollments e ON s.StudentId = e.StudentId " +
              "LEFT JOIN Courses c ON c.CourseId = e.CourseId";
DataTable dt = GetData(query);
dgvResults.DataSource = dt;

3 Hard: Complete Reporting System

Task: Build a WinForms app with dropdown to select join type and display results.

// ===== COMPLETE JOIN REPORTING SYSTEM =====

private void btnRunQuery_Click(object sender, EventArgs e)
{
    string joinType = cmbJoinType.SelectedItem.ToString();
    string query = "";
    
    switch (joinType)
    {
        case "INNER JOIN":
            query = "SELECT s.FirstName, c.Title FROM Students s " +
                    "INNER JOIN Enrollments e ON s.StudentId = e.StudentId " +
                    "INNER JOIN Courses c ON c.CourseId = e.CourseId";
            break;
            
        case "LEFT JOIN":
            query = "SELECT s.FirstName, c.Title FROM Students s " +
                    "LEFT JOIN Enrollments e ON s.StudentId = e.StudentId " +
                    "LEFT JOIN Courses c ON c.CourseId = e.CourseId";
            break;
            
        case "FULL JOIN":
            query = "SELECT s.FirstName, c.Title FROM Students s " +
                    "FULL JOIN Enrollments e ON s.StudentId = e.StudentId " +
                    "FULL JOIN Courses c ON c.CourseId = e.CourseId";
            break;
            
        case "CROSS JOIN":
            query = "SELECT s.FirstName, c.Title FROM Students s CROSS JOIN Courses c";
            break;
    }
    
    dgvResults.DataSource = GetData(query);
    lblStatus.Text = $"🔍 Using {joinType} - {dgvResults.RowCount} rows";
}

🏆 Congratulations! You've built a professional reporting system with ALL join types!

🎉 Mastery Summary - All Join Types

INNER

Only matches

ON s.Id = e.Id

LEFT

All left + matches

All students

RIGHT

All right + matches

All courses

FULL

All from both

Everything

CROSS

All combinations

Every pair

In C#

DataTable dt = GetData(query);

Just 1 line to run any join!

🎯 You're now a master of SQL Joins in both SSMS and C#!

Previous Lesson Next Lesson

Test Your Knowledge - Take Quiz