9. Advanced Joins

Why Data Is Split Across Tables

Consider this scenario: if you stored the department name directly inside every student row, and 500 students are in Computer Science, the text Computer Science would appear 500 times. If the department ever changes its name, you must update 500 rows. Miss even one and your database now contains two different names for the same department — inconsistent data.

The solution: store the department name once in a Department table. The Student table stores only a DeptID — a number referencing the correct department. To see the name alongside the student data, you join the two tables at query time. This is the relational approach, and it is why the relational model is so powerful.

Setting Up Test Data

Make sure you are in VoidX_Academy with Department and Student tables containing data. If you completed Module 5, your tables should already have rows. Verify this:

USE VoidX_Academy;
SELECT COUNT(*) AS DeptCount FROM Department;
SELECT COUNT(*) AS StudentCount FROM Student;

If either count shows zero, re-run the INSERT statements from Module 5 before continuing.

Also add one student with no department assigned — you will need this to see the difference between join types:

INSERT INTO Student (FirstName, LastName, Email)
VALUES ('Efua', 'Boateng', 'efua@ucc.edu.gh');

Efua has no DeptID value, which means her DeptID column is NULL.

INNER JOIN — Only Matching Rows

An INNER JOIN returns only the rows where the join condition is true in both tables. Rows with no match on either side are excluded completely from the result.

Join students with their department names:

SELECT
  Student.FirstName,
  Student.LastName,
  Department.DeptName
FROM Student
INNER JOIN Department ON Student.DeptID = Department.DeptID;

Breaking this apart: FROM Student says start with the Student table. INNER JOIN Department brings in the Department table. ON Student.DeptID = Department.DeptID is the condition — a student row and a department row are paired when their DeptID values match.

Run this. You will see Kofi, Ama, and Kwame — but not Efua. Because Efua's DeptID is NULL, she has no match in Department, and INNER JOIN excludes her entirely.

When table names are long, use short aliases to reduce typing:

SELECT
  s.FirstName,
  s.LastName,
  d.DeptName
FROM Student AS s
INNER JOIN Department AS d ON s.DeptID = d.DeptID;

Identical result — just cleaner to write. s refers to Student and d refers to Department throughout the query.

LEFT JOIN — All Rows from the Left Table

A LEFT JOIN returns all rows from the left table (the one in the FROM clause) regardless of whether a match exists in the right table. Where no match exists, the right table's columns appear as NULL.

SELECT
  s.FirstName,
  s.LastName,
  d.DeptName
FROM Student AS s
LEFT JOIN Department AS d ON s.DeptID = d.DeptID;

Run this. Now all four students appear — including Efua. Her DeptName column shows NULL because she has no department. LEFT JOIN is the right choice when you want all records from the left table, including those without a matching record on the right side.

You can use LEFT JOIN specifically to find records that are missing a relationship:

SELECT s.FirstName, s.LastName
FROM Student AS s
LEFT JOIN Department AS d ON s.DeptID = d.DeptID
WHERE d.DeptID IS NULL;

This finds students who have not been assigned to any department — a common data-quality check in real systems.

RIGHT JOIN — All Rows from the Right Table

A RIGHT JOIN is the mirror image of LEFT JOIN. It returns all rows from the right table regardless of whether there is a match in the left table.

SELECT
  s.FirstName,
  d.DeptName
FROM Student AS s
RIGHT JOIN Department AS d ON s.DeptID = d.DeptID;

This shows all departments — including Mathematics, which currently has no students enrolled. The student columns appear as NULL for empty departments. A RIGHT JOIN is useful for finding departments with no students, or courses with no enrollment.

Joining Three Tables

First create the Course and Enrollment tables, then insert sample data:

CREATE TABLE Course (
  CourseID INT PRIMARY KEY,
  CourseName VARCHAR(100) NOT NULL,
  DeptID INT,
  FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

CREATE TABLE Enrollment (
  StudentID INT,
  CourseID INT,
  Semester VARCHAR(20),
  Grade DECIMAL(4,2),
  PRIMARY KEY (StudentID, CourseID, Semester),
  FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
  FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

INSERT INTO Course VALUES
  (101, 'Database Design', 1),
  (102, 'Programming Fundamentals', 1);

INSERT INTO Enrollment VALUES
  (1, 101, '2024-Sem1', 78.5),
  (1, 102, '2024-Sem1', 85.0),
  (2, 101, '2024-Sem1', 91.0);

Now write a query joining all three tables — Enrollment, Student, and Course — to show each student's name, the course they are enrolled in, and their grade:

SELECT
  s.FirstName,
  s.LastName,
  c.CourseName,
  e.Grade
FROM Enrollment AS e
INNER JOIN Student AS s ON e.StudentID = s.StudentID
INNER JOIN Course AS c ON e.CourseID = c.CourseID
ORDER BY s.LastName, c.CourseName;

Read this from top to bottom: start with Enrollment as the base table. Join Student by matching StudentID. Join Course by matching CourseID. Sort the result by last name and then course name. Each INNER JOIN adds another table to the result, linking on the specified columns.

Viewing the Execution Plan

SSMS can show you graphically how SQL Server plans to execute any join query. Before pressing F5, press Ctrl + L. The Estimated Execution Plan appears as a graphical diagram showing each operation — table scans, index seeks, nested loop joins. Thicker arrows indicate more rows flowing between operations.

This tool becomes increasingly important as your queries grow more complex. Start looking at it now so you are comfortable with it by the time you reach the performance modules.