MySQL Joins – Class 12 Informatics Practices / Computer Science Notes

Relational databases store data in multiple tables. Often, we need to retrieve data from more than one table at the same time. Joins in MySQL allow us to combine rows from two or more tables based on a related column.

---

Key Concepts of Joins

1. Definition:
   A join is used to fetch data from two or more tables based on a related column (usually a primary key in one table and a foreign key in another).
2. Why Use Joins?
   • To avoid redundancy of data in a single table.
   • To make databases normalized (divided into smaller logical tables).
   • To fetch meaningful combined information from multiple tables.
3. Syntax of Join: SELECT table1.column, table2.column FROM table1 JOIN table2 ON table1.common_column = table2.common_column;

---

Types of Joins in MySQL

1. INNER JOIN

• Returns only the rows that have matching values in both tables.
• Excludes unmatched rows.

Example:

Students Table

RollNo	Name	Class
1	Rahul	12A
2	Priya	12B
3	Amit	12A

Marks Table

RollNo	Subject	Marks
1	CS	90
2	CS	85
4	CS	75

Query:

SELECT Students.RollNo, Students.Name, Marks.Marks
FROM Students
INNER JOIN Marks
ON Students.RollNo = Marks.RollNo;

Result:

RollNo	Name	Marks
1	Rahul	90
2	Priya	85

---

2. LEFT JOIN (LEFT OUTER JOIN)

• Returns all rows from the left table and matching rows from the right table.
• If there is no match, NULL is returned for right table columns.

Query:

SELECT Students.RollNo, Students.Name, Marks.Marks
FROM Students
LEFT JOIN Marks
ON Students.RollNo = Marks.RollNo;

Result:

RollNo	Name	Marks
1	Rahul	90
2	Priya	85
3	Amit	NULL

---

3. RIGHT JOIN (RIGHT OUTER JOIN)

• Returns all rows from the right table and matching rows from the left table.
• If there is no match, NULL is returned for left table columns.

Query:

SELECT Students.RollNo, Students.Name, Marks.Marks
FROM Students
RIGHT JOIN Marks
ON Students.RollNo = Marks.RollNo;

Result:

RollNo	Name	Marks
1	Rahul	90
2	Priya	85
4	NULL	75

---

4. CROSS JOIN (CARTESIAN JOIN)

• Returns all possible combinations of rows between two tables.
• If one table has 3 rows and another has 4 rows → Result = 3 × 4 = 12 rows.

Query:

SELECT Students.Name, Marks.Subject
FROM Students
CROSS JOIN Marks;

---

5. SELF JOIN

• A table is joined with itself (as if it were two tables).
• Used for hierarchical data or comparing rows within the same table.

Example:

SELECT A.Name AS Student1, B.Name AS Student2, A.Class
FROM Students A, Students B
WHERE A.Class = B.Class AND A.RollNo < B.RollNo;

---

Key Points to Remember

• INNER JOIN → Only matching rows.
• LEFT JOIN → All rows from left + matched from right.
• RIGHT JOIN → All rows from right + matched from left.
• CROSS JOIN → Cartesian product.
• SELF JOIN → Table with itself.

---

Practice Questions

Short Answer / Theory Questions

1. Define a JOIN in MySQL with an example.
2. Differentiate between INNER JOIN and OUTER JOIN.
3. Which join will return unmatched rows also? Explain with an example.
4. Write SQL query to fetch all students with their marks, including those who do not have marks yet.
5. What is the difference between LEFT JOIN and RIGHT JOIN?

Query-based Questions

Given the tables:

Employee Table

EID	Name	DeptID
101	Ramesh	10
102	Suresh	20
103	Priya	30
104	Aman	NULL

Department Table

DeptID	DeptName
10	HR
20	Accounts
40	IT

Write queries for:

1. Display all employees with their department names using INNER JOIN.
2. Display all employees and their departments (if not assigned, show NULL) using LEFT JOIN.
3. Display all departments with employees (if no employees, show NULL) using RIGHT JOIN.
4. List employee names with department names, ensuring all employees are displayed even if they do not belong to a department.
5. Write a query to get all possible pairs of employees with different IDs from the Employee table itself (using SELF JOIN).

---

Higher-Order Thinking Questions

1. A school maintains two tables: Teacher(TID, TName, Subject) and Class(CID, ClassName, TID).
   • Write a query to display all classes with their teachers (use JOIN).
   • Write a query to display teachers who are not assigned any class.
2. A company maintains Project(PID, PName, Budget) and Employee(EID, Name, PID).
   • Write SQL queries using JOIN to:
     a) Display employee names with their project names.
     b) Display projects that have no employees assigned.