SQL Joins (CBSE Class 12 Computer Science - 083)

In a relational database, information is often stored in multiple related tables instead of one large table. SQL Joins combine data from two or more tables based on a common column. Joins help retrieve meaningful information stored across different tables.

Learning Objectives

• Understand the concept of SQL Joins.
• Learn Cartesian Product.
• Learn Equi Join.
• Learn Natural Join.
• Differentiate between different types of joins.

---

Why are Joins Required?

Suppose student information and department information are stored in separate tables. If we want to display the student's name along with the department name, we must combine both tables. SQL Joins make this possible.

---

Sample Tables

Student Table

StudentID	Name	City	Marks	DeptID
101	Riya	Jaipur	95	1
102	Aman	Delhi	91	2
103	Priya	Jaipur	88	1
104	Rahul	Mumbai	76	3
105	Neha	Delhi	84	2
106	Arjun	Jaipur	92	1
107	Simran	Mumbai	81	3
108	Karan	Delhi	69	2

Department Table

DeptID	Department
1	Science
2	Commerce
3	Humanities

---

What is a Join?

A Join is an SQL operation used to retrieve related data from two or more tables using a common column.

---

Types of Joins (CBSE Syllabus)

• Cartesian Product
• Equi Join
• Natural Join

---

1. Cartesian Product

A Cartesian Product combines every row of the first table with every row of the second table.

If the first table has m rows and the second table has n rows, the result contains m × n rows.

---

Syntax

SELECT *
FROM Student,
Department;

---

Example

The Student table has 8 rows and the Department table has 3 rows.

Number of rows returned:

8 × 3 = 24 rows

---

Sample Output

Student Name	Department
Riya	Science
Riya	Commerce
Riya	Humanities
Aman	Science
Aman	Commerce

The pattern continues until every student is combined with every department.

---

2. Equi Join

An Equi Join retrieves only those rows where the common columns in both tables have equal values.

---

Syntax

SELECT *
FROM Student, Department
WHERE Student.DeptID = Department.DeptID;

---

Example

SELECT Student.Name,
Department.Department
FROM Student,
Department
WHERE Student.DeptID = Department.DeptID;

Output

Name	Department
Riya	Science
Aman	Commerce
Priya	Science
Rahul	Humanities
Neha	Commerce
Arjun	Science
Simran	Humanities
Karan	Commerce

---

Characteristics of Equi Join

• Uses the WHERE clause.
• Compares common columns using the '=' operator.
• Duplicate join columns appear in the result.

---

3. Natural Join

A Natural Join automatically joins two tables using columns having the same name and compatible data types.

Unlike an Equi Join, the common column appears only once in the output.

---

Syntax

SELECT *
FROM Student
NATURAL JOIN Department;

---

Example

SELECT Name,
Department
FROM Student
NATURAL JOIN Department;

Output

Name	Department
Riya	Science
Aman	Commerce
Priya	Science
Rahul	Humanities
Neha	Commerce
Arjun	Science
Simran	Humanities
Karan	Commerce

---

Difference Between Equi Join and Natural Join

Equi Join	Natural Join
Uses WHERE condition.	Uses NATURAL JOIN keyword.
Join condition is written explicitly.	Join condition is identified automatically.
Duplicate join column appears in output.	Duplicate join column is removed.

---

Difference Between Cartesian Product and Join

Cartesian Product	Join
Combines every row with every row.	Combines only matching rows.
No matching condition.	Uses matching columns.
Produces many unnecessary rows.	Produces meaningful results.

---

Execution Flow

Student Table
        │
        ▼
Department Table
        │
        ▼
Join Condition
        │
        ▼
Combined Result

---

Real-Life Example

Instead of storing the department name for every student, only the DeptID is stored in the Student table.

Whenever the department name is required, SQL joins the Student and Department tables.

This reduces data redundancy and maintains consistency.

---

Common Errors

• Forgetting the join condition while writing an Equi Join.
• Confusing Cartesian Product with Equi Join.
• Using different column names in a Natural Join.
• Expecting Cartesian Product to produce meaningful results.
• Joining unrelated tables.

---

Exam Tips

---

Frequently Asked Questions (FAQs)

1. What is a Join in SQL?

A Join combines related data from two or more tables.

2. What is a Cartesian Product?

It combines every row of one table with every row of another table.

3. What is an Equi Join?

An Equi Join combines rows having equal values in the common columns.

4. What is a Natural Join?

A Natural Join automatically joins tables using columns having the same name and compatible data types.

5. Which join removes duplicate join columns?

Natural Join removes duplicate join columns from the result.

---

Summary

• Joins combine related data from multiple tables.
• Cartesian Product combines every row with every row.
• Equi Join retrieves matching rows using a WHERE condition.
• Natural Join automatically joins tables using common column names.
• Joins are essential for retrieving related information stored in normalized databases.