SEMI JOIN in Oracle

SEMI Join is used to return one copy of those rows from a table where at least one match is found in the values with the other mentioned table, and to serve this purpose, EXISTS construct is used instead of any JOIN keyword. The main advantage of this kind of Join query is that it makes the queries run faster and thus is a very powerful SQL construct.

Syntax:

SELECT columns  
FROM table_1  
WHERE EXISTS (  
SELECT values  
FROM table_2  
WHERE table_2.column = table_1.column);  

Example: Students Table:

STUDENT_ID STUDENT_NAME STUDENT_AGE
1 Joy 20
2 Smiley 19
3 Happy 21
4 James 22
5 Bond 25

Teachers Table:

TEACHER_ID TEACHER_NAME TEACHER_AGE
101 James 22
102 Bond 25
103 Smith 40

Query:

SELECT students.student_id, students.student_name  
FROM students
WHERE EXISTS (  
SELECT 25  
FROM teachers 
WHERE teachers.teacher_age = students.student_age);

Output:

STUDENT_ID STUDENT_NAME TEACHER_AGE
5 Bond

Explanation: The ‘students’ and the ‘teachers’ are the already existing tables. After the joining, the selected fields of the rows of the ‘students’ table satisfying the equality condition will be displayed as a result, but this equality condition is valid only for those rows in the ‘students’ table that does have the value of student_age as 25.

Please follow and like us:
Content Protection by DMCA.com