SQL: Types of Joins

It is important to note that the INNER JOIN only returns data where a match is found. While using the inner join we have seen that if there exists certain records in one table. Which doesn’t have corresponding values in the second table then those rows will not be selected.

The rows which do not have values in other table could be forcefully selected by using the OUTER join. The corresponding values for such rows will have NULL values. The result of such query will be all those rows that have corresponding rows in other table as well as those rows that do not have values in other table.

This could be achieved by using Outer Join and Plus (+) Operator. This operator will be used on that side that has shortage of information.

We can say that it includes all the rows from both of the tables or result sets participating in the JOIN. When no matching rows exist for rows on the “left” side of the JOIN, you see null values from the result set on the “right.” Conversely, when no matching rows exist for rows on the “right” side of the JOIN, you see Null values from the result set on the “left.”

Outer Join can be of three types. We can categorize OUTER join according to Oracle join as well as ANSI Join. ANSI Join is introduced to tackle the queries for Orac1e9i and “above Version.

Full Outer Join

To retrieve the Employee name, job, salary, deptno, dname from EMP, DEPT table. It will return all information regarding the employees and the department information. This is Full Outer Join’s Example.

SQL> SELECT ENAME,JOB.SAl.DEPT_DEPTNO.DHAME FROM EMP,DEPT WHERE EMP.DEPTNO(+) = DEPT.DEPTNO AND DEPT.DEPTNO NOT IN (10,20);

Explanation: It is clear from the query that it is missing ENAME, JOB, and SAL for the department number 40. it is due to the information missing in the employee table but it still shows the available information .i.e. DEPTNO, DNAME. So in the EMP table there was deficit of information, hence the (+) operator placed on EMP side.

• To retrieve the Employee name, job, salary, deptno, dname from EMP, DEPT table. It will return all information regarding the employees and the department information.

Explanation: It is clear from the query below that now we have employee information but we don’t have department information in the DEPT table so now deficit of information is on the DEPT side.

SQL> SELECT ENRME,JOB.SAl,DEPT.DEPTND,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPl.DEPTNO(+);

Full Outer Join

We can also use Outer join according to the ANSI standard. The below query is performed by using the ANSI standard. The ANSI Standards are defined for Oracle 9i or advance versions.

A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

SQL> SELECT ENRME,JOB.SAl,DEPT.DEPTND,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPl.DEPTNO(+);

Explanation: This command will return all the employee names and their respective Dept numbers.

Left Outer Join

The result of a left outer join (or simply left join) for table A and B always contains records of the “left” table (A), even if the join-condition does not find any matching record in the “right” table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result-but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).

For example, this allows us to find an employee’s department, but still shows the employee(s) even when their department does not exist (contrary to the inner-join example above, where employees in non-existent departments are excluded from the result).

SQL>SELECT e.ename. d.dname FROM emp e LEFT OUTER JOIN dept d ON (e.deptno = d.deptno);

Right Outer Joins

A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the “right” table (B) will appear in the Joined table at least once. If no matching row from the “left” table (A) exists, NULL will appear in columns from A for those records that have no match in A.

A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).

For example, this allows us to find each employee and his or her department, but still show departments that have no employees.

SQl> SELECT e.ename. d.dname FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno = d.deptno);