SQL Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN 🔗

---

    

---

    

SQL Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN 🔗
A JOIN in SQL combines rows from two or more tables based on a related column (foreign key).

1️⃣ INNER JOIN (Only Matching Records)
An INNER JOIN returns only the rows that have matching values in both tables.

✅ Example: Students & Courses
| students 🧑‍🎓 | | courses 📚 | |----|---------| |----|--------| | student_id | name | | course_id | course_name | | 1 | Alice | | 101 | Math | | 2 | Bob | | 102 | Science | | 3 | Charlie | | 103 | History |

| enrollments (Student-Course Relationship) | |------------|-----------| | student_id | course_id | | 1 | 101 | | 1 | 102 | | 2 | 103 |

✅ SQL Query: INNER JOIN (Students with Enrolled Courses)

SELECT students.name, courses.course_name

FROM students

INNER JOIN enrollments ON students.student_id = enrollments.student_id

INNER JOIN courses ON enrollments.course_id = courses.course_id;

🔹 Output:

name course_name
Alice Math
Alice Science
Bob History

🔹 INNER JOIN excludes students who are not enrolled in any course.

2️⃣ LEFT JOIN (All Left Table Records + Matching Right Table Records)
A LEFT JOIN returns all records from the left table, and only matching records from the right table. If there's no match, NULL appears.

✅ Example: Get All Students (Even Those Without Courses)

SELECT students.name, courses.course_name

FROM students

LEFT JOIN enrollments ON students.student_id = enrollments.student_id

LEFT JOIN courses ON enrollments.course_id = courses.course_id;

🔹 Output:

name course_name
Alice Math
Alice Science
Bob History
Charlie NULL

🔹 Charlie has no enrolled courses, but is still included (NULL).

3️⃣ RIGHT JOIN (All Right Table Records + Matching Left Table Records)
A RIGHT JOIN returns all records from the right table, and only matching records from the left table.

✅ Example: Get All Courses (Even Those Without Students)

SELECT students.name, courses.course_name

FROM students

RIGHT JOIN enrollments ON students.student_id = enrollments.student_id

RIGHT JOIN courses ON enrollments.course_id = courses.course_id;

🔹 Output:

name course_name
Alice Math
Alice Science
Bob History
NULL English

🔹 English appears (NULL for name), meaning no students are enrolled.

🎯 Summary
Join Type Returns
INNER JOIN Only matching records from both tables.
LEFT JOIN All left table records, plus matching right ones (NULL if no match).
RIGHT JOIN All right table records, plus matching left ones (NULL if no match).

---

    Date: 2025-03-29 00:00:00.000000