SQL Joins in SQL Server – INNER, LEFT, RIGHT, FULL Explained with Examples

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 | CourseName

To 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;

✅ Returns: Only rows where StudentID matches in both tables.

---

🔸 LEFT JOIN

SELECT Students.Name, Enrollments.CourseName
FROM Students
LEFT JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;

✅ Returns: All students, even if they are not enrolled in any course.

---

🔸 RIGHT JOIN

SELECT Students.Name, Enrollments.CourseName
FROM Students
RIGHT JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;

✅ Returns: All enrollments, even if the student is not found in the Students table.

---

🔸 FULL OUTER JOIN

SELECT Students.Name, Enrollments.CourseName
FROM Students
FULL OUTER JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;

✅ Returns: All students and all enrollments, matching where possible.

---

📊 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 ON clause correctly to prevent Cartesian products.
• Use INNER JOIN when you only want matched records.
• Use LEFT JOIN when 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! 🚀

---

← Previous Next →