Join Queries

 

Join Queries in SQL

---

1. Aim

To study and implement different types of JOIN operations in SQL, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN (simulated).

---

2. Theory

A JOIN operation in SQL is used to combine rows from two or more tables based on related columns between them. JOINs help retrieve related data stored across multiple tables.

---

3. Types of JOINs

---

3.1 INNER JOIN

• Returns only the rows where there is a matching value in both tables.

• Non-matching rows are excluded.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

---

3.2 LEFT JOIN (LEFT OUTER JOIN)

• Returns all rows from the left table.

• Matching rows from the right table are included.

• If no match exists, the right-side columns return NULL.

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

---

3.3 RIGHT JOIN (RIGHT OUTER JOIN)

• Returns all rows from the right table.

• Matching rows from the left table are included.

• If no match exists, the left-side columns return NULL.

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

---

3.4 FULL OUTER JOIN

MySQL does not support FULL JOIN, but it can be simulated using:

FULL JOIN = LEFT JOIN + RIGHT JOIN (without duplicates)

Syntax (MySQL simulation):

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column
UNION
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;

---

4. Example Tables

We use the following sample tables:

Table: students

id	first_name	last_name
1	John	Doe
2	Sara	Rose
3	Mike	Tyson

Table: enrollments

student_id	course_id
1	C101
1	C102
3	C103

---

5. JOIN Examples

---

5.1 INNER JOIN Example

SELECT students.id, students.first_name, students.last_name, enrollments.course_id
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id;

Output:

id	first_name	last_name	course_id
1	John	Doe	C101
1	John	Doe	C102
3	Mike	Tyson	C103

---

5.2 LEFT JOIN Example

SELECT students.id, students.first_name, students.last_name, enrollments.course_id
FROM students
LEFT JOIN enrollments ON students.id = enrollments.student_id;

Output:

id	first_name	last_name	course_id
1	John	Doe	C101
1	John	Doe	C102
3	Mike	Tyson	C103
2	Sara	Rose	NULL

(Sara has no enrollment → course_id is NULL)

---

5.3 RIGHT JOIN Example

SELECT students.id, students.first_name, students.last_name, enrollments.course_id
FROM students
RIGHT JOIN enrollments ON students.id = enrollments.student_id;

Output:

id	first_name	last_name	course_id
1	John	Doe	C101
1	John	Doe	C102
3	Mike	Tyson	C103

(If an enrollment existed for a non-existing student, student fields would be NULL.)

---

5.4 FULL JOIN Example (Simulated)

SELECT students.id, students.first_name, students.last_name, enrollments.course_id
FROM students
LEFT JOIN enrollments ON students.id = enrollments.student_id

UNION

SELECT students.id, students.first_name, students.last_name, enrollments.course_id
FROM students
RIGHT JOIN enrollments ON students.id = enrollments.student_id;

Output:

id	first_name	last_name	course_id
1	John	Doe	C101
1	John	Doe	C102
3	Mike	Tyson	C103
2	Sara	Rose	NULL

---

6. Results

• Successfully implemented INNER, LEFT, RIGHT, and simulated FULL JOIN in MySQL.

• Observed differences between inclusive and exclusive joins.

• Understood how JOINs combine related data across multiple tables.

---

7. Conclusion

JOIN operations are essential for retrieving data across related tables.
By mastering these SQL JOINs, one can efficiently query relational databases and analyze connected datasets.
MySQL supports INNER, LEFT, and RIGHT joins but requires a UNION-based workaround for FULL JOIN.