SQL Join

Join

Joins:-We are using joins when we need data from two are more tables

Note:- By "exists" we can select columns of outer table only , same way we can select outer table columns by "in" as well but by "Join" we can select columns from all joined tables (Which is possible in join only). 

 Types of Joins:- There are below types joins in Oracle

1) Cross Join or Cartesian Join
2) Inner Join
3) Natural Join
4) Outer Join
             a) Left Outer Join
             b) Right Outer Join
             c) Full Outer Join
5) Self Join

Note:- There are 2 standard syntax for Join
           1) ANSI Standard
           2) Oracle Standard

1) ANSI Standard:- In ANSI standard Join type need to mentioned and joining condition supply with "on" clause.

2) Oracle Standard :-In Oracle Standard, Join type not mentioned and joining condition supply with "where" clause.

1) Cross Join or Cartesian Join:-This is the worst join where no join condition mentioned. In this join, Oracle joins every record of first table with all records of second table .

Note:-After this join record count will be (m X n) where m is record count of first table and n is the record count of second table.


Example:-
select a.*,b.* from emp a,dept b;--Oracle Standard
select a.*,b.* from emp a cross join dept b; --ANSI standard

2) Inner Join:-In this join result depend on join condition.

Note:-After this join record count will be (<= m )  where m is the record count of that table which have the highest records.


Example:- 
ANSI standard:-
select a.*,b.* from emp a  inner join dept b on a.deptno=b.deptno; 
                                OR
select a.*,b.* from emp a  join dept b on a.deptno=b.deptno;

Oracle standard:-
select a.*,b.* from emp a , dept b where  a.deptno=b.deptno;

3) Natural Join :- This join is the same as Inner Join but here no need to supply any joining condition. Oracle automatically decide joining condition on the basis of columns name in Tables.There should be at-least one column name should be same in both tables.

Note:- You can't select that column on which system joined both tables in select query and when you are selecting all columns by "*" system will display that column in first position. 

select * from emp a  natural join dept b ; --ANSI standard
select a.EMPNO,a.ENAME,b.DNAME from emp a  natural join dept b ;

Note:-You can not take join column in select list.

4) Outer Join:- This join is used when we need records from joining table that are not satisfying join condition with records that are satisfying matching condition.

a) Left Outer Join:- In Left Outer Join, All records from left table will be part of result and where not matching records of right table, NULL will be displayed.

select a.*,b.* from emp111 a  left outer join dept b on a.deptno=b.deptno;

ANSI standard:-
select a.*,b.* from emp111 a  left join dept b on a.deptno=b.deptno; --ANSI standard
select a.*,b.* from emp111 a , dept b where a.deptno=b.deptno(+);--Oracle Standard

b) Right Outer Join:- In Right Outer Join, All records from right table will be part of result and where not matching records of left table, NULL will be displayed.

select a.*,b.* from emp a  right outer join dept b on a.deptno=b.deptno;

ANSI standard:-
select a.*,b.* from emp a  right join dept b on a.deptno=b.deptno; --ANSI standard
select a.*,b.* from emp a , dept b where a.deptno(+)=b.deptno;--Oracle Standard

c) Full Outer Join:- In Full Outer Join, All records from both tables will be part of result and where not matching records of left/right table, NULL will be displayed.

select a.*,b.* from emp111 a  full outer join dept b on a.deptno=b.deptno; --ANSI standard
select a.*,b.* from emp a  full join dept b on a.deptno=b.deptno; --ANSI standard
select a.*,b.* from emp a , dept b where a.deptno(+)=b.deptno
union
select a.*,b.* from emp a , dept b where a.deptno=b.deptno(+);--Oracle Standard

Practice Questions :-

Q1 :- What is Join ?
Q2 :- Why Join is necessary ?
Q3 :- What are types of Join ?
Q4 :- What is the difference between Inner Join and Outer Join ?
Q5 :- What types of Outer Joins available in Oracle ?