Joins in SQL

Consider two sample tables

Emp table

Dept table

O/P(Equi Join)

Nonequi joins

Same like inner join, Based on a condition  other than equality condition(<,<=,>,>=,<>,between,–)

select ename,loc from
emp
join 
dept
on emp.deptnowhere loc='CHICAGO';

O/P

Self-join

• Self-join is nothing but joining a table with itself
• This type of join is used when we want to compare two different column values within the same table then we must use self join but here the condition  column must belong to Same data type
• Before we are using self join we must create table alias names.
• This alias name must be the different name which is also known as reference names
• alias Names internally behaves like exact another table

self join SQL Query

select rownum,e1.ename "employees",e2.ename "manager" 
from emp e1,emp e2 
where e1.mgr=e2.empno;

O/P

Outer join

• This join is used to retrieve all rows from one table and matching rows from another table
• If you want to retrieve nonmatching  rows then we are using join operator (+) within the  join condition (left side or right side) this is called Oracle 8i outer join.

Left outer join

The join operator(+) is added at the left  side  table of the join condition so that all records from the left side and matching records from the right table are  displayed

Left Join SQL query

select rownum,ename,sal,d.deptno,dname,loc 
from emp e,dept d 
where e.deptno(+)=d.deptno

O/P

Right outer join

Join operator(+) is appended at the right side table of the join condition so that all records from the right side table and matched records from right side table are  displayed

Right join SQL query

select rownum,ename,sal,d.deptno,dname,loc 
from emp e,dept d 
where e.deptno=d.deptno(+);

O/P

Full outer join

• Prior to Oracle 9i if you want to retrieve both matching and unmatched rows as well, can we perform Union operation between left and right joins
• so that matched rows from two tables with the join condition are satisfied, and unmatched rows from the left side table, as well as a right side table, also displayed.

Full Join  SQL query

select rownum,ename,sal,d.deptno,dname,loc 
from emp e,dept d 
where e.deptno(+)=d.deptno ;
union 
select rownum,ename,sal,d.deptno,dname,loc 
from emp e,dept d 
where e.deptno=(+)d.deptno;

O/P