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:

Teachers Table:

Query:

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

Output:

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 follow and like us: