CBSE 12th Unit 3-Chapter -3 DATABASE MANAGEMENT AND DATABASE CONCEPTS

 

CHAPTER – 3

Joins

Joins are used to combine data from two or more tables based on a related column.

1. Cartesian Product

• The simplest type of join.
• Combines every row from the first table with every row from the second table.
• Can result in a very large number of rows, especially for large tables.

Diagram: Cartesian Product

 

 diagram illustrating a Cartesian Product of two tables, showing all possible combinations of rows

Code Example (SQL)

SQL

SELECT *

FROM Students, Courses;

This query performs a Cartesian product between the "Students" and "Courses" tables, resulting in a table with all possible combinations of students and courses.

2. Equi-Join

• Combines rows from two tables based on a specific condition, usually an equality comparison between two columns.
• More efficient than the Cartesian product.

Diagram: Equi-Join

                                            

 

 diagram illustrating an EquiJoin of two tables, showing only the rows where the join condition is met

Code Example (SQL)

SQL

SELECT *

FROM Students, Courses

WHERE Students.StudentID = Courses.StudentID;

This query performs an equi-join between the "Students" and "Courses" tables, selecting only the rows where the "StudentID" in both tables matches.

3. Natural Join

• A special type of equi-join that automatically joins tables based on the matching columns with the same name.
• Combines rows from two tables based on the equality of columns with the same name.

Code Example (SQL)

SQL

SELECT *

FROM Students NATURAL JOIN Courses;

This query performs a natural join between the "Students" and "Courses" tables, joining them based on the "StudentID" column.