JOINS IN ORACLE

J O I N I N G

Combining data from more than one table using a single select statement is called JOINING. Joining in SQL is of these types.

Equi Join
Natural Join
Outer Join
Inner Join
Self Join

1.EQUI JOIN :

Joining more than one table by using a condition that includes ‘ = ’ operator is called as EQUI JOIN.
Following example displays employee details along with Department name in which he is working. Employee details are present in EMP table and Department name exists in another table DEPT. Hence in this example joining is performed between EMP and DEPT. When performing join operation, then we must specify column names in selection list as tablename.columnname when any two tables among all tables we are going to join have a column with same name. Instead of using entire table name, we can use alias names for the tables as in the following example. Using alias names is otional for the columns that are not common in two tables.

Example : Select Ename, Job, Sal, E.Deptno, Dname from EMP E, DEPT D where E.Deptno=D.Deptno;


2. NATURAL JOIN :

As of Oracle 9i, you can use the natural join keyword to indicate that a join should be performed based on all columns that have the same name in the two tables being joined.

Example : Select Ename, Job, Sal, Deptno, Dname from EMP natural join DEPT;

3. OUTER JOIN :

Outer Join is used to display the rows that doesn’t satisfy the given condition, along with the rows that satisfy the given condition. To perform outer join, “right outer join” or “left outer join” or “full outer join” clauses are used. These clauses are used in from clause of the select statement.
The above example, written for the equi-join does not display the department names, in which there are no employees. Because the given condition E.Deptno=D.Deptno will be false for the departments that have no employees. Following example displays the employee details along with their department names. Also displays the department names that have no employees. For this outer-join is performed between emp and dept and “right outer join” clause is used. Because a department exists but corresponding employee does not exist. This means that EMP table does not contain rows corresponding to one or more departments in dept table and DEPT table is towards right side of the equi-join condition.

Example : Select Ename, Job, Sal, D.Deptno, Dname from EMP E right outer join DEPT D on E.Deptno = D.Deptno;

You can replace the on clause with a using clause along with the name of the column the tables have in common – do not qualify the column name with a table name or table alias.

Example : Select Ename, Job, Sal, Deptno, Dname from EMP right outer join DEPT using(deptno)

4. INNER JOIN :

Support for inner join was introduced in Oracle9i. inner joins are the default – they return the rows the two tables have in common, and are the alternative to outer joins. Note that they support on and using clauses. So you can specify your join criteria.
Following example displays employee details along with their grade. Employee details are present in EMP Table and Grade Details are present in SALGRADE Table. SALGRADE table contains LOSAL, HISAL and GRADE Columns. LOSAL and HISAL are the salary range and GRADE is grade of the employees having the salary in that salary range. In this example between…and operator is used. Here as both tables EMPand SALGRADE does not contain columns with same names, Table aliases are not used.

Example : Select Ename, Job, Sal, Deptno, Grade from EMP inner join SALGRADE on Sal BETWEEN Losal AND Hisal;

5. SELF JOIN :

Joining a table to it self is called as Self-Join. Generally self-join is performed on a table that contains self-referential key. Self-referential key is a foreign key that refers to a primary key present in the same table in which it presents.
Following example displays Employee details along with their manager name. In EMP table, EMPNO is primary key and MGR is a foreign key that refers to the primary key EMPNO. As manager is also an employee in the same table, we can get manager’s name by joining EMP table to it self. In self-join we must use alias names or tablename.columnname notation.

Example : Select E.Empno, E.Ename, E.Job, E.Sal, E.Mgr, M.Ename “MANAGER” from EMP E inner join EMP M on E.Mgr = M.Empno;

RULES TO PERFORM JOIN :

User must follow the following rules when performing join operation.

1. In joining statements you must specify the column names in the selection list even you are
Selecting all columns of the table and specifying “*” to select al columns is not possible.
2. If the joining tables contain columns with same name, then tablename.columnname notation or table alias is must except when performing natural join and when using “using” clause.
3. The number of join conditions must be one less than the number of tables you are going to join when performing inner join.