INNER Versus OUTER Joins

• In SQL the join of two tables returning only matched rows is called an inner join
• A join between two tables that returns the results of the inner join as well as the unmatched rows from the left (or right) tables is called a left (or right) outer join
• A join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join

LEFT OUTER JOIN :

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

• 

RIGHT OUTER JOIN :

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

• 

FULL OUTER JOIN :

• SELECT e.ename, e.deptno, d.dname FROM emp e FULL OUTER JOIN dept d ON (e.deptno = d.deptno) ;

• 

Non-Equijoins

Retrieving Records with Non-Equijoins :

• SELECT e.ename, e.sal, j.gra FROM emp e JOIN job_grd j ON e.sal BETWEEN j.lowest_sal AND j.highest_sal;

Outer Joins :