Semi Join with Examples in Relational Algebra, Database Systems

What is Semi Join?

Semi-Join matches the rows of two relations and then shows the matching rows of the relation whose name is mentioned on the left side of ⋉ the Join operator. I am describing more details in the below examples. Hopefully, it will help you to understand the semi-join.

Example 1 of Semi Join

ID Rank Salary
101 Assistant Professor 80,000
102 Associate Professor 90,000
103 Lecturer 70,000

Table 1: Teacher

ID RollNo Marks
103 2017 – 01 80
104 2017 – 02 90
105 2017 – 03 70

Table 2: Student

ID RollNo Marks
103 2017 – 01 80

Table 3: Student ⋉ Teacher OR Student Semi-Join Teacher

ID Rank Salary
103 Lecturer 70,000

Table 4: Teacher ⋉ Student OR Teacher SEMI-JOIN Student

Comparison of semi-join with other joins

Type Syntax Syntax What will do?
Semi Join SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.column = table2.column); SELECT * FROM Student_Table WHERE EXISTS (SELECT 1 FROM Teacher_Table WHERE Student_Table.ClassID = Teacher_Table.ClassID); Show only the rows from the left table where there is a match in the right table.
Cross Join SELECT * FROM table1 CROSS JOIN table2; SELECT * FROM Student_Table CROSS JOIN Teacher_Table; Show the Cartesian product of the two tables, resulting in all possible combinations of rows.
Full (Outer) Join SELECT * FROM table1 FULL JOIN table2 ON table1.column = table2.column; SELECT * FROM Student_Table FULL JOIN Teacher_Table ON Student_Table.ClassID = Teacher_Table.ClassID; Show all rows when there is a match in either the left or right table.
Inner Join SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column; SELECT * FROM Student_Table INNER JOIN Teacher_Table ON Student_Table.ClassID = Teacher_Table.ClassID; Show only the rows where there is a match in both tables based on the specified condition.
Left (Outer) Join SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column; SELECT * FROM Student_Table LEFT JOIN Teacher_Table ON Student_Table.ClassID = Teacher_Table.ClassID; Show all rows from the left table and the matched rows from the right table.
Right (Outer) Join SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; SELECT * FROM Student_Table RIGHT JOIN Teacher_Table ON Student_Table.ClassID = Teacher_Table.ClassID; Show all rows from the right table and the matched rows from the left table.
Self Join SELECT * FROM table1 t1 INNER JOIN table1 t2 ON t1.column = t2.column; SELECT * FROM Student_Table t1 INNER JOIN Student_Table t2 ON t1.ClassID = t2.ClassID; Joins a table with itself, treating it as if it were two separate tables.

Example 2 of Semi Join

ID Rank Salary
101 Assistant Professor 80,000
102 Associate Professor 90,000
103 Lecturer 70,000

Table 5: Teacher

ID RollNo Marks
101 2017 – 01 80
103 2017 – 02 90
106 2017 – 03 70

Table 6: Student

ID RollNo Marks
101 2017 – 01 80
103 2017 – 02 90

Table 7: Student ⋉ Teacher OR Student SEMI-JOIN Teacher:

 

ID Rank Salary
101 Assistant Professor 80,000
103 Lecturer 70,000

Table 8: Teacher ⋉ Student OR Teacher SEMI-JOIN Student


Example 3 of Semi Join

Employee
Name EmpId DeptName
Sameed 1 CS
Shahzeb 2 SE
Abid 3 CS
Shamil 4 IT
Department
DeptName Manager
SE Shahzeb
IT Shamil
Employee  Dept
Name EmpId DeptName
Shahzeb 2 SE
Shamil 4 IT

 

Topic Covered

Semi-Join with Examples in Relational Algebra and database Systems.