Part 8: SQL Joins – INNER, LEFT, RIGHT, FULL Explained
Microsoft SQL Server Tutorial Series: Beginner to Expert
Welcome to the Intermediate section of our SQL Server Tutorial Series! In this part, we’ll dive into one of the most important topics in SQL — Joins.
Joins are essential when working with relational databases, as they allow you to combine data from multiple tables based on related columns.
🔍 What You'll Learn
- What SQL Joins are and why they matter
- The difference between INNER, LEFT, RIGHT, and FULL Joins
- Syntax and visual examples of each type
- Best practices for using Joins in SQL Server
📘 What is a SQL Join?
A SQL Join is used to combine rows from two or more tables, based on a related column between them — often a foreign key and primary key relationship.
Let’s say you have two tables:
Students
---------
StudentID | Name
Enrollments
--------------
EnrollmentID | StudentID | CourseNameTo get a list of student names with their courses, you'll use a join on the StudentID column.
🔗 Types of Joins in SQL Server
Here are the four main types of joins in SQL Server:| Join Type | Description |
|---|---|
| INNER JOIN | Returns only the matching rows between both tables. |
| LEFT JOIN | Returns all rows from the left table, and matched rows from the right table. |
| RIGHT JOIN | Returns all rows from the right table, and matched rows from the left table. |
| FULL JOIN | Returns all rows when there is a match in either table. |
🧠 Example Tables
Let’s assume the following data:Students
+-----------+---------+
| StudentID | Name |
+-----------+---------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
+-----------+---------+
Enrollments
+--------------+-----------+---------------+
| EnrollmentID | StudentID | CourseName |
+--------------+-----------+---------------+
| 1 | 1 | Math |
| 2 | 2 | Science |
| 3 | 4 | History |
+--------------+-----------+---------------+🔸 INNER JOIN
SELECT Students.Name, Enrollments.CourseName
FROM Students
INNER JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;StudentID matches in both tables.
🔸 LEFT JOIN
SELECT Students.Name, Enrollments.CourseName
FROM Students
LEFT JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;🔸 RIGHT JOIN
SELECT Students.Name, Enrollments.CourseName
FROM Students
RIGHT JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;🔸 FULL OUTER JOIN
SELECT Students.Name, Enrollments.CourseName
FROM Students
FULL OUTER JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;📊 Visual Summary of Join Behavior
| Join Type | Includes Unmatched Rows From |
|---|---|
| INNER JOIN | None |
| LEFT JOIN | Left Table (Students) |
| RIGHT JOIN | Right Table (Enrollments) |
| FULL OUTER JOIN | Both Tables |
🛠️ Best Practices for Using Joins
- Always use
ONclause correctly to prevent Cartesian products. - Use
INNER JOINwhen you only want matched records. - Use
LEFT JOINwhen you want all data from the first (left) table. - Make sure joined columns have proper indexes for performance.
- Use table aliases (
S,E) in complex queries to make them readable.
🧾 Quick Join Syntax Cheat Sheet
-- INNER JOIN SELECT * FROM A INNER JOIN B ON A.ID = B.A_ID; -- LEFT JOIN SELECT * FROM A LEFT JOIN B ON A.ID = B.A_ID; -- RIGHT JOIN SELECT * FROM A RIGHT JOIN B ON A.ID = B.A_ID; -- FULL JOIN SELECT * FROM A FULL OUTER JOIN B ON A.ID = B.A_ID;
✅ Summary
In this tutorial, you learned:
- How to use SQL Joins in SQL Server
- INNER, LEFT, RIGHT, and FULL OUTER joins with syntax and examples
- How Joins combine data from multiple tables
- Best practices for writing clean and efficient joins
🔗 What’s Next?
Now that you’ve mastered SQL Joins, the next part will teach you how to perform grouping and aggregation using GROUP BY, HAVING, and aggregate functions like COUNT(), SUM(), and AVG().
Have any questions or insights? Leave a comment below — let’s grow together! 🚀
