EQUI JOIN in Oracle

EQUI JOIN
The Oracle EQUI Join query always uses a comparison operator to check the matching columns of the associated tables, and then the matching columns of the two tables are displayed as the result. It is not mandatory to mention the JOIN keyword for this type of joining to take place. It also happens when there are two tables in the join query with a condition of joining clearly mentioned in the WHERE Clause, such that the condition of joining is somehow a condition of equality of the two corresponding fields of the associated tables.

Syntax 1: Using JOIN Keyword.

SELECT expr_1, expr_2, ... expr_n  
FROM table_1   
JOIN table_2  
ON join_predicate;

Syntax 2: Without using JOIN Keyword.

SELECT expr_1, expr_2, ... expr_n   
FROM table_1, table_2 
WHERE table_1.column_name =  table_2.column_name; 

Parameters:
expr_1, expr_2, … expr_n: It is used to specify the columns of the table which needs to be joined.
table_1, table_2: It is used to specify the name of the tables from which the records need to be joined.
join_predicate: It is used to specify the joining conditions to be strictly followed by the rows to be included in the result set.

Example: Without using JOIN Keyword.
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 30
102 Bond 25
103 Smith 40

Query:

SELECT students.student_id, students.student_name, teachers.teacher_id  
FROM students, teachers  
WHERE students.student_name = teachers.teacher_name;

Output:

STUDENT_ID STUDENT_NAME TEACHER_ID
1 Joy 101
2 Smiley 102

Explanation:
The ‘students’ and the ‘teachers’ are the already existing tables. After the joining, the selected fields of the rows satisfying the equality condition will be displayed as the result. Here the equality condition is checked between the student_name and the teacher_name fields of the ‘students’ and the ‘teachers’ tables respectively.

Please Share