SQL JOINS Explained with Examples (INNER, LEFT, RIGHT, FULL, NATURAL)

✨ Introduction: Why Use Joins?

---

In real-world databases, we don’t store all the data in one big table. Instead, we split data into multiple smaller tables. This makes it easier to manage and avoids duplication. This process is called Normalization.

But when we want to see related data together, like:

“Show me each student and their course”

...we need to join those tables using SQL JOINs.

Let’s explore 5 major types of SQL JOINs with real examples and easy explanations. We will use these two tables throughout:

---

🧾 Sample Tables We’ll Use

🎓 Table 1: Student

CREATE TABLE Student (
  ROLL_NO INT PRIMARY KEY,
  NAME VARCHAR(50),
  AGE INT
);

INSERT INTO Student VALUES
(1, 'Harsh', 21),
(2, 'Pratik', 22),
(3, 'Riyanka', 23),
(4, 'Deep', 24),
(5, 'Saptarshi', 25),
(6, 'Dhanraj', 22),
(7, 'Rohit', 21),
(8, 'Niraj', 22);

📚 Table 2: StudentCourse

CREATE TABLE StudentCourse (
  COURSE_ID INT,
  ROLL_NO INT
);

INSERT INTO StudentCourse VALUES
(1, 1),
(2, 2),
(2, 3),
(3, 4),
(1, 5),
(4, NULL),
(5, NULL),
(4, NULL);

---

1️⃣ INNER JOIN (Only Matching Data)

✅ What it does:

It shows only the students who are enrolled in a course.

🔍 Query:

SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE 
FROM Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

📊 Output:

🟡 This JOIN ignores students who are not enrolled in any course.

---

2️⃣ LEFT JOIN (All Students, Course If Available)

✅ What it does:

Shows all students, whether they have a course or not.
If there's no course, it shows NULL.

🔍 Query:

SELECT Student.NAME, StudentCourse.COURSE_ID 
FROM Student
LEFT JOIN StudentCourse 
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

📊 Output:

🟡 Useful when you want a full list of students, with or without courses.

---

3️⃣ RIGHT JOIN (All Courses, Students If Available)

✅ What it does:

Shows all courses, whether they are assigned to a student or not.

🔍 Query:

SELECT Student.NAME, StudentCourse.COURSE_ID 
FROM Student
RIGHT JOIN StudentCourse 
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

📊 Output:

🟡 Useful when you want to list all courses, even those without any enrolled students.

---

4️⃣ FULL JOIN (All Students + All Courses)

✅ What it does:

Combines both LEFT and RIGHT JOIN.
It shows all students and all courses, even if they don’t match.

🔍 Query:

SELECT Student.NAME, StudentCourse.COURSE_ID 
FROM Student
FULL JOIN StudentCourse 
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

📊 Output:

🟡 Best when you want everything, and don’t want to miss any data.

---

5️⃣ NATURAL JOIN (Automatic Match)

✅ What it does:

Automatically joins two tables based on same column names.

We’ll use a different example for this:

👨‍💼 Table: Employee

CREATE TABLE Employee (
  Emp_id INT PRIMARY KEY,
  Emp_name VARCHAR(50),
  Dept_id INT
);

INSERT INTO Employee VALUES
(1, 'Ram', 10),
(2, 'Jon', 30),
(3, 'Bob', 50);

🏢 Table: Department

CREATE TABLE Department (
  Dept_id INT PRIMARY KEY,
  Dept_name VARCHAR(50)
);

INSERT INTO Department VALUES
(10, 'IT'),
(30, 'HR'),
(40, 'TIS');

🔍 Query:

SELECT * 
FROM Employee 
NATURAL JOIN Department;

📊 Output:

🟡 NATURAL JOIN only works when both tables have same column name (like Dept_id here).

---

✅ Quick Summary: When to Use Which Join?