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
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:
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.
