SQL

Types of JOIN in Oracle 

If User want to get or merge columns from two table (table1 and table2)

Syntax : select * from   table1    ***** JOIN   table2    ON      table1.Col1 = table2.col2 ;

1. Self Join - Joining the table itself is called self join.
     
  select *  from table1 t1, table1 t2 where t1.lastname=t2.lastname;

2. Inner Join - This join returns all matched rows from both table.
             
select * from table1 inner join table2 on table1.col1 = table2.col2.
OR
select * from table1 , table2 where table1.col1 = table2.col2


3. Outer Join - Outer join gives the non-matching records along with matching records.

      a. Left outer join - This will display the all matching records and the records which are in left hand side table(table1) those that are not in right hand side table(table2).
   
  select * from table1 left outer join table1 on table1.col1 = table2.col2
                                              OR
           select * from table1 , table 2 where table1.col = table2.col2 (+);
   
      b. Right outer join - This will display the all matching records and the records which are in right hand side table(table2) those that are not in left hand side table(table1).
   
select * from table1 right outer join table1 on table1.col1 = table2.col2
                                                  OR
               select * from table1 , table 2 where table1.col (+)= table2.col2;

      c. Full outer join - This will display the all matching records and the non-matching records from both tables
select * from table1 full outer join table2 on table1.col1 = table2.col2.
           
4. cross join - This will gives the cross product. This join lists all the rows in all tables

                  select * from table1 , table2;
 select * from table1 natural join table2; -- remove duplicate entry


5. Anti join -  It returns rows from the first table (table1) where no matches are found in the second table(table2)

select * from table1 where table1.col1 not in (select col2 from table2 where table2.col3= ‘MGMNT’) order by XXX;

6. Semi Join -  It returns rows from the first table where one or more matches are found in the second table.

                SELECT * FROM table1 WHERE EXISTS (SELECT * FROM table2 WHERE table1.cole1= table2.col2 and table1.col3 =35)
ORDER BY XXX;