Joins

The purpose of a join is to combine the data spread across tables. A join is actually performed by the where clause which combines the specified rows of tables.
Types of Joins:-

Syn:- select columnslist from table1,table2 where expression
Joining Multiple Tables(EQUI JOINS/ Simple Joins)
            Sometimes we require to treat multiple tables as though key were a single entity. Then a single SQL sentence can manipulate data from all the tables. To achieve this, we have to join tables. Tables are joined on columns that have the same data type and data width in the tables.
Ex:-     1)select * from stock,stock1;
2)select stock.icode,stock1.na from stock,stock1;
3)select stock.icode,stock.name from stock,stock1
where stock.icode=stock1.icode;
4)select stock.icode,sum(stock.qty) from stock,stock1
where stock.icode=stock1.icode

           

group by(stock.icode) ;          

SELF JOINS:- In some situations you may find it necessary to join a table to itself as though you were joining two separate tables. This is referred to as a self-join. In  a self join two rows from the same table combine to from a result row.
            To join a table to itself two copies of the very same table have to be opened in memory. Hence in the from clause the table name needs to be mentioned twice, since the table names are the same, the second table will overwrite the first table and in effect result in only one table being in memory. This is because a table name is translated into a specific memory location. To avoid this each table is opened under an alias. Now these table aliases will cause two identical tables to be opened in different memory locations. This will result in two identical tables to be physically present in the computer’s memory.

Syn:- select columnslist from table1 alias1,table2 alias2,…….
Ex:- create table employ(eno number(5),name varchar2(20),mno number(5));
            Insert into employ values(1,’prasad’,2);
            Insert into employ values(2,’rajesh’,5);
            Insert into employ values(3,’eswar’,4);
            Insert into employ values(4,’harish’,null);
            Insert into employ values(5,’anil’,null);

Ex:-2) select empl.name,mngr.name from employ empl,employ mngr where
            Empl.mno=mngr.eno;
OUTER JOINS
            The outer join extends the result of a simple join. An outer join returns all the rows returned by simple join as well as those rows from one table that do not match any row from the other table. The symbol,(+) represents outer joins. The following examples help us for better understanding.
Ex:-     insert into stock1 values(100,’lux’,4300);
            1) select stock.icode,stock.name from stock,stock1
where stock.icode=stock1.icode(+);
2) select stock.icode,stock1.na from stock,stock1
where stock.icode(+)=stock1.icode;
3) select stock.icode,stock1.na from stock,stock1
where stock.icode=stock1.icode(+)

For
Online Classes

Contact Us: +919885348743